Index-Match with Variable Match Column

wiz329

New Member
Joined
Jun 4, 2014
Messages
43
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"

ABCD
1Location
Ranking Criteria #1Ranking Criteria #2Ranking Criteria #3
2Entity #11
23
3Entity #2232
4Entity #3311

<tbody>
</tbody>


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

Sheet Name: "Output Sheet"

ABCD
1RankingEntity for Criteria #1Entity for Criteria #2Entity for Criteria #3
21Entity #1Entity #3Entity #3
32Entity #2Entity #1Entity #2
43Entity #3Entity #2Entity #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.
 
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?
 
Upvote 0

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).
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. :)
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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?
 
Last edited:
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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?

Disable AdBlock

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
Back
Top