Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Table Lookup
I have a spreadsheet that looks something like this:
Coverage Amount AGE 0 - 40 41 - 45 46 - 50 51 - 60 61+ 0 - 40,000 A A A A B 41,001 - 99,999 A A A B B 100,000 - 199,999 A A A F F 200,000 - 249,999 A A F G G 250,000 - 300 000 E F F G H 300,000 - 499,999 F F G G H 500,000 F F G G H 500,001 - 750,000 F G G G H 750,001 - 1,000,000 G G G H H 1,000,001 - 1,500,000 G G H H H 1,5,000,001 - 2,000,000 G H H H H 2,000,001 - and up H H H H H A Procedure 1 B Procedure 2 C Procedure 3 D Procedure 4 E Procedure 5 F Procedure 6 G Procedure 7 H Procedure 8 ISSUE: I need to be able to lookup the coverage amount range, (e.g. the coverage could be 200,003), lookup the age (for example 43) and return what procedure is to be followed (to complete the example 200,003 for a 43 year old should return "Procedure 1". Any specific help? |
#2
|
|||
|
|||
Assumptions:
A4:B15 contains your coverage amount range C3:G3 contains your age brackets C4:G15 contains your data A17:B24 contains your 'Procedure" table Formula: =LOOKUP(INDEX(C4:G15,MATCH(D17,A4:A15),MATCH(E17,{ 0,41,46,51,61})),A17:B2 4) ....where D17 contains the coverage amount, and E17 contains the age of interest. Hope this helps! In article , Rod wrote: I have a spreadsheet that looks something like this: Coverage Amount AGE 0 - 40 41 - 45 46 - 50 51 - 60 61+ 0 - 40,000 A A A A B 41,001 - 99,999 A A A B B 100,000 - 199,999 A A A F F 200,000 - 249,999 A A F G G 250,000 - 300 000 E F F G H 300,000 - 499,999 F F G G H 500,000 F F G G H 500,001 - 750,000 F G G G H 750,001 - 1,000,000 G G G H H 1,000,001 - 1,500,000 G G H H H 1,5,000,001 - 2,000,000 G H H H H 2,000,001 - and up H H H H H A Procedure 1 B Procedure 2 C Procedure 3 D Procedure 4 E Procedure 5 F Procedure 6 G Procedure 7 H Procedure 8 ISSUE: I need to be able to lookup the coverage amount range, (e.g. the coverage could be 200,003), lookup the age (for example 43) and return what procedure is to be followed (to complete the example 200,003 for a 43 year old should return "Procedure 1". Any specific help? |
#3
|
|||
|
|||
The "Amount" Lookup is working well, however, the "Age" lookup is not. I
tried age 42 and above and it returns #N/A. The age row is as such: D3 E3 F3 G3 H3 I3 J3 K3 L3 M3 N3 O3 P3 0 - 40 41 - 45 46 - 50 51 - 60 61+ Thoughts? "Domenic" wrote: Assumptions: A4:B15 contains your coverage amount range C3:G3 contains your age brackets C4:G15 contains your data A17:B24 contains your 'Procedure" table Formula: =LOOKUP(INDEX(C4:G15,MATCH(D17,A4:A15),MATCH(E17,{ 0,41,46,51,61})),A17:B2 4) ....where D17 contains the coverage amount, and E17 contains the age of interest. Hope this helps! In article , Rod wrote: I have a spreadsheet that looks something like this: Coverage Amount AGE 0 - 40 41 - 45 46 - 50 51 - 60 61+ 0 - 40,000 A A A A B 41,001 - 99,999 A A A B B 100,000 - 199,999 A A A F F 200,000 - 249,999 A A F G G 250,000 - 300 000 E F F G H 300,000 - 499,999 F F G G H 500,000 F F G G H 500,001 - 750,000 F G G G H 750,001 - 1,000,000 G G G H H 1,000,001 - 1,500,000 G G H H H 1,5,000,001 - 2,000,000 G H H H H 2,000,001 - and up H H H H H A Procedure 1 B Procedure 2 C Procedure 3 D Procedure 4 E Procedure 5 F Procedure 6 G Procedure 7 H Procedure 8 ISSUE: I need to be able to lookup the coverage amount range, (e.g. the coverage could be 200,003), lookup the age (for example 43) and return what procedure is to be followed (to complete the example 200,003 for a 43 year old should return "Procedure 1". Any specific help? |
#4
|
|||
|
|||
That's because I assumed that each age bracket is contained in one cell.
But I see now that this is not the case. From what I can tell from your original post, the data for each age bracket is contained in Columns D, G, J, M, and P. If this is the case, replace this part of the formula... MATCH(E17,{0,41,46,51,61}) ....with the following... CHOOSE(MATCH(E17,{0,41,46,51,61}),1,4,7,10,13) Does this help? In article , Rod wrote: The "Amount" Lookup is working well, however, the "Age" lookup is not. I tried age 42 and above and it returns #N/A. The age row is as such: D3 E3 F3 G3 H3 I3 J3 K3 L3 M3 N3 O3 P3 0 - 40 41 - 45 46 - 50 51 - 60 61+ Thoughts? "Domenic" wrote: Assumptions: A4:B15 contains your coverage amount range C3:G3 contains your age brackets C4:G15 contains your data A17:B24 contains your 'Procedure" table Formula: =LOOKUP(INDEX(C4:G15,MATCH(D17,A4:A15),MATCH(E17,{ 0,41,46,51,61})),A17:B2 4) ....where D17 contains the coverage amount, and E17 contains the age of interest. Hope this helps! In article , Rod wrote: I have a spreadsheet that looks something like this: Coverage Amount AGE 0 - 40 41 - 45 46 - 50 51 - 60 61+ 0 - 40,000 A A A A B 41,001 - 99,999 A A A B B 100,000 - 199,999 A A A F F 200,000 - 249,999 A A F G G 250,000 - 300 000 E F F G H 300,000 - 499,999 F F G G H 500,000 F F G G H 500,001 - 750,000 F G G G H 750,001 - 1,000,000 G G G H H 1,000,001 - 1,500,000 G G H H H 1,5,000,001 - 2,000,000 G H H H H 2,000,001 - and up H H H H H A Procedure 1 B Procedure 2 C Procedure 3 D Procedure 4 E Procedure 5 F Procedure 6 G Procedure 7 H Procedure 8 ISSUE: I need to be able to lookup the coverage amount range, (e.g. the coverage could be 200,003), lookup the age (for example 43) and return what procedure is to be followed (to complete the example 200,003 for a 43 year old should return "Procedure 1". Any specific help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
How do I lookup a table from right to left ? | Excel Worksheet Functions | |||
lookup or table, not sure what to do | Excel Worksheet Functions | |||
Index table lookup anomaly | Excel Worksheet Functions | |||
Lookup Table | Excel Worksheet Functions |