Forum Discussion
Solved
How to use LOOKUP to refer to tables related to specific ranges of values in a nominated cell
I want to modify the LOOKUP entry in F16 , =LOOKUP(ABS(F14),K6:K26,L6:L26), (24 shown), so that it refers to and selects the relevant column and value from Tables A, B, C or D according to the value ...
Hello Alcesterman ,
In the attached file, I modified K27, V30, AB30, AH30, and AN30 from 40+ to 41. The formula in F16, copied across to I16, is:
=INDEX(($V9:$Z30,$AB9:$AF30,$AH9:$AL30,$AN9:$AR30),
MATCH(F14,CHOOSE(SUM(F8>0,F8>24,F8>25,F8>32),$V9:$V30,$AB9:$AB30,$AH9:$AH30,$AN9:$AN30),1),
COLUMN(B1),SUM(F8>0,F8>24,F8>25,F8>32))Cheers!
Twifoo
Hi,
As the basis you may modify your formula as
=LOOKUP(ABS(F14),OFFSET($K$6,0,0,21),OFFSET($L$6,0,0,21))
With nested IF or another lookup find on how many columns you shall shift your ranges and use that formula instead of second zero in each OFFSET. And be careful with absolute and relative references.
many thanks, I will look at this.