Using IF statement in a VLOOKUP

[wormburner]

I created a VLOOKUP that includes an IF statement. However, I need to be able
to reference 4 possible scenarios not only 2. Currently
,(IF($J$1=1,6,(IF($J$1=2,7)))) works. I need it to also ask IF($J$1=3,8) and
IF($J$1=4,9) in some sort of formula.

I have provided my formula below.

=VLOOKUP($A7,$A$4:$I$3000,(IF($J$1=1,6,(IF($J$1=2,7)))),FALSE).

Any help will be appreciated.

 

[Pete_UK]

You seem to be adding 5 onto J1 as long as J1 is 1 to 4. So, you could
reduce the number of IFs like this:

=VLOOKUP($A7,$A$4:$I$3000,IF(AND($J$1>=1,$J$1<=4),5+$J$1,1),FALSE)

The IF returns 1 if J1 is not valid, which in turn will return the
lookup value ($A7). There is no error checking here to see if A7 does
exist in A4:A3000 (which it obviously does !!).

Hope this helps.

Pete

 

[Daryl S]

Wormburner -

If the lookup always returns a number 5 greater, try this:

=VLOOKUP($A7,$A$4:$I$3000,($J$1+5),FALSE)

Otherwise, if $J$1 is always a small integer, you can use a CHOOSE function,
like this:

=VLOOKUP($A7,$A$4:$I$3000,CHOOSE($J$1,6,7,8,9),FALSE)