Index-Match with Variable Match Column

[wiz329]

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"

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Location
[/TD]
[TD]Ranking Criteria #1[/TD]
[TD]Ranking Criteria #2[/TD]
[TD]Ranking Criteria #3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Entity #1[/TD]
[TD]1
[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Entity #2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Entity #3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


Now, suppose I have an output table on another sheet:

Sheet Name: "Output Sheet"

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Ranking[/TD]
[TD]Entity for Criteria #1[/TD]
[TD]Entity for Criteria #2[/TD]
[TD]Entity for Criteria #3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]Entity #1[/TD]
[TD]Entity #3[/TD]
[TD]Entity #3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]Entity #2[/TD]
[TD]Entity #1[/TD]
[TD]Entity #2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]Entity #3[/TD]
[TD]Entity #2[/TD]
[TD]Entity #1[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[wiz329]

I've tried isolating portions of the formula, and I've run across a puzzle:

=INDEX('Data Sheet'!$B$2:$D$4,0,2)

I simply evaluated the inner match function MATCH(B$1,'Data Sheet'!$B$1:$D$1,0) to 2 by deleting parts of it. It evaluates to an integer. However, when I copy the contents of the formula exactly, mind you), I get an error! It's the exact same formula! What's up with that?

 

[Peter_SSs]

Would you mind decomposing and explaining your formula?

I've never used an Index formula with the ROW NUMBER as 0?

Row_Num used in an INDEX like that means "all rows".

Take the formula in B2:
=INDEX('Data Sheet'!$A$2:$A$4,MATCH($A2,INDEX('Data Sheet'!$B$2:$D$4,0,MATCH(B$1,'Data Sheet'!$B$1:$D$1,0)),0))


=INDEX('Data Sheet'!$A$2:$A$4,
We are going to return one of the values from this range

MATCH($A2,
We are going to match the ranking criteria (1) in one of the columns from the Data sheet, but which column?

MATCH(B$1,'Data Sheet'!$B$1:$D$1,0)
Match the heading (Ranking Criteria #2) in B1:D1 of the Data Sheet. The match is at position 2


INDEX('Data Sheet'!$B$2:$D$4,0,MATCH(B$1,'Data Sheet'!$B$1:$D$1,0))
becomes
INDEX('Data Sheet'!$B$2:$D$4,0,2)

.. so we now match the position of the ranking (1) within all rows of column 2 of Data Sheet B2:B4.
This returns 3, so the final result is to return the 3rd value from A2:A4 of the Data Sheet (Entity #3)

Hope that made sense.

 

[Peter_SSs]

I've tried isolating portions of the formula, and I've run across a puzzle:

=INDEX('Data Sheet'!$B$2:$D$4,0,2)

If placed in a cell by itself, this will return an error, because it is trying to return an array of 3 cell values. Can't just do that into one cell.
However, if you select 3 vertical cells (we know its 3 because of the range in the formula), put that formula in the formula bar and confirm with Ctrl+Shift+Enter, not just Enter, you should see the 3-cell results.

 

[wiz329]

If placed in a cell by itself, this will return an error, because it is trying to return an array of 3 cell values. Can't just do that into one cell.
However, if you select 3 vertical cells (we know its 3 because of the range in the formula), put that formula in the formula bar and confirm with Ctrl+Shift+Enter, not just Enter, you should see the 3-cell results.

Understood. However, why am I able to delete parts of the formula, such that all that is left in the one cell is that formula, and it evaluates to 1.

However, when I copy and paste the same exact formula (not copying and pasting the cell, the actual text in the formula), it returns an error.

Why would the same exact formula yield an error in one situation, but not the other?

PS I ctrl+shift+entered the cell that was yielding an error, and it evaluated properly. So I thought it might be the the cell that was already evaluating properly might be an array formula. But it wasn't!

 

[Peter_SSs]

Sorry, I've lost track of exactly what you have done so I can't answer at the moment.

1. Are we still dealing with the sample data/layout as shown in post #6?

2. What is the modified formula that 'works', and what sheet and cell is it in?

3. What sheet and cell did you copy that formula text to such that it yielded an error?

 

[wiz329]

Sorry, I've lost track of exactly what you have done so I can't answer at the moment.

1. Are we still dealing with the sample data/layout as shown in post #6?

2. What is the modified formula that 'works', and what sheet and cell is it in?

3. What sheet and cell did you copy that formula text to such that it yielded an error?

1. Yes we are talking about the same exact data set.

2. The formula that works are exactly identical (except for how they were "created". By that I mean the formula (in cell F4, just a random cell) was created by deleting parts of the original, complete formula, whereas the formula that yields an error, was simply copied and pasted text from the formula that "works" in the cell immediately below, F5).

3. In the output sheet.

Not sure what the rules are, but would it be possible to email you the file? That way you could see first hand in the actual (sample) workbook.

The formula that both does and doesn't work is:

=INDEX('Data Sheet'!$B$2:$D$4,0,2)

Whether or not it works also seems to be dependent on the order in which the original formula is deleted (and the match function replaced with a 2).

 

[Peter_SSs]

The results are nothing to do with the order you have 'reduced' the original formula.
As I said before, that formula is trying to return the results from 3 cells, which it cannot do in a single cell.

Speaking of the 'Output Sheet', that formula, placed anywhere in
- row 2 returns 2
- row 3 returns 3
- row 4 returns 1
- any other row returns an error

Given that the formula cannot do what it is being asked to do, it 'guesses' what might be next best and says:
OK, you are referencing rows 2:4 in the 'Data Sheet'. If you place me in row 4 then I will return the value from that row (4) in the referenced range (result = 1) etc
If you place me in a row that does not correspond to a row in the referenced range, I have no idea what you want so I'll give you an error.

 

[wiz329]

The results are nothing to do with the order you have 'reduced' the original formula.
As I said before, that formula is trying to return the results from 3 cells, which it cannot do in a single cell.

Speaking of the 'Output Sheet', that formula, placed anywhere in
- row 2 returns 2
- row 3 returns 3
- row 4 returns 1
- any other row returns an error

Given that the formula cannot do what it is being asked to do, it 'guesses' what might be next best and says:
OK, you are referencing rows 2:4 in the 'Data Sheet'. If you place me in row 4 then I will return the value from that row (4) in the referenced range (result = 1) etc
If you place me in a row that does not correspond to a row in the referenced range, I have no idea what you want so I'll give you an error.

That makes sense. Thank you for helpful explanations!

Austin