Formula to find 2nd Match using Index and 2 Match conditions

Can you explain (or upload a new file) showing your desired outcomes? What results do you want populated and where do you want them populated?

Basically on the sheet entitled master I would want a formula in C2 that would return the value 33.2% from the 'Data 1' sheet --> (i.e. the 2nd match of cell B1 ("% closed Early") and then in D2 a formula that would return the value 34.7% (i.e. the 3rd match of the data in cell B1 ("% closed Early") .

Hope that makes sense...

Try the formula below in B2 of your 'Master' sheet. Confirm it with Ctrl + Shift + Enter instead of the usual enter, then fill down through B12, then across through column E. I think it does what you're looking for.

=IF(ISERROR(INDEX('Data 1'!$A$1:$T$15,MATCH($A2,'Data 1'!$A$1:$A$15,0),SMALL(IF('Data 1'!$A$1:$T$1=B$1,COLUMN('Data 1'!$A$1:$T$1)),COLUMN()-1))),"",INDEX('Data 1'!$A$1:$T$15,MATCH($A2,'Data 1'!$A$1:$A$15,0),SMALL(IF('Data 1'!$A$1:$T$1=B$1,COLUMN('Data 1'!$A$1:$T$1)),COLUMN()-1)))

Nice catch, thanks! Old habits die hard, especially on Friday afternoons.

Works perfectly so thanks - much appreciated.

Is it possible to briefly explain how this works (i.e. what the formula is doing to get the correct answer) - I understand the IF ERROR part and the index Match but don;t understand how the SMALL,IF AND COLUMN BITS get me to the answer..

Thanks Dave

Hi, both of these work (so thanks for help) however as they use the "column()" they will only work if they are all located in the cells next door to each other (i.e. if I enter formula in 1st cell and then drag across). My spreadsheet is large and the cells that i want use to find 1st match and then then 2nd match are not located next to each other. Is there an alternative to using column so i can still find the nth match of my Index match but without using the column() where the cells have to be next door to each other.

Hope that makes sense & any help would be gratefully appreciated....

=IFERROR(INDEX('Data 1'!$A$1:$T$15,MATCH($A2,'Data 1'!$A$1:$A$15,0),SMALL(IF('Data 1'!$A$1:$T$1=B$1,COLUMN('Data 1'!$A$1:$T$1)),COLUMN(A:A))),"")

The version above (still entered w/Ctrl + Shift + Enter) still uses COLUMN, but should be a little more versatile than COLUMN()-1. When filled right, you'll have COLUMN(A:A) in the first column, returning the first result, COLUMN(B:B) in the second column returning the second result, etc. This will happen regardless of where your first column actually is. If you aren't returning the nth results in order left to right, you can modify the letters in COLUMN to suit your needs, e.g. COLUMN(H:H) returns the 8th result.

To address your earlier question:
IF('Data 1'!$A$1:$T$1=B$1,COLUMN('Data 1'!$A$1:$T$1))
This section says "If the header in 'Data 1' matches this column's header, return that column identifier", then
SMALL(IF('Data 1'!$A$1:$T$1=B$1,COLUMN('Data 1'!$A$1:$T$1)),COLUMN(A:A) says to look at the Nth smallest column number that matches, so COLUMN(A:A) returns the first column that matches, B:B returns the second, etc. That return then tells the INDEX part of the formula to run the INDEX/MATCH result in that particular column. Hope this helps?

=IFERROR(INDEX('DATA'!$D$3:$DD$1000,MATCH($S$2,'DATA'!$D$3:$D$1000,0),SMALL(IF('DATA'!$D$3:$DD$3=$S$9,COLUMN('DATA'!$D$3:$DD$3)),COLUMN(A:A))),"")

This is my adjusted formula (now in larger spreadsheet so differs from sample) where the Matches are based on Names in cells S2 and then S9. However once I have input the formula into my 1st cell (using “A:A”) the 1st cell seems to find the 2nd match – not 1st match??

Have I input this incorrectly or can you possibly explain what’s going wrong?

Thanks Tony- that seems to do the trick....