Excel VLOOKUP with multiple values?

[TSDible]

Ok... somebody please help me before I go nuts here.

I need to do a vlookup where the lookup_value could be one of two possibilities (but not both)

So, if I have a table like...

A B C
cal1 1 2

It is possible based on the user that they could have entered either "cal1" or "cal 1" in column A. I want the VLOOKUP to find the value of lets say C.

If I use =VLOOKUP("cal1",A1:C1,3,FALSE) It returns the number 2. However, if the user of the machine named that cal 1 instead (with a space) the sheet no longer finds it.

I tried the folowing with no luck.

=VLOOKUP(OR("cal1","cal 1"),A1:C1,3,FALSE)

but it doesn't seem to like the nested OR function.

Any help???

Or is there another function I could be using?

The Table that will be used is actually imported from an instrument and depending on how the sample names are entered into that machine, the name could be slightly different.

Any help is appreciated.

 

[kranky]

Would it be easier to remove all the spaces from the cell with "cal 1" entries? Then your first formula would work.

 

[TSDible]

Yes, it would work, and it would be easier, but not everybody names their samples the same way.

However, I did manage to figure out how to incorporate wildcards into the VLOOKUP if anyone is interested.

Here is my actual cell formula

=IF(ISNA(VLOOKUP("cal"&"*"&"1",'Cal Import'!$A$2:$N$20,1,FALSE)),"Import Error",VLOOKUP("cal"&"*"&"1",'Cal Import'!$A$2:$N$20,1,FALSE))

Works like a charm.