Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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.

excel
posted by: JeffA on Wed Feb 20, 2019 at 4:56 pm
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.

posted by: Variatus (rep: 4889) on Fri Feb 22, 2019 at 5:12 am
Discuss


Answer the Question

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