Table Lookup

[JeffK]

$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

 

[JBeaucaire]

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?

 

[Pete_UK]

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

 

[JeffK]

Thanks JB

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.