Using Index and Match for multiple results without using an aggregate

0

This is my function
=INDEX('Van Ride Raw Data'!$C$2:$K$1000,MATCH(D2,'Van Ride Raw Data'!$A$2:$A$1000,0),0)
This pulls a row of information based off of the date entered in cell D2 from another worksheet.  This is great, but, only works with the first row of data.  I have multiple rows, not in any order, and there will be thousands of rows in total.  I want to be able to have each row be identified and the information to the right of that matched the date be pulled in without having to go through and making an aggregate and naming each row in the formula.

I have attached the workbook that I am working with wo you can see what I am doing and hopefully be able to help.

Thank you.

Answer
Discuss

Answers

0

I converted your data Van Ride Raw Data!A1:K15 to a table which I called RawData. You can reverse the conversion by replacing "RawData" in the formula below with Van "'Ride Raw Data'!A2:K15" and "RawData[Date]" with "'Ride Raw Data'!A2:A15". "Date" in "RawData[Date]" is the table column caption and would have to be changed if that caption isn't the same.

=IFERROR(INDEX(INDEX(RawData,0,COLUMN(C5)),SMALL(IF(RawData[Date]=$D$2,ROW(RawData[Date])-ROW(INDEX(RawData[Date],1,1))+1),$B5)),"")

Although this is an array formula, it doesn't copy itself across all columns like your formula does. It must be entered in 'Van Ride Daily Itinerary'!B5 and copied to all cells C:K as far down as you might have data. The IFERROR() function, in which the principal formula is embedded, will return "" if the formula can't return a value. In my trial I copied formulas to row10 but only rows 5 and 6 show results.

Note that COLUMN(C5) in INDEX(RawData,0,COLUMN(C5)) returns the column numberof column C, which is 3 and changes to 4, 5 etc, as the formula is copied across. The row number is insignificant but you can change the initial column number. In this example, column C is copied to column C, column D to column D, etc. Therefore it's COLUMN(C5) in the formula entered in C5.

The SMALL() function in the formula takes its n from B5. Of course B5 = 1 which induces return of the 1st instance of $D$2. This value changes to 2, 3 etc. as the formula is copied down. If n is greater than the number of recurrances of $D$2 in the RawData and error occurs which results in a blank cell.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login