Forum Discussion
How to use LOOKUP to refer to tables related to specific ranges of values in a nominated cell
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
file dropbox link:
https://www.dropbox.com/s/5at72u390w3b7th/PAR%20BRIDGE%20-%20PAR%20SCORE%20CALCULATOR%201909.xlsx?dl=0
As I said, which table is referred to depends on the value in cell row 8 (f,g,h or i)
If the value in cell f8, g8, h8 or i8 is <25 , table A should be used to look up from the corresponding value for row 14 (f to i)
If = 25, then Table B
If in range 26 - 32, then Table C
and if greater or equal to 33, then table D
The choice of which column is used (f, g, h or i) is determined by the user (according to the final Bridge contract and the Bridge vulnerability)
The values in Rows 6, 7, 10 and 12 are entered by the user. Excel determines the 'Par Value' in 16.
the User enters the actual score in 18
Excel calculates the Net score and Looks up the IMPs (Internnational Match Points) equivalent by LOOKUP in Table E
Simple concept! Not so simple to set up to work automatically. I like an intellectual challenge and appreciate the help I am getting.
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
- Twifoo is a star!
The formula works - not sure how - but it works! (Now my intellectual challenge is to see how it works.)
Many thanks (and a game of bridge if you come near Alcester in the UK!)
Many thanks to all who advised.
Alcesterman Twifoo is a star!
The formula works - not sure how - but it works! (Now my intellectual challenge is to see how it works.)
Many thanks (and a game of bridge if you come near Alcester in the UK!)
Many thanks to all who advised.
Alcesterman
- The formula uses the reference form of INDEX. CHOOSE returns the lookup_array argument of MATCH. The first SUM returns the index_num argument of CHOOSE. The second SUM returns the area_num argument of INDEX.
- Q.E.D.
(as I was taught at school 70 years ago!)
Many thanks