Forum Discussion
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 in F8 (24 shown).
I want it to use Table A if F8<25, Table B if F8=25, Table C if F8>25 and <33, Table D if F8>32.
As highlighted, only Table A is referenced, and the value from F14 (24) is 'read' from Table A, W13 (120).
In the example, the value for G8 is 25 and the values should be read from Table B, in H8 the value is 26, so reference should be made to Table C and finally, I8 (33) Table D should be referenced.
For correspondents who play Bridge, the scoresheet allows 4 players to simulate a competitive Teams event and generate a 'Par Score' to compare with the actual Duplicate result at the table - an improvement (I feel), on simply using Chicago scoring. (My Intellectual Copyright)
Thank you,
Alf
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
14 Replies
Just in case a non-standard approach is of interest. I rely entirely upon defined names and never use the standard cell referencing by location. My first step would be to build a defined name 'case' that will indicate which table A-D is to be to used for subsequent lookups.
The definition of 'case'
= 2 + SIGN( combinedHCP-25)+ ( combinedHCP>32)
is a bit of a dog but it sets the scene for the named reference 'table' that is defined by
= CHOOSE( case, tableA, tableB, tableC, tableD )
From there on, one can ignore the fact that 4 tables are involved and simple build the lookup formula as if 'table' were a straightforward range reference. Individual column names can be defined to refer to
= INDEX( table, 0, 1 )
etc. as required.
- Many thanks, This looks very interesting (I like a challenge!), Alf
I did implement a solution but I had overlooked the fact that your longest suit length etc. changed as the vulnerability or contract suit changed across the columns.
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. Twifoo gave me a solution
- many thanks, I will look at this.
