Vlookup and IF statments

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

Hi SPM,

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

Regards,

Snook

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