OR Function in VLookup

  • Thread starter Thread starter Biff
  • Start date Start date
B

Biff

I am trying to embed an OR function in a VLookup function, but am getting an
error message. Can someone help me out. I am trying to return a value based
on a particular look up value, but if that lookup value doesn't return
anything, I want it to look at another value in anothe adjacent cell. I was
thinking that an OR function would solve this problem, but am having
troubles.

Thanks,
Biff
 
Here you go.

=VLOOKUP(OR($C41,$H41),'[Model Summary -
Tamarac.xls]Input'!$C$5:$S$35,14,FALSE)
 
I am trying to embed an OR function in a VLookup function, but am getting an
error message. Can someone help me out. I am trying to return a value based
on a particular look up value, but if that lookup value doesn't return
anything, I want it to look at another value in anothe adjacent cell. I was
thinking that an OR function would solve this problem, but am having
troubles.

Thanks,
Biff
Click to expand...

A VLOOKUP function will always return something, maybe you mean by
"does'n return anything" that there is an error returned from the
VLOOKUP.

If so, you may try this code structure:

IF(ISERROR( xxx ), yyy, xxx )

where xxx is your first VLOOKUP formula (that may return an error)
and yyy is your second, fallback, VLOOKUP formula.

Hope this helps / Lars-Åke
 
try using IF instead of OR. If you encounter any problem,
Post your database and your expected result in order
to eliminate guesswork.

HTH

--
If this posting was helpful, please click on the Yes button below

Thank You

cheers, francis
 
Hi,

You can check for an error on the first vlookup and if there is one run the
second

=IF(ISNA(VLOOKUP(C41,'[Model Summary -
Tamarac.xls]Input'!$C$5:$S$35,14,FALSE)),VLOOKUP(H41,'[Model Summary -
Tamarac.xls]Input'!$C$5:$S$35,14,FALSE),VLOOKUP(C41,'[Model Summary -
Tamarac.xls]Input'!$C$5:$S$35,14,FALSE))

Mike

Biff said:
Here you go.

=VLOOKUP(OR($C41,$H41),'[Model Summary -
Tamarac.xls]Input'!$C$5:$S$35,14,FALSE)



Mike H said:
Post your formula even if it's not working

Mike
Click to expand...
Click to expand...
 
Here is more detail:


A B C
1Column 1 Column 2 Return Value
2 A B 100

Table:

A B
10 Look Up Value Return Value
11 C 50
12 B 100
13 D 72
14 F 68

I am trying to get a formula that looks in both A2 and B2 for look up value
when going to the table. It shouldn't find both values in the table.

The formual I used is: =VLOOKUP(OR(A2,B2),A10:B14,2,FALSE)

Help?
 
Hi,

I am not comfortable with the column 1 containing "A B", is that really
what you have A followed by a bunch of spaces and then B?

If you have two values you want to check against the same table if the first
one is not found then

Replace
=VLOOKUP(OR(A2,B2),A10:B14,2,FALSE)
With
=IF(ISNA(VLOOKUP(A2,A10:B14,2,FALSE)),VLOOKUP(B2,A10:B14,2,FALSE),VLOOKUP(A2,A10:B14,2,FALSE))

or in 2007

=IFERROR(VLOOKUP(A2,A10:B14,2,FALSE),VLOOKUP(B2,A10:B14,2,FALSE))
 
That worked. Thank you.

Shane Devenshire said:
Hi,

I am not comfortable with the column 1 containing "A B", is that really
what you have A followed by a bunch of spaces and then B?

If you have two values you want to check against the same table if the first
one is not found then

Replace
=VLOOKUP(OR(A2,B2),A10:B14,2,FALSE)
With
=IF(ISNA(VLOOKUP(A2,A10:B14,2,FALSE)),VLOOKUP(B2,A10:B14,2,FALSE),VLOOKUP(A2,A10:B14,2,FALSE))

or in 2007

=IFERROR(VLOOKUP(A2,A10:B14,2,FALSE),VLOOKUP(B2,A10:B14,2,FALSE))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire
Click to expand...
 
=IF(ISNA(VLOOKUP(A2,A10:B14,2,FALSE)),VLOOKUP(B2,A10:B14,2,FALSE),VLOOKUP(A2,A10:B14,2,FALSE))

That can be reduced to:

=VLOOKUP(IF(COUNTIF(A10:A14,A2),A2,B2),A10:B14,2,0)
 
Back
Top