IF with VLookup Formula

[Sundance_Kid]

Hi All,

Looking for help with a formula using an IF & Vlookup function please

I want to say if cell E8 = X then vlookup value in cell F8 in table array D2:F10,column 3, if E8 = Y, then vlookup value in cell F8 in table array D2:G10,column 4, if it is something else in cell E8 return "Check".

I can get the first part but thats it.

Thanks

 

[Eric W]

=IF(OR(E8={"X","Y"}),VLOOKUP(F8,OtherTab!D2:G10,3+(E8="Y"),0),"Check")

If E8 is X or Y, then VLOOKUP F8 in OtherTab!D2:G10 using the column of 3, plus 1 if E8 is Y, otherwise show "Check".

Probably the trickiest part is the 3+(E8="Y") part. (E8="Y") is a conditional expression which returns TRUE or FALSE. But if you use it in arithmetic, TRUE becomes 1 and FALSE becomes 0.

 

[Sundance_Kid]

Thank you both for your help

 

[Sundance_Kid]

=IF(OR(E8={"X","Y"}),VLOOKUP(F8,OtherTab!D2:G10,3+(E8="Y"),0),"Check")

If E8 is X or Y, then VLOOKUP F8 in OtherTab!D2:G10 using the column of 3, plus 1 if E8 is Y, otherwise show "Check".

Probably the trickiest part is the 3+(E8="Y") part. (E8="Y") is a conditional expression which returns TRUE or FALSE. But if you use it in arithmetic, TRUE becomes 1 and FALSE becomes 0.

Thanks

And you do different brackets too, instead of the standard bracks at the start I noticed? So by you saying E8="Y" it added 1 to the column 3 to give 4?

Thanks

 

[Eric W]

OK, the OR is the other tricky part! The curly brackets (or braces) {} are used to indicate an array constant. When Excel sees that, it performs the function on everything in the braces. So if E8 is Y, then E8={"X","Y"} evaluates to FALSE,TRUE. Then the OR will return TRUE if at least one of the elements in the list is TRUE. You could also rewrite that bit as IF(OR(E8="X",E8="Y"), ...

The (E8="Y") later in the formula uses the regular parentheses because we're only checking one value.