Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Lookup
$40,001 $45,001 $50,001
$45,000 $50,000 $55,000 625 639 A A A 640 679 A A A 680 719 A B B 720 749 B B B 750 779 B C C 780 C C C Table range is A1:E8 I have this table above which tells me when an account has a total revenues between $40,001 - $45,000 AND a risk score between 625-639 then it's classified as an "A" type account. I have a list of accounts on a separate sheet that gives me the Revenues in column B and risk score in column C. In column D I want cross reference with the table above an provide the type of account. I'm having problems figuring out the ranges lookup in a function. Thanks for the help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Lookup
For tables, you only really need to see the START of each range, not the
start and finish. The start of the next tier already implies the previous tier has ended, understood? But, still keeping your current layout (though pretty much ignoring column B and row 2), the INDEX/MATCH formula on Sheet2 would be: =INDEX(Sheet1!$C$3:$E$8, MATCH($C2,Sheet1!$A$3:$A$8, 1), MATCH($B2,Sheet1!$C$1:$E$1, 1)) Does that help? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "JeffK" wrote: $40,001 $45,001 $50,001 $45,000 $50,000 $55,000 625 639 A A A 640 679 A A A 680 719 A B B 720 749 B B B 750 779 B C C 780 C C C Table range is A1:E8 I have this table above which tells me when an account has a total revenues between $40,001 - $45,000 AND a risk score between 625-639 then it's classified as an "A" type account. I have a list of accounts on a separate sheet that gives me the Revenues in column B and risk score in column C. In column D I want cross reference with the table above an provide the type of account. I'm having problems figuring out the ranges lookup in a function. Thanks for the help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Lookup
Assuming you don't have any revenues below 40,000 and risk scores are
above 624, then put this in D1 of Sheet2: =INDEX(Sheet1!$C$3:$E$8,MATCH(C1,Sheet1!$A$3:$A$8) ,MATCH(B1,Sheet1!$C $1:$E$1)) Copy it down as required. Hope this helps. Pete On Oct 24, 12:00*am, JeffK wrote: * * * * * * * * $40,001 $45,001 $50,001 * * * * * * * * $45,000 $50,000 $55,000 625 * * 639 * * A * * * A * * * A 640 * * 679 * * A * * * A * * * A 680 * * 719 * * A * * * B * * * B 720 * * 749 * * B * * * B * * * B 750 * * 779 * * B * * * C * * * C 780 * * * * * * C * * * C * * * C Table range is A1:E8 I have this table above which tells me when an account has a total revenues between $40,001 - $45,000 AND a risk score between 625-639 then it's classified as an "A" type account. I have a list of accounts on a separate sheet that gives me the Revenues in column B and risk score in column C. *In column D I want cross reference with the table above an provide the type of account. I'm having problems figuring out the ranges lookup in a function. Thanks for the help |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Lookup
Thanks JB
"JBeaucaire" wrote: For tables, you only really need to see the START of each range, not the start and finish. The start of the next tier already implies the previous tier has ended, understood? But, still keeping your current layout (though pretty much ignoring column B and row 2), the INDEX/MATCH formula on Sheet2 would be: =INDEX(Sheet1!$C$3:$E$8, MATCH($C2,Sheet1!$A$3:$A$8, 1), MATCH($B2,Sheet1!$C$1:$E$1, 1)) Does that help? -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "JeffK" wrote: $40,001 $45,001 $50,001 $45,000 $50,000 $55,000 625 639 A A A 640 679 A A A 680 719 A B B 720 749 B B B 750 779 B C C 780 C C C Table range is A1:E8 I have this table above which tells me when an account has a total revenues between $40,001 - $45,000 AND a risk score between 625-639 then it's classified as an "A" type account. I have a list of accounts on a separate sheet that gives me the Revenues in column B and risk score in column C. In column D I want cross reference with the table above an provide the type of account. I'm having problems figuring out the ranges lookup in a function. Thanks for the help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup data in a variable table & retrieve data from a pivot table | Excel Worksheet Functions | |||
Lookup table? | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Table Lookup | Excel Worksheet Functions | |||
Lookup Table | Excel Worksheet Functions |