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"

	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.

 

[wiz329]

EDIT:

I realize I could reorganize "Data Sheet" as follows, and lookup Column C from the combination of A and B:

	A	B	C
1	Criteria	Rank	Entity
2	Ranking Criteria #1	1	Entity #1
3	Ranking Criteria #1	2	Entity #2
4	Ranking Criteria #1	3	Entity #3
5	Ranking Criteria #2	1	Entity #3
6	Ranking Criteria #2	2	Entity #1
7	Ranking Criteria #2	3	Entity #2
8	Ranking Criteria #3	1	Entity #3
9	Ranking Criteria #3	2	Entity #2
10	Ranking Criteria #3	3	Entity #1

<tbody>
</tbody>

However, this would require significant effort to reorganize the data, and I'd really like to see if there's a good way to pull the data I need in it's current format.

Thanks in advance.

 

[FDibbins]

Try this, copied down and across...
=INDEX($A$2:$A$4,MATCH(SMALL(B$2:B$4,ROWS($A$1:A1)),B$2:B$4,0))

I did this on the same sheet, you just need to add the sheet names, if needed

 

[wiz329]

Not 100% sure I follow without the sheet names. Also, would this work if the Ranking Criteria Columns were out of order?

 

[FDibbins]

You mentioned that you had the data on 1 sheet, and wanted the answers on a 2nd sheet, so this would refer to another sheet...
=INDEX(Sheet2!$A$2:$A$4,MATCH(SMALL(Sheet2!B$2:B$4,ROWS(Sheet2!$A$1:A1)),Sheet2!B$2:B$4,0))

The order would make no difference, it is looking for the smallest, then 2nd smallest, etc.

=SAMLL() works this way...
=SMALL(range,x) where x is the n-th smallest value you want
=SMALL(range,1) will give the smallest
=SMALL(range,2) will give the 2nd smallest
=SMALL(range,3) will give the 3rd smallest

To give a "sequential" list of "smalls", I used the ROWS() function
=ROWS($A$1:A1) = 1
=ROWS($A$1:A2) = 2
=ROWS($A$1:A3) = 3

 

[Peter_SSs]

Excel Workbook
	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
Data Sheet

My suggestion is to change the headings in the 'Output' sheet to be the same as in the 'Data Sheet' though their order is not relevant (I have changed the order below).

Formula in B2 is copied across and down.

Excel Workbook
	A	B	C	D
1	Ranking	Ranking Criteria #2	Ranking Criteria #1	Ranking Criteria #3
2	1	Entity #3	Entity #1	Entity #3
3	2	Entity #1	Entity #2	Entity #2
4	3	Entity #2	Entity #3	Entity #1
Output Sheet

 

[wiz329]

Is there an easy way to communicate with actual workbooks?

While working it out myself, I ended up using Index-Match with an INDIRECT thrown in there:

=INDEX('Data Sheet'!$A$2:$A$4,MATCH($A2, INDIRECT("'Data Sheet'!"&SUBSTITUTE(ADDRESS(1,MATCH(B$1,'Data Sheet'!$A$1:$D$1,0),4),"1",)&"2:"&SUBSTITUTE(ADDRESS(1,MATCH(B$1,'Data Sheet'!$A$1:$D$1,0),4),"1",)&"4"),0))

However, I'm curious to see how your way (which is obviously more concise) works. The problem I see is that the end of your MATCH function (the array against which you are finding the match) is that if you only wanted to output a subset of the criterias, it wouldn't work.

My implementation of that way is the following:

=INDEX('Data Sheet'!$A$2:$A$4,MATCH('Output 2'!$A2,'Data Sheet'!C$2:C$4,0))

But it only works if I include all criterias, and in the same order.

 

[wiz329]

@Peter_XXs, that's brilliant, exactly what I was looking for, and much simpler than my INDIRECT solution.

 

[wiz329]

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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:71px;"><col style="width:131px;"><col style="width:131px;"><col style="width:131px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4


My suggestion is to change the headings in the 'Output' sheet to be the same as in the 'Data Sheet' though their order is not relevant (I have changed the order below).

Formula in B2 is copied across and down.

Output Sheet

	A	B	C	D
1	Ranking	Ranking Criteria #2	Ranking Criteria #1	Ranking Criteria #3
2	1	Entity #3	Entity #1	Entity #3
3	2	Entity #1	Entity #2	Entity #2
4	3	Entity #2	Entity #3	Entity #1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:66px;"><col style="width:136px;"><col style="width:136px;"><col style="width:136px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula 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)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Would you mind decomposing and explaining your formula?

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

 

[Peter_SSs]

@Peter_XXs, that's brilliant, exactly what I was looking for, and much simpler than my INDIRECT solution.

You are welcome. Glad it's what you wanted.