Choose line label according to two-parameter lookup

brunothomas

New Member
Joined
Oct 4, 2018
Messages
2
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\Grade12345678910
Jr Analyst 14.000.0004.000.0004.000.0001.600.0001.200.000800.0000000
Jr Analyst 28.000.0008.000.0008.000.0003.200.0002.400.0001.600.0000000
Analyst16.000.00016.000.00016.000.0008.000.0003.200.0002.400.0001.120.000000
Senior Analyst36.000.00036.000.00036.000.00024.000.00015.000.0007.500.0002.400.000000
AVP, Analyst0000000000
Manager56.000.00056.000.00056.000.00040.000.00027.000.00015.000.0006.000.000640.000640.000640.000
Director112.000.000112.000.000112.000.00080.000.00048.000.00022.400.00096.000.0003.000.0003.000.0003.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
ExposureGradeAnalyst
Company 12.700.0004?
Company 28.500.0007?
Company 356.000.0005?
Company 4500.0008?

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

Thanks in advance!
 

No_Need_For_Macros

New Member
Joined
Sep 29, 2018
Messages
14
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,785
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Try this, copied down.

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,785
Office Version
365
Platform
Windows
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.
 

Forum statistics

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

Some videos you may like

This Week's Hot Topics

Top