This one has me stumped.
Suppose I have a list of Locations with rankings according to different criteria (this is my "data table"):
Sheet Name: "Data Sheet"
<tbody>
</tbody>
Now, suppose I have an output table on another sheet:
Sheet Name: "Output Sheet"
<tbody>
</tbody>
Basically, I need the formula to return the cells in red letters, but I can't quite figure it out. It seems to be a trivial Index-Match formula. However, since I want a *single formula* (rather than a separate one for each column in the output sheet), I'm not quite sure how to do it -- it seems like it would require a variable columns against which to match (B, C, or D on "Data Sheet", depending on which Criteria we are using).
In other words, I want to input the Criteria Name and the Rank, and receive the Entity Name as output. Let me know if this doesn't make sense.
Suppose I have a list of Locations with rankings according to different criteria (this is my "data table"):
Sheet Name: "Data Sheet"
A | B | C | D | |
1 | Location | Ranking Criteria #1 | Ranking Criteria #2 | Ranking Criteria #3 |
2 | Entity #1 | 1 | 2 | 3 |
3 | Entity #2 | 2 | 3 | 2 |
4 | Entity #3 | 3 | 1 | 1 |
<tbody>
</tbody>
Now, suppose I have an output table on another sheet:
Sheet Name: "Output Sheet"
A | B | C | D | |
1 | Ranking | Entity for Criteria #1 | Entity for Criteria #2 | Entity for Criteria #3 |
2 | 1 | Entity #1 | Entity #3 | Entity #3 |
3 | 2 | Entity #2 | Entity #1 | Entity #2 |
4 | 3 | Entity #3 | Entity #2 | Entity #1 |
<tbody>
</tbody>
Basically, I need the formula to return the cells in red letters, but I can't quite figure it out. It seems to be a trivial Index-Match formula. However, since I want a *single formula* (rather than a separate one for each column in the output sheet), I'm not quite sure how to do it -- it seems like it would require a variable columns against which to match (B, C, or D on "Data Sheet", depending on which Criteria we are using).
In other words, I want to input the Criteria Name and the Rank, and receive the Entity Name as output. Let me know if this doesn't make sense.