Choose line label according to two-parameter lookup

[brunothomas]

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.

[TABLE="width: 961"]
<tbody>[TR]
[TD]Analyst\Grade[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD]Jr Analyst 1[/TD]
[TD="align: right"]4.000.000[/TD]
[TD="align: right"]4.000.000[/TD]
[TD="align: right"]4.000.000[/TD]
[TD="align: right"]1.600.000[/TD]
[TD="align: right"]1.200.000[/TD]
[TD="align: right"]800.000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Jr Analyst 2[/TD]
[TD="align: right"]8.000.000[/TD]
[TD="align: right"]8.000.000[/TD]
[TD="align: right"]8.000.000[/TD]
[TD="align: right"]3.200.000[/TD]
[TD="align: right"]2.400.000[/TD]
[TD="align: right"]1.600.000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Analyst[/TD]
[TD="align: right"]16.000.000[/TD]
[TD="align: right"]16.000.000[/TD]
[TD="align: right"]16.000.000[/TD]
[TD="align: right"]8.000.000[/TD]
[TD="align: right"]3.200.000[/TD]
[TD="align: right"]2.400.000[/TD]
[TD="align: right"]1.120.000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Senior Analyst[/TD]
[TD="align: right"]36.000.000[/TD]
[TD="align: right"]36.000.000[/TD]
[TD="align: right"]36.000.000[/TD]
[TD="align: right"]24.000.000[/TD]
[TD="align: right"]15.000.000[/TD]
[TD="align: right"]7.500.000[/TD]
[TD="align: right"]2.400.000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]AVP, Analyst[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[TD="align: right"]56.000.000[/TD]
[TD="align: right"]56.000.000[/TD]
[TD="align: right"]56.000.000[/TD]
[TD="align: right"]40.000.000[/TD]
[TD="align: right"]27.000.000[/TD]
[TD="align: right"]15.000.000[/TD]
[TD="align: right"]6.000.000[/TD]
[TD="align: right"]640.000[/TD]
[TD="align: right"]640.000[/TD]
[TD="align: right"]640.000[/TD]
[/TR]
[TR]
[TD]Director[/TD]
[TD="align: right"]112.000.000[/TD]
[TD="align: right"]112.000.000[/TD]
[TD="align: right"]112.000.000[/TD]
[TD="align: right"]80.000.000[/TD]
[TD="align: right"]48.000.000[/TD]
[TD="align: right"]22.400.000[/TD]
[TD="align: right"]96.000.000[/TD]
[TD="align: right"]3.000.000[/TD]
[TD="align: right"]3.000.000[/TD]
[TD="align: right"]3.000.000[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 429"]
<tbody>[TR]
[TD="colspan: 2"]Assessment Distribution[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Exposure[/TD]
[TD]Grade[/TD]
[TD]Analyst[/TD]
[/TR]
[TR]
[TD]Company 1[/TD]
[TD]2.700.000[/TD]
[TD]4[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Company 2[/TD]
[TD]8.500.000[/TD]
[TD]7[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Company 3[/TD]
[TD]56.000.000[/TD]
[TD]5[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Company 4[/TD]
[TD]500.000[/TD]
[TD]8[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

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").

Thanks in advance!

 

[No_Need_For_Macros]

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 D14 (Next to "Grade") is "Range Start": =ADDRESS(2,C14+1,1,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]

Welcome to the MrExcel board!

Try this, copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Analyst\Grade	1	2	3	4	5	6	7	8	9	10
2	Jr Analyst 1	4000000	4000000	4000000	1600000	1200000	800	0	0	0	0
3	Jr Analyst 2	8000000	8000000	8000000	3200000	2400000	1600000	0	0	0	0
4	Analyst	16000000	16000000	16000000	8000000	3200000	2400000	1120000	0	0	0
5	Senior Analyst	36000000	36000000	36000000	24000000	15000000	7500000	2400000	0	0	0
6	AVP, Analyst	0	0	0	0	0	0	0	0	0	0
7	Manager	56000000	56000000	56000000	40000000	27000000	15000000	6000000	640	640	640
8	Director	112000000	112000000	112000000	80000000	48000000	22400000	96000000	3000000	3000000	3000000
9
10
11	Assessment Distribution
12		Exposure	Grade	Analyst
13	Company 1	2700000	4	Jr Analyst 2
14	Company 2	8500000	7	Director
15	Company 3	56000000	5	Nobody qualified
16	Company 4	500	8	Manager
Analyst

 

[brunothomas]

Welcome to the MrExcel board!

Try this, copied down.

Hello, Peter!

This worked perfectly. Thank you so much!

Bruno

 

[Peter_SSs]

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.