Using colons in cells causing me grief.

[jgreenlee]

I'm using VLOOKUP in a spreadsheet to take a MAC Address and lookup the associated host which lives in another worksheet. Unfortunately the results of my queries are pretty sporadic. For example...

10:00:00:00:C9:32:AA:BB

Is my reference MAC. I have the following formula that performs the lookup...

=IF($N23="";"";VLOOKUP($N23;systems_macs.$E$2:systems_macs.$F$100;2))

Instead of returning the proper value for that MAC address it returns the value for a completely different MAC address.

For giggles I removed all the colons from the reference MAC address and the target MAC address in my data worksheet which returned the proper system.

An interesting observation is that adding the first colon back (10:00...) returns a different MAC but still not the right one. (why it returns ANY result is beyond me because now the reference doesn't match ANY field in the lookup table.)

I formatted the cells in both columns of MACs as "text".

Why isn't the lookup working properly?

Thanks,

James

[jgreenlee]

Okay...It appears that colons are a special opperand that causes Calc to do some "figgerin."

Using dots in place of the colons seems to fix my issue...But I would like to see my MAC's with colons instead. How can I get around this?

James

[ms777]

Hi,

if I remember correctly, VLOOKUP expects 4 parameter, not 3, and the last one must be 'false' for exact matches ... check the OO Basic help

Good luck,

ms777

[Villeroy]

The colons are not the problem, but the missing 4th argument of VLOOKUP.
Have a look at this: http://www.oooforum.org/forum/viewtopic.phtml?t=38012
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[jgreenlee]

Much thanks guys...I never saw mention of the "fourth" parameter of VLOOKUP in the OOo tutorial.

Things usually work when you use them right.

Thanks!

James