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)
<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
<tbody>
</tbody>
-JJG
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
