# Reverse Lookup with Multiple Criteria

#### JJG123

##### New Member
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)
 A B D E F G 1 Year Month Business Segment Closed per Day Productivity Rating 2 2019 January A S1 1.5 Desired result = 1 3 2019 January A S1 3 Desired result = 2 4 2019 January A S2 2.5 Desired result = 2 5 2019 January A S2 3 Desired result = 3

<tbody>
</tbody>

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
 A B C D E F G H I 1 Year Month Business Segment Metric Category 1 2 3 2 2019 January A S1 Business Results Productivity 2 3 4 3 2019 January A S1 Business Results Other 4 2019 January A S2 Business Results Productivity 1.5 2 3 5 2019 January A S2 Business Results Other

<tbody>
</tbody>

-JJG

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
For simplicity, let's assume the following...

Code:
``````1) A1:F5 contains Table1

2) A9:I13 contains Table2``````

Try...

Code:
``````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...

Code:
``````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!

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

Replies
1
Views
379
Replies
26
Views
987
Replies
4
Views
580
Replies
4
Views
143
Replies
0
Views
127

1,203,356
Messages
6,054,938
Members
444,759
Latest member
TeckTeck

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back