OR Function in VLookup

[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

 

[Mike H]

Post your formula even if it's not working

Mike

 

[Biff]

Here you go.

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

 

[Lars-Åke Aspelin]

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

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

 

[xlm]

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

 

[Mike H]

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

Here you go.

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

Post your formula even if it's not working

Mike

 

[Biff]

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?

 

[Shane Devenshire]

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))

 

[Biff]

That worked. Thank you.

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

 

[T. Valko]

=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)