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

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.

Formula:

Please Login or Register  to view this content.

In E2 and filled down.

Formula:

Please Login or Register  to view this content.

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.

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

Removed by FR.

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.

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.

Formula:

Please Login or Register  to view this content.

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.

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.

Double posted.

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?

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.

Formula:

Please Login or Register  to view this content.

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.

Formula:

Please Login or Register  to view this content.

Let me know how it does.

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.

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.

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

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

The final formula is now

Formula:

Please Login or Register  to view this content.

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

You are welcome.