Choose line label according to two-parameter lookup

brunothomas

New Member
Hello guys,

Every month I need to distribute a number of companies to be assessed by diferently graded analysts. This distribution is based on a matrix that attributes authority according to the internal rating/grade that a given company has and also what exposure that company holds.

 Analyst\Grade 1 2 3 4 5 6 7 8 9 10 Jr Analyst 1 4.000.000 4.000.000 4.000.000 1.600.000 1.200.000 800.000 0 0 0 0 Jr Analyst 2 8.000.000 8.000.000 8.000.000 3.200.000 2.400.000 1.600.000 0 0 0 0 Analyst 16.000.000 16.000.000 16.000.000 8.000.000 3.200.000 2.400.000 1.120.000 0 0 0 Senior Analyst 36.000.000 36.000.000 36.000.000 24.000.000 15.000.000 7.500.000 2.400.000 0 0 0 AVP, Analyst 0 0 0 0 0 0 0 0 0 0 Manager 56.000.000 56.000.000 56.000.000 40.000.000 27.000.000 15.000.000 6.000.000 640.000 640.000 640.000 Director 112.000.000 112.000.000 112.000.000 80.000.000 48.000.000 22.400.000 96.000.000 3.000.000 3.000.000 3.000.000

<tbody>
</tbody>

For instance, Jr Analysts 1 are only authorized to assess a Grade 5 company that holds up to \$1.200.000 in exposure. Grade 5 companies with larger exposures need to be escalated to analysts with higher authority.

 Assessment Distribution Exposure Grade Analyst Company 1 2.700.000 4 ? Company 2 8.500.000 7 ? Company 3 56.000.000 5 ? Company 4 500.000 8 ?

<tbody>
</tbody>

What I'm trying to is build a formula that returns in the column "Analyst" the lowest-graded analyst station that has authority to assess each of those companies (in the first case, Company 1 with 2.7MM and Grade 4 should return "Jr Analyst 2").

No_Need_For_Macros

New Member
Bruno,

I have a series of formulas that work, but please note that it is far from optimized. Furthermore, there are probably plenty of people able to help with a VBA solution.

**I could not use a 'single cell' formula to solve your issue due to the fact that I use an array formula to find the minimum acceptable exposure. This, in turn, required me to use indirect references to data in other cells.**

For reference in my test workbook I placed Company 1 in Column A:Row14, Exposure in Column B, etc. Analyst\Grade are Column A:Row 1

In Cell E14 is "Range End": =ADDRESS(8,C14+1,1,1). The number 8 here is because you have 7 total Analyst Types. If you have more, you will need to change this number
In Cell F14 is "Min Exposure": {=MIN(IF(INDIRECT(D14):INDIRECT(E14)>B14,INDIRECT(D14):INDIRECT(E14)))}. This is where I needed an Array formula noted above.
In Cell G14 is "Exposure Row": =MATCH(F14,INDIRECT(D14,1):INDIRECT(E14,1),0)
In Cell H14 is "Analyst": =IF(F14=0,"None",INDEX(\$A\$2:\$A\$8,G14,1)). Similar to "Range End", this Index ends at A8. In one case, no Analyst could be used, so "None" is displayed.

Copy these formulas down for how ever many companies you have. And, again, I'm sure someone can clean this up, but things get too confusing for my brain when I'm mixing regular Formulas with Array Formulas. Cheers!

Peter_SSs

MrExcel MVP, Moderator
Welcome to the MrExcel board!

Try this, copied down.

Excel Workbook
ABCDEFGHIJK
2Jr Analyst 1400000040000004000000160000012000008000000
3Jr Analyst 28000000800000080000003200000240000016000000000
4Analyst1600000016000000160000008000000320000024000001120000000
5Senior Analyst360000003600000036000000240000001500000075000002400000000
6AVP, Analyst0000000000
7Manager5600000056000000560000004000000027000000150000006000000640640640
8Director11200000011200000011200000080000000480000002240000096000000300000030000003000000
9
10
11Assessment Distribution
13Company 127000004
14Company 285000007Director
15Company 3560000005Nobody qualified
16Company 45008Manager
 Analyst

brunothomas

New Member
Welcome to the MrExcel board!

Try this, copied down.
Hello, Peter!

This worked perfectly. Thank you so much!

Bruno

Last edited by a moderator:

Peter_SSs

MrExcel MVP, Moderator
Hello, Peter!

This worked perfectly. Thank you so much!

Bruno
You are very welcome.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only. I've 'pruned' your previous post.

1,078,213
Messages
5,338,891
Members
399,265
Latest member
aj17x55