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

=INDEX($datarange,MAX((VerticalLookupValue=$NameRange)*MATCH(ROW($NameRange),ROW($NameRange))))

<tbody>
</tbody>

B) - Check several columns for a lookup value

=INDEX($dataRange,MATCH($verticalLookupValue,$verticalLookupRange,0),MIN(IF($dataRange=$horizontalLookupValue,COLUMN($ColumnRange)-10)))

<tbody>
</tbody>

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)

Date <tbody> </tbody>

Winner 1

Rating 1 <tbody> </tbody>

Winner 2 <tbody> </tbody>

Rating 2 <tbody> </tbody>

Loser 1 <tbody> </tbody>

Rating 1 <tbody> </tbody>

Loser 2 <tbody> </tbody>

Rating 2 <tbody> </tbody>

W1 <tbody> </tbody>

EndRating W1 <tbody> </tbody>

W2 <tbody> </tbody>

EndRating W2 <tbody> </tbody>

L1 <tbody> </tbody>

EndRatingL1 <tbody> </tbody>

L2 <tbody> </tbody>

EndRatingL2 <tbody> </tbody>

12/03/2016 <tbody> </tbody>

Smith <tbody> </tbody>

1100 <tbody> </tbody>

Jones <tbody> </tbody>

1080 <tbody> </tbody>

Smith <tbody> </tbody>

1106 <tbody> </tbody>

Jones <tbody> </tbody>

1076 <tbody> </tbody>

12/03/2016 <tbody> </tbody>

Wu <tbody> </tbody>

1200 <tbody> </tbody>

Zhang <tbody> </tbody>

1215 <tbody> </tbody>

Jones <tbody> </tbody>

1076 <tbody> </tbody>

Wu <tbody> </tbody>

1201 <tbody> </tbody>

Zhang <tbody> </tbody>

1216 <tbody> </tbody>

Jones <tbody> </tbody>

1074 <tbody> </tbody>

12/03/2016 <tbody> </tbody>

Wu <tbody> </tbody>

1201 <tbody> </tbody>

Zhang <tbody> </tbody>

1215 <tbody> </tbody>

Smith <tbody> </tbody>

1106 <tbody> </tbody>

Wu <tbody> </tbody>

1204 <tbody> </tbody>

Zhang <tbody> </tbody>

1219 <tbody> </tbody>

Smith <tbody> </tbody>

1100 <tbody> </tbody>

12/03/2016 <tbody> </tbody>

Leclerc <tbody> </tbody>

1340 <tbody> </tbody>

Jones <tbody> </tbody>

1074 <tbody> </tbody>

Leclerc <tbody> </tbody>

1342 <tbody> </tbody>

Jones <tbody> </tbody>

1072 <tbody> </tbody>

12/03/2016 <tbody> </tbody>

Leclerc <tbody> </tbody>

1342 <tbody> </tbody>

Smith <tbody> </tbody>

1100 <tbody> </tbody>

Leclerc <tbody> </tbody>

1344 <tbody> </tbody>

Smith <tbody> </tbody>

1098 <tbody> </tbody>

12/03/2016 <tbody> </tbody>

Leclerc <tbody> </tbody>

1344 <tbody> </tbody>

Wu <tbody> </tbody>

1204 <tbody> </tbody>

Zhang <tbody> </tbody>

1219 <tbody> </tbody>

Leclerc <tbody> </tbody>

1349 <tbody> </tbody>

Wu <tbody> </tbody>

1202 <tbody> </tbody>

Zhang <tbody> </tbody>

1217 <tbody> </tbody>

12/03/2016 <tbody> </tbody>

dummy <tbody> </tbody>

Leclerc <tbody> </tbody>

1349 <tbody> </tbody>

<tbody>
</tbody>

Let me know if I can provide any more information.

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

Thank you all!

 

[Malachite84]

I've thought about this since yesterday, and written the following formula. This is able to search multiple columns for the lowest row, using IF conditions - but it can't check multiple columns for the matching data value.

=INDEX($P$6:$P$12, IF(MAX( ($D$3=$O$6:$O$12)*MATCH(ROW($O$6:$O$12),ROW($O$6:$O$12)))> MAX(($D$3=$Q$6:$Q$12)*MATCH(ROW($Q$6:$Q$12),ROW($Q$6:$Q$12))),MAX(($D$3=$O$6:$O$12)*MATCH(ROW($O$6:$O$12),ROW($O$6:$O$12))),MAX(($D$3=$Q$6:$Q$12)*MATCH(ROW($Q$6:$Q$12),ROW($Q$6:$Q$12)))))

I'll keep retooling this, but hoping someone can advise in the meantime.
Thanks again!

 

[AlanY]

the sample data posted don't have the row/column references made it difficult to understand.

is it possible to share the sample file on dropbox or google drive etc?

 

[Malachite84]

Thanks AlanY
The sample data represents Columns A:R, if that makes it easier. I can't share a file at present but could do so later.


Update 2:
I aimed to nest the INDEX within the IF clauses, such that if the lowest matching row is in column O, there is an index against column P, but if the lowest matching row is not in column O, there is an index against column Q.

It works - although I'll have to build up the formula with further nest If/Index/Match combinations to cover six potential columns. For reference, here's the working formula for 2-column pairs:
=IF(MAX(($D$3=$O$6:$O$12)*MATCH(ROW($O$6:$O$12),ROW($O$6:$O$12)))>MAX(($D$3=$Q$6:$Q$12)*MATCH(ROW($Q$6:$Q$12),ROW($Q$6:$Q$12))), INDEX($P$6:$P$12, MAX(($D$3=$O$6:$O$12)*MATCH(ROW($O$6:$O$12),ROW($O$6:$O$12)))), INDEX($R$6:$R$12, MAX(($D$3=$Q$6:$Q$12)*MATCH(ROW($Q$6:$Q$12),ROW($Q$6:$Q$12)))))

 

[Malachite84]

Although after a moment of excitement, I've realised this formula doesn't also check against the date match, so I'll have to build that in as well - starting with working out whether the date match needs to be built into each IF clause, or can be given once, outside the IF clauses.

Apologies for spamming my own question, but I can't edit my own posts.

Thanks all.

 

[AlanY]

right, based on the sample data columns A-R.

let say we are looking at 12/03/2016, loser driver Smith.
are we trying to locate Smith in Columns K to Q, once Smith was found in Column O then look down to Row 6 and get 1098 from Cell P6?

 

[Malachite84]

That's exactly right, AlanY - with the added complexity that there will be data from different dates in the same table.

 

[AlanY]

more question.

on the same date, will Smith can also appears on Column Q (L2)?
you are only looking for the losers but not winners, right? i.e. from Column O, rather than K to R.

 

[Malachite84]

Yes, Smith could appear in L2 on the same date (ie, being a co-driver on two entries in the same race).
However, I'm only interested in retrieving the result from lowest row (on this date) in which Smith appears, regardless of whether he appears in multiple loser columns

 

[Malachite84]

Thought - can I build a MATCH against the date column into the arrays for my nested INDEX functions?