How to match multiple criteria from a list to pull entire row from another worksheet?

[Ozymandius]

Good day all,

I am working on trying to be able to pull data that matches criteria in 1 worksheet that would return an entire row of data from another worksheet and place the data on the 3rd sheet.

The data I wish to match has 3 columns (First Name, Last Name, Year)

The data I pulling from has Columns A through AI and I am trying to match against first name, last name and year.

In the data set, I wish to return all lines of data that match the first name, last name match and is equal to or greater than the year.

I have tried VLOOKUP but my understanding is that you cannot use multiple match criteria for that.

I tried using Index(range,Match(),0) and I thought if I have the column value a zero value it would return the entire row of date I consistently could only get it to return one cell's value.

I have not tried any of this with a macro or VB coding, but I am not opposed to that if someone wishes to point me in the proper direction.

I have attached a sample spreadsheet that has raw data on tab 1, match criteria on tab 2 and the hopeful result set on tab 3.

Thank you in advance for taking the time to read this and any help that is offered.

[FlameRetired]

Ozymandius welcome to the forum.

In the attached find two helper columns in 'Match Data'. These establish the lower and upper boundary row number for each individual and years >= YEAR.

In D2 and filled down.

=MATCH(1,INDEX(1/(($A2='Raw Data'!$A$2:$A$373)*($B2='Raw Data'!$B$2:$B$373)*('Raw Data'!$C$2:$C$373>=C2)),0),0)

In E2 and filled down.

=MATCH(2,INDEX(1/(($A2='Raw Data'!$A$2:$A$373)*($B2='Raw Data'!$B$2:$B$373)*('Raw Data'!$C$2:$C$373>=$C2)),0),1)

Then there is a duplicate Result sheet 'Result Set (2)'. Find a helper column A inserted to the left. It has an array formula that returns each relevant row number from 'Raw Data'. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. Fill down until you get blanks.

=IFERROR(SMALL(IF((ROW('Raw Data'!$A$2:$A$373)-MIN(ROW('Raw Data'!$A$2:$A$373))+1>=

TRANSPOSE('Match Data'!$D$2:$D$6))*(ROW('Raw Data'!$A$2:$A$373)-MIN(ROW('Raw Data'!$A$2:$A$373))+1<=

TRANSPOSE('Match Data'!$E$2:$E$6)),ROW('Raw Data'!$A$2:$A$373)-MIN(ROW('Raw Data'!$A$2:$A$373))+1),ROWS($2:2)),"")

Then in B2 filled down and across until you get blanks. This is also array entered.

=IFERROR(INDEX(IF(ISNUMBER('Raw Data'!A$2:A$373),'Raw Data'!A$2:A$373,'Raw Data'!A$2:A$373&""),$A2),"")

[FlameRetired]

Removed by FR.

[Ozymandius]

FlameRetired,

I just wanted to thank you very much. I am still trying to understand exactly how this is working and how to expand it to a much larger section of raw data, but while I am working through understanding this I wanted to make sure I thanked you for this. If you do not mind I would like to be able to post back on this thread with future questions.

Brian

[FlameRetired]

You are welcome, and yes of course. I am still subscribed. I'll stay alert. But one thing.

I am still trying to understand exactly how this is working and how to expand it to a much larger section of raw data,

While I have not found a formula work around for those array formulas you need to know that they are resource hungry. Too many of them will slow a work book down. Let us know if that becomes unacceptable.

[FlameRetired]

Brian if you're still with us I found an non array formula for the last set. In B2 of 'Result Set (2)' fill down and across until you get blanks.

=IFERROR(IF(INDEX('Raw Data'!A$2:A$373,$A2)="","",INDEX('Raw Data'!A$2:A$373,$A2)),"")

I'm still working on the helper in 'Result Set (2)'. It may not be much of a problem as it is only one column, but let me know.

[Ozymandius]

Dave,

I am definitely still here and I have to say I am definitely seeing the performance issues you were referring to. My original data set goes from A1:AI13129 so the arrays are definitely an issue. I am still trying to make sure I understand the Transpose and why you are doing the "1/(($A2='Raw Data'!$A$2:$A$373)*($B2='Raw Data'!$B$2:$B$373)*('Raw Data'!$C$2:$C$373>=$C2))" as the first part of the INDEX. I am going to try your non array formula and see how that goes and thank you again!

[FlameRetired]

Double posted.

[FlameRetired]

I am still trying to make sure I understand the Transpose

Try Googling the term "orthogonal".

Transposing makes certain that each element in D2:D6 and E2:E6 (which recall are row numbers returned by the previous helper formulas) are compared with all the row numbers in 'Raw Data'. To do so requires one of the ranges be "cross-ways" ... arranged as a single row to the other which is a single column ... orthogonal. That is an indirect way to assign indexing numbers to qualifying combinations of first name, last name, years. It's a lot of trouble to go to to get index numbers, but I can't think of another formula way to do it.

Having said that:

My original data set goes from A1:AI13129

And Yikes! That is huge! I am going to see if I can get us some help. Someone with VBA skills make be able to help you out.

You didn't ask, but did you want a "walk-through" on the "1/(($A2='Raw Data'!$A$2:$A$373)*($B2='Raw Data'!$B$2:$B$373)*('Raw Data'!$C$2:$C$373>=$C2))" part? Let me know after I get some help for the other part. The explanation is a bit involved.

[Ozymandius]

Ok, so the Transpose are just like a transpose of any matrix. I have not messed with those since linear algebra a long time ago and certainly never in excel! An explanation of that other part would be great if you are willing. I love learning new things things in excel! Thanks!

[FlameRetired]

I'll do my best.

In the FORMULAS menu locate the Evaluate Formula feature. It will help. Together with that and the F9 function key you can dissect and trouble shoot formulas. They are excellent tools for self instruction as well. I recommend them whenever I can.

That said this needs some context.

=MATCH(1,INDEX(1/(($A2='Raw Data'!$A$2:$A$373)*($B2='Raw Data'!$B$2:$B$373)*('Raw Data'!$C$2:$C$373>=C2)),0),0)

With D2 in 'Match Data' active click on Evaluate Formula. Click Evaluate repeatedly and you will see how Excel calculates the formula step by step in context. Unfortunately the view port is quite small, but you can still get an idea especially when used in conjunction with the F9 function key.

To use the F9 function key we start with operations in the inner most parentheses and work our way out ... just like algebra.

To use the F9 function key start by selecting just ($A2='Raw Data'!$A$2:$A$373) in the formula bar. Press F9. You will see an array for TRUE / FALSE. Sometimes ($A2='Raw Data'!$A$2:$A$373) is TRUE sometimes it's FALSE. BTW the ";" are understood to be row separators in an array.

Extend the selection in the formula bar to include ($A2='Raw Data'!$A$2:$A$373)*($B2='Raw Data'!$B$2:$B$373)*('Raw Data'!$C$2:$C$373>=C2) and hit the F9 function key again. You will see an array of 1s and 0s. This is the effect operators (multiplication in this case) have on TRUE/FALSE. They coerce TRUE and FALSE into their underlying numeric values 1 and 0.

Now extend the selection in the formula bar to include 1/(($A2='Raw Data'!$A$2:$A$373)*($B2='Raw Data'!$B$2:$B$373)*('Raw Data'!$C$2:$C$373>=C2)). Hit F9. You will see an array of #DIV/0! errors and if you scroll up the array you will eventually see some 1s. MATCH will ignore errors in its second argument and will match the 1 (MATCH(1,1/array,0)) to the first 1 it finds in the array because the match type is set to 0 ... exact match. It returns the row number of that match.

The INDEX function I've wrapped around the array is optional. In this case it removes the necessity of entering the formula with Ctrl + Shift + Enter. Just Enter works. It is still an array formula though.

In the case of D2 the match is row 25.

Now make E2 the active cell. This is the formula.

=MATCH(2,INDEX(1/(($A2='Raw Data'!$A$2:$A$373)*($B2='Raw Data'!$B$2:$B$373)*('Raw Data'!$C$2:$C$373>=$C2)),0),1)

The F9 steps are exactly the same and the array returned is exactly the same as in the previous. The difference here is that we are going to match 2 or at least a number greater than 1 to the same array but with a match type of 1 ... approximate match. MATCH will find the last number in the array less than or equal to 2. It returns that row number which in this case is 29.

So Tommie Aaron Year>=1963 starts in row 25 and ends in row 29 of 'Raw Data'!A2:C373. (A2 is the first row.)

Did this help?

[FlameRetired]

I have a new approach.

This has just one non array helper column. As long as the years in 'Match Data' are in ascending order or all the same year this should work.

In the new column A of 'Raw Data' this formula filled down.

=IFERROR(IF(MATCH(D2,'Match Data'!$C$2:$C$6,1),IF(ISNUMBER(MATCH(2,MMULT(--EXACT(B2:C2,'Match Data'!$A$2:$B$6),{1;1}),0)),ROW()-ROW($2:$2)+1,"")),"")

It's involved. I had to do some fiddling around with different functions and function combinations to get it to work, but it seems to.

Then in D2 of 'Result Set (2)' filled down and across.

=IFERROR(IF(INDEX('Raw Data'!B$2:B$373,SMALL('Raw Data'!$A$2:$A$373,ROWS($2:2)))="","",INDEX('Raw Data'!B$2:B$373,SMALL('Raw Data'!$A$2:$A$373,ROW()-ROW($2:$2)+1))),"")

Let me know how it does.

[Ozymandius]

Thanks! I will be able to look at all of this later this evening. I really do appreciate the time taken and the patience shown in explanations!

[Ozymandius]

Great hint of using evaluate formula and very nice and easy to follow explanation of the formula!

The new method works wonderfully! I was able to expand it to the large data set and I see no major performance issues. Actually, the only issue I do see is from where I have kept the original method because it summing up the difference of the min and max rows and adding 1 to each line (or doing a count) gives me how many rows should be returned and I can use that to double check when I have spelling errors between the raw data and the match data.

I am still working on understanding the MMULT function but that is more of a refresher on linear algebra again and not excel. It has been a while since I took the product of 2 matrices. ROW, ISNUMBER, EXACT, IFERROR and MATCH I have used before, though not much for ROW.

Thank you again and do I need to do something to mark this thread as solved?

[FlameRetired]

Glad to hear it. Have you tested years in 'Match Data'? I am kind of curious.

And yes. If you are satisfied it's solved select Thread Tools from the menu link above and mark thread as SOLVED.

Have good weekend.

[Ozymandius]

I actually had not tested that until you asked, but I changed one of the values in the Match Data to be 1964 instead of 1963 and I am not sure I got the results I expected.
I then realized well from the data set that particular person had no data for years 1964, 1965, 1966 and 1967 so I changed the next one and also did not get results I expected.
I used the Evaluate formula and the Match of D40 on Raw Data (Hitters) and it returns 200 which seemed odd to me. I have attached the larger worksheet in case you wish to analyze.
What you have done works well for me and when I have to change the match data it will all have a new year (and the same year) so I think for my purposes this would work,
but I also know when I am working on things like this I do not like the answer to be less than elegant when it comes to things like this so in case you feel the same way...

Edit: I am having issues trying to attach a new file. it keeps failing.

[Ozymandius]

Finally got it posted. I trimmed off a ton of data to get it under 1 MB

[FlameRetired]

Yes. I got to thinking about that. Came back tonight another approach. I tested it on the original file. It works. I also found a way to streamline the helper and final formulas. It reduces a lot of repeating calculations.

Find a new column A in both 'Raw Data' and 'Result Set (2)'. Just use fill series. I "future proofed" both sheets.(1000 rows)

Then the indexing helper in column B 'Raw Data' is

=IFERROR(IF(E2>=INDEX('Match Data'!$C$2:$C$6,MATCH(2,MMULT(--EXACT(C2:D2,'Match Data'!$A$2:$B$6),{1;1}),0)),A2,""),"")

The indexing helper column B in 'Result Set (2)' is

=IFERROR(SMALL('Raw Data'!$B$2:$B$373,'Result Set (2)'!A2),"")

The final formula is now

=IFERROR(IF(INDEX('Raw Data'!C$2:C$373,$B2)="","",INDEX('Raw Data'!C$2:C$373,$B2)),"")

They all calculate faster now. Try them and let me know what you think.

[Ozymandius]

These is MUCH better for performance on the large data set! Thank you very much and is easy to follow as well! I have to remember the idea to add indexes to both original data set and result set! Thank you again!

[FlameRetired]

You are welcome.