Vlookup and IF statments

[silkpantsman]

Hi,

Im trying to use the Vlookup function with IF conditions for the array but to no avail:

=VLOOKUP(A198,IF(AND(Dashboard!R2="Revenue",Dashboard!T2="10x10"),$A$42:$N$51,IF(AND(Dashboard!R2="Revenue",Dashboard!T2="5x5"),$A$91:$N$100,IF(AND(Dashboard!R2="Revenue",Dashboard!T2="5x20"),$A$139:$N$148,IF(AND(Dashboard!R2="Units",Dashboard!T2="10x10"),$A$58:$N$67,IF(AND(Dashboard!R2="Units",Dashboard!T2="5x5"),$A$107:$N$116,IF(AND(Dashboard!R2="Units",Dashboard!T2="5x20"),$A$155:$N$164,IF(AND(Dashboard!R2="Price",Dashboard!T2="10x10"),$A$74:$N$83,IF(AND(Dashboard!R2="Price",Dashboard!T2="5x5"),$A$123:$N$132,IF(AND(Dashboard!R2="Price",Dashboard!T2="5x20"),$A$171:$N$180,""))))))))),6,FALSE)

is there a limit on the amount of IF's that can be used. I would also like to use IF's for the Col_index_num can this be done or will i need to use VBA? Should i be putting the Vlookup inside the if statement instead?

Thanks,
SPM

[The_Snook]

Hi SPM,

It's always easier to get assistance if you provide an example workbook.

Regards,

Snook

[Richard Buttrey]

Hi,

I suggest you upload the workbook so that we can see the request in context. The limit is I believe 64 Ifs but in reality of you ever find yourself using more than half a dozen I'd suggest looking for an alternative solution.

It doesn't appear that you have a default lookup range if none of the Ifs are true, but upload the workbook for a more considered view.

[JohnTopley]

I used an INDEX/MATCH based on a table lookup in X2:Y10 on the "Dashboard" sheet (using your VLOOKUP formula as a guide)

=INDEX($A$42:$N$180,(MATCH(1,(R2=X2:X10)*(T2=Y2:Y10),0)-1)*16+1,6)

Entered with Ctrl+Shift+Enter

See the "Dashboard" sheet.

I have queried the ranges as they appear to be blocks of 16 but appear to skip a row: highlighted in "Dashboard").

[sktneer]

You can also use the CHOOSE function to select the table array as per the conditions like this.......

Please Login or Register  to view this content.