Reverse Lookup with Multiple Criteria

[JJG123]

Good Afternoon,

I am hoping the kind people of this forum can help me arrive to a solution. I have basic excel knowledge, but what I am trying to achieve is beyond the scope of my current understanding of excel formulas.

I need to do what I believe is referred to as a reverse lookup (to return header values), but the lookup needs to reference various criteria. The desired formula would be placed into Table 1 / column G (below) and use the corresponding row values to assign a Productivity Rating (1,2,3) from Table 2. The formula would need to reference and match the Year, Month, Business, and Segment values (especially important for Business and Segments).

Thanks in advance for any assistance that you can offer.


Table 1: Results Table ('Closed per Day' = Productivity)
[TABLE="class: grid, width: 510"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Business[/TD]
[TD]Segment[/TD]
[TD]Closed per Day[/TD]
[TD]Productivity Rating[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2019[/TD]
[TD]January[/TD]
[TD]A[/TD]
[TD]S1[/TD]
[TD]1.5[/TD]
[TD]Desired result = 1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2019[/TD]
[TD]January[/TD]
[TD]A[/TD]
[TD]S1[/TD]
[TD]3[/TD]
[TD]Desired result = 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2019[/TD]
[TD]January[/TD]
[TD]A[/TD]
[TD]S2[/TD]
[TD]2.5[/TD]
[TD]Desired result = 2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2019[/TD]
[TD]January[/TD]
[TD]A[/TD]
[TD]S2[/TD]
[TD]3[/TD]
[TD]Desired result = 3[/TD]
[/TR]
</tbody>[/TABLE]

Table 2: Ratings & Scales Table
- the ratings (1,2,3) are listed in the headers, and the 'Closed per Day' ranges are listed in the cells
[TABLE="class: grid, width: 510"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Year[/TD]
[TD]Month[/TD]
[TD]Business[/TD]
[TD]Segment[/TD]
[TD]Metric[/TD]
[TD]Category[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2019[/TD]
[TD]January[/TD]
[TD]A[/TD]
[TD]S1[/TD]
[TD]Business Results[/TD]
[TD]Productivity[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2019[/TD]
[TD]January[/TD]
[TD]A[/TD]
[TD]S1[/TD]
[TD]Business Results[/TD]
[TD]Other[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2019[/TD]
[TD]January[/TD]
[TD]A[/TD]
[TD]S2[/TD]
[TD]Business Results[/TD]
[TD]Productivity[/TD]
[TD]1.5[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2019[/TD]
[TD]January[/TD]
[TD]A[/TD]
[TD]S2[/TD]
[TD]Business Results[/TD]
[TD]Other[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

-JJG

 

[Domenic]

For simplicity, let's assume the following...

1) A1:F5 contains Table1

2) A9:I13 contains Table2

Try...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($G$9:$I$9,IFERROR(MATCH(E2,INDEX($G$10:$I$13,MATCH(1,IF($A$10:$A$13=A2,IF($B$10:$B$13=B2,IF($C$10:$C$13=C2,IF($D$10:$D$13=D2,IF($F$10:$F$13="Productivity",1))))),0),0),1),1))

However, when productivity falls below the range, shouldn't the rating be #N/A, as in not available? If so, try the following instead...

F2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($G$9:$I$9,MATCH(E2,INDEX($G$10:$I$13,MATCH(1,IF($A$10:$A$13=A2,IF($B$10:$B$13=B2,IF($C$10:$C$13=C2,IF($D$10:$D$13=D2,IF($F$10:$F$13="Productivity",1))))),0),0),1))

Hope this helps!

 

[JJG123]

It works! Thank you very much for responding to my post and offering your assistance!