Table Lookup

J

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
 
J

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?
 
P

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
 
J

JeffK

Thanks JB

JBeaucaire said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top