Fetching 2nd, 3rd, 4th (and so on) Match while Ignoring Blanks

[LearnExcl]

Hi,
The below formula currently fetches the 2nd value, but it is actually the first corresponding in a cell where there is actually a value, since the first corresponding match is a blank cell. Could we revise the formula to fetch the second real value (text) ignoring all the corresponding blank cells?

=IF(SUMPRODUCT(--(INDEX($A$2:$AU$500,,MATCH("Nr",$A$1:$AU$1,0))=RIGHT(INDEX($A$1:$CU$500,ROW($A2:B2),MATCH("UMR",$A$1:$CU$1,0)),9))))>=1,IFERROR(INDEX($AM$1:$AM$500,SMALL(IF($J$1:$J$500=RIGHT(INDEX($A$1:$CU$500,ROW($A2:B2),MATCH("UMR",$A$1:$CU$1,0)),9),ROW($AM$1:$AM$500),1000000),2)),""))

 

[jasonb75]

Could you provide an XL2BB data sample with an explanation of what is needed so that we don't have to try and reverse engineer the entire formula?

 

[Peter_SSs]

I suggest that you also update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[LearnExcl]

Sheet 1			Sheet 2
A	B	C	A	B
345​			888​
888​			345​	Gear
980​			455​	Hellen
323​			345​
455​			888​	Heim
			980​	Rerer
			888​
			323​	Steph
			345​	Manol
			888​	Watson

I suggest that you also update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Again,
**I am sorry I am unable to download XL2BB
To make it simple, I just wanted reflect a sample of the data. Sheet1 , B2 is where the 1st formula goes and is carried down. The formula finds the first match for A2 in Column A of Sheet2 to and brings its corresponding value in Column B in Sheet2, while ignoring blanks. The second formula goes on Sheet1, C2 and fetches the second match for A2 in Sheet1 in Sheet2 (same logic), while ignoring blanks. The third formula goes in Sheet 1, D2 and fetches the third match for A2 while ignoring blanks, and son on.

 

[jasonb75]

I think I'm following what you need, I've done this in one sheet for simplicity, hopefully it will make sense.

Book1 (version 1).xlsb
	A	B	C	D	E
1	Sheet 1			Sheet 2
2	A	B	C	A	B
3	345	Gear		888
4	888	Heim		345	Gear
5	980	Rerer		455	Hellen
6	323	Steph		345
7	455	Hellen		888	Heim
8				980	Rerer
9				888
10				323	Steph
11				345	Manol
12				888	Watson
Sheet8

Cell Formulas
Range	Formula
B3:B7	B3	=IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW($E$3:$E$12)/($D$3:$D$12=A3)/($E$3:$E$12<>""),COUNTIF(A$3:A3,A3))),"")

 

[Peter_SSs]

Try in B2 of Sheet1 and copy across and down

=IFERROR(INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!$B$1:$B$20)/((Sheet2!$A$1:$A$20=$A2)*(Sheet2!$B$1:$B$20<>"")),COLUMNS($B:B))),"")

 

[jasonb75]

Well spotted, Peter!

I missed the part about dragging right and read it as duplicates dragging down

 

[LearnExcl]

Try in B2 of Sheet1 and copy across and down

=IFERROR(INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!$B$1:$B$20)/((Sheet2!$A$1:$A$20=$A2)*(Sheet2!$B$1:$B$20<>"")),COLUMNS($B:B))),"")

Fantastic! Thank you so much. I'd be obliged if you could briefly explain the formula and how it works.

 

[LearnExcl]

An additional question. Can this formula be revised to include multiple ranges? For instance, if I have data in Sheet3 and Sheet4, same columns?
Revised question: Sheet1 , B2 is where the 1st formula goes and is carried down. The formula finds the first match for A2 in Sheet2, Sheet3 and Sheet4 and brings its corresponding value in Column B in Sheet2, while ignoring blanks. The second formula goes in C2 on Sheet1, and fetches the second match for A2 in Sheet2, Sheet3, and Sheet4 (same logic), while ignoring blanks. The third formula goes in D2 of Sheet1 and fetches the third match for A2 in Sheet2, Sheet3, and Sheet4 while ignoring blanks, and so on. I truly appreciate your response.

 

[jasonb75]

Is it actually 3 sheets of data being pulled into one, or in reality will it be more?

It could be done by repeating the existing formula in place of the double quotes at the end then changing the sheet number in the repeated section, then adding countif to the column count at the end.

With 2 or 3 sheets it would be just about manageable but any more would result in a very long and messy formula. I can't see that there is any realistic way to condense multiple sheet references into an array with what you're trying to do. I'll hold back on re-writing the formula until you confirm the actual sheet count (and to see if Peter has any alternative ideas).