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

 

[Fluff]

Maybe
=IF(OR(E8<>{"X","Y"}),"Check",VLOOKUP(F8,D2:F10,IF(E8="X",3,4),0))

But why are you using values in the lookup table?

 

[Eric W]

Maybe:

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

The description is a little odd, since your E8 and F8 values are in the middle of your table.

 

[Fluff]

Oops, mine should have been
=IF(AND(E8<>{"X","Y"}),"Check",VLOOKUP(F8,D2:F10,IF(E8="X",3,4),0))

 

[Eric W]

Fluff, and you need to change the range to D2:G10.

 

[Sundance_Kid]

Sorry the values in the lookup table are in a different tab.

This is what I am trying to do, but i know its wrong.

=IF(E8="X",VLOOKUP(F8,OtherTab!$D$2:$F$10,3,IF(E8="Y",VLOOKUP(F8,OtherTab!$D$2:$G$10,4,"Check"))))

 

[Fluff]

Fluff, and you need to change the range to D2:G10.

Thanks Eric
Made a complete mess of that didn't I.
=IF(AND(E8<>{"X","Y"}),"Check",VLOOKUP(F8,D2:G10,IF(E8="X",3,4),0))

 

[Eric W]

You weren't too far off with your formula:

=IF(E8="X",VLOOKUP(F8,OtherTab!$D$2:$F$10,3),IF(E8="Y",VLOOKUP(F8,OtherTab!$D$2:$G$10,4),"Check"))

but Fluff's or my version would shorten it a bit:

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

 

[Fluff]

@Sundance_Kid
You have a couple of the brackets in the wrong place
=IF(E8="X",VLOOKUP(F8,OtherTab!$D$2:$F$10,3),IF(E8="Y",VLOOKUP(F8,OtherTab!$D$2:$G$10,4),"Check"))
At the moment your lookup is doing an approximate match, rather than an exact match, not sure if that's what you wanted.

 

[Sundance_Kid]

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

Thank you.

What exactly is your formula saying (in simple English) please?

Thanks