INDEX-MATCH (or alternatives) with multiple columns

[Malachite84]

Hello All,

This is a first post, so apologies if I break any guidelines.

Summary: I'm working with a dataset where the values I want to retrieve need to be the last row meeting the criteria (containing the correct date and name), but could fall into one of six columns. I've seen various versions of INDEX-MATCH formulae which cover some of the aspects I need, but can't work out how to merge them, or even if that is possible. Any advice would be very greatly appreciated.

Extended Version:
The dataset relates to motor-racing rankings based on race results, organised pairwise, so each one row contains a record of one driver beating another in a given race on a given date, and the associated exchange of ranking points between them. This interacts with a second sheet that contains the 'end of race' values for each driver. I have already created Index-Match-Match formulae that populate the driver data which are fed into the calculations, but want to create one in the second sheet that retrieves the 'end of race' values.

The complicating factor here is that some race entries consist of co-drivers, and therefore there isn't one 'Driver' column for the winner and loser - there are up to six.

Consequently, I need a formula that can:
1) - Look up records with the correct date in Column A.
2) - Look up records with the correct Losing Driver in any of Columns AA, AC, AE, AG, AI, or AK
3) - Identify which of the records meeting the two criteria above is in the highest value row (i.e., further down the worksheet)
4) - Retrieve the appropriate Losing Driver New Rating in any of Columns AB, AD, AE, AH, AJ, or AL.

I've seen formulae that can:
A) - Return the last occurrence of a lookup value
[TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl63"]=INDEX($datarange,MAX((VerticalLookupValue=$NameRange)*MATCH(ROW($NameRange),ROW($NameRange))))[/TD]
[/TR]
</tbody>[/TABLE]

B) - Check several columns for a lookup value
[TABLE="width: 114"]
<tbody>[TR]
[TD]=INDEX($dataRange,MATCH($verticalLookupValue,$verticalLookupRange,0),MIN(IF($dataRange=$horizontalLookupValue,COLUMN($ColumnRange)-10)))[/TD]
[/TR]
</tbody>[/TABLE]

The problem being, that the first won't check multiple columns, and the second only retrieves the first result.

I've also seen other formula of different constructions, which seem to be dealing with similar issues, but I'm not sure if these will solve my problems (cell/range references as quoted in the source text, and not relevant to my data)
e.g.
=INDEX($B$4:$M$5,MATCH(A9,$A$4:$A$5,0),MATCH(1,IF($B$1:$M$1=B9,IF($B$2:$M$2=C9,IF($B$3:$M$3=D9,1))),0))

As stated above, any help or advice would be great, even if just links to good guides on how to merge MIN/MAX/IF/AND into INDEX-MATCH formula.

Here's a mock of the kind of data I'm dealing with. I've not included any data columns that aren't relevant, and I've only put in two driver columns for simplicity here, rather than the six required. I've highlighted in bold the data that I need to be finding and retrieving.

Data Sample (not genuine, but in the right format)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65"]Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Winner 1[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Rating 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Winner 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Rating 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Loser 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Rating 1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Loser 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Rating 2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]W1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]EndRating W1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]W2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]EndRating W2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]L1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]EndRatingL1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]L2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]EndRatingL2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, align: right"]12/03/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Jones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1080[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1106[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Jones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, align: right"]12/03/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Wu[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1200[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Zhang[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1215[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Jones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1076[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Wu[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1201[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Zhang[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1216[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Jones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1074[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, align: right"]12/03/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Wu[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1201[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Zhang[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1215[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1106[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Wu[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1204[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Zhang[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1219[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, align: right"]12/03/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Leclerc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1340[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Jones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1074[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Leclerc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1342[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Jones[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]1072[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, align: right"]12/03/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Leclerc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1342[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Leclerc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1344[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Smith[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]1098[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, align: right"]12/03/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Leclerc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1344[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Wu[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1204[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Zhang[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1219[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD]Leclerc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="align: right"]1349[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Wu[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]1202[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Zhang[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]1217[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, align: right"]12/03/2016[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]dummy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65"]Leclerc[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, align: right"]1349[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Let me know if I can provide any more information.

(Issue while posting this, hope it hasn't double posted)

Thank you all!

 

[Malachite84]

Right, I found a formula which claims to use offset/match to return a range of cells that match a lookup value. I was thinking I could put this formula in to the array for the INDEX clause, to make the array dependent on matching the relevant date criteria.

The two problems I have here are that the formula doesn't bring back a range, it brings back the number of cells in the range - presumably because it's using COUNTIF, but I don't know what to replace that with.
Secondly, if it did bring back a range, it would be the range containing the date values, rather than the range 15 (or 17, etc) columns to the left, where the data I want is.

The formula I found, adjusted to fit my data, is:
=COUNTIF(OFFSET($A$7:$A$13,MATCH($E$3,$A$7:$A$13,0)-1,0,MATCH(TRUE,OFFSET($A$7:$A$13,MATCH($E$3,$A$7:$A$13,0),0)<>$E$3,0)),42441)

 

[AlanY]

have a look to see of this work for you.
Green cells altered for testing.

Formula in L12 and M12 can copy down to cover L3 to L6 if they follow the same format.

Excel 2012
	A	B	J	K	L	M	N	O	P	Q	R	S	T
1	Date	Winner 1		W1	EndRating W1	W2	EndRating W2	L1	EndRatingL1	L2	EndRatingL2	L3
2	12/03/2016	Smith		Smith	1106			Jones	1076
3	12/03/2016	Wu		Wu	1201	Zhang	1216	Jones	1074
4	12/03/2016	Wu		Wu	1204	Zhang	1219	Smith	1100			Smith	1000
5	12/03/2016	Leclerc		Leclerc	1342			Jones	1072
6	12/03/2016	Leclerc		Leclerc	1344			Smith	1098
7	12/03/2016	Leclerc		Leclerc	1349			Wu	1202	Smith	1000
8	13/03/2016			dummy				Smith	888
9
10				12/03/2016
11				Smith
12				L1	6	1098
13				L2	7	1000
14				L3	4	1000
15				L4	0	0
16				L5	0	0
17				L6	0	0
18					EndRatingL1	1000
Sheet6

Cell Formulas
Range	Formula
M12	=IF(L12=0,0,INDEX($O$1:$Z$8,L12,MATCH(K12,$O$1:$Z$1,0)+1))
M18	=INDEX(M12:M17,MATCH(MAX(L12:L17),L12:L17,0))
L12	{=MAX(IF($A$1:$A$8=$K$10,ROW($A$1:$A$8)*(INDEX($O$1:$Z$8,0,MATCH(K12,$O$1:$Z$1,0))=$K$11)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Malachite84]

Thanks AlanY!
It definitely works, which is fantastic. It'll mean that I need to add 6 hidden columns and a hidden row to the second tab where the end of race ratings will be held, to store the calculations, which isn't ideal, but it's still a working method.

Now I just have to study your formulae for future reference!
Thanks again!

 

[AlanY]

you're welcome