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)),""))

 

[LearnExcl]

Jason, thank you so much for responding. It is actually Sheet2, Sheet3 and Sheet4 being pulled into Sheet1. I very much appreciate your support on this.

 

[jasonb75]

I'm just about to go offline for the day so this is a bit rushed. Hopefully I have it right, although I haven't had chance to test the theory.

Enter this formula into B2 and array confirm with Ctrl Shift Enter before using the fill handle to populate the rest of the table.

=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))),IFERROR(INDEX(Sheet3!$B:$B,AGGREGATE(15,6,ROW(Sheet3!$B$1:$B$20)/(Sheet3!$A$1:$A$20=$A2)/(Sheet3!$B$1:$B$20<>""),COLUMNS($B:B)-SUM(COUNTIF(Sheet2!$A$1:$A$20,$A2:A2)))),IFERROR(INDEX(Sheet4!$B:$B,AGGREGATE(15,6,ROW(Sheet4!$B$1:$B$20)/((Sheet4!$A$1:$A$20=$A2)*(Sheet4!$B$1:$B$20<>"")),COLUMNS($B:B)-SUM(COUNTIF(Sheet2!$A$1:$A$20,$A2:A2),COUNTIF(Sheet3!$A$1:$A$20,$A2:A2)))),"")))

I'll check when I can to see if it worked or needs some adjustments.

 

[LearnExcl]

Hi Jason, it actually worked. Thank you very much!

 

[jasonb75]

Maybe Peter will have an easier method that I'm not thinking of but given the requirements, I don't think that there will be one (except perhaps with functions that you would not have available in your version of excel).

 

[Peter_SSs]

Hi Jason, it actually worked.

If that is so then I haven't understood the data and/or requirement.

Suppose we have these sheets

LearnExcl.xlsm
	A	B
1
2	888
3	345	Gear2
4	455	hellen2
5	345
6	888	Heim2
7	980	Rerer2
8	888
9	323	Steph2
10	345	Manol2
11	888	Watson2
Sheet2

LearnExcl.xlsm
	A	B
1
2	888
3	345	Gear3
4	455	hellen3
5	345
6	888	Heim3
7	980	Rerer3
8	888
9	323	Steph3
10	345	Manol3
11	888	Watson3
Sheet3

LearnExcl.xlsm
	A	B
1
2	888
3	345	Gear4
4	455	hellen4
5	345
6	888	Heim4
7	980	Rerer4
8	888
9	323	Steph4
10	345	Manol4
11	888	Watson4
Sheet4

The the post 12 formula produces this

LearnExcl.xlsm
	A	B	C	D	E
1
2	345	Gear2	Manol2
3	888	Heim2	Watson2
4	980	Rerer2	Rerer3	Rerer4
5	323	Steph2	Steph3	Steph4
6	455	hellen2	hellen3	hellen4
Sheet1

Cell Formulas
Range	Formula
B2:E6	B2	=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))),IFERROR(INDEX(Sheet3!$B:$B,AGGREGATE(15,6,ROW(Sheet3!$B$1:$B$20)/(Sheet3!$A$1:$A$20=$A2)/(Sheet3!$B$1:$B$20<>""),COLUMNS($B:B)-SUM(COUNTIF(Sheet2!$A$1:$A$20,$A2:A2)))),IFERROR(INDEX(Sheet4!$B:$B,AGGREGATE(15,6,ROW(Sheet4!$B$1:$B$20)/((Sheet4!$A$1:$A$20=$A2)*(Sheet4!$B$1:$B$20<>"")),COLUMNS($B:B)-SUM(COUNTIF(Sheet2!$A$1:$A$20,$A2:A2),COUNTIF(Sheet3!$A$1:$A$20,$A2:A2)))),"")))

.. whereas my reading of the requirements would be this

LearnExcl_1.xlsm
	A	B	C	D	E	F	G	H
1
2	345	Gear2	Manol2	Gear3	Manol3	Gear4	Manol4
3	888	Heim2	Watson2	Heim3	Watson3	Heim4	Watson4
4	980	Rerer2	Rerer3	Rerer4
5	323	Steph2	Steph3	Steph4
6	455	hellen2	hellen3	hellen4
Sheet1

Cell Formulas
Range	Formula
B2:H6	B2	=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))),IFERROR(INDEX(Sheet3!$B:$B,AGGREGATE(15,6,ROW(Sheet3!$B$1:$B$20)/((Sheet3!$A$1:$A$20=$A2)*(Sheet3!$B$1:$B$20<>"")),COLUMNS($B:B)-COUNTIFS(Sheet2!$A$2:$A$20,$A2,Sheet2!$B$2:$B$20,"<>"))),IFERROR(INDEX(Sheet4!$B:$B,AGGREGATE(15,6,ROW(Sheet4!$B$1:$B$20)/((Sheet4!$A$1:$A$20=$A2)*(Sheet4!$B$1:$B$20<>"")),COLUMNS($B:B)-COUNTIFS(Sheet2!$A$2:$A$20,$A2,Sheet2!$B$2:$B$20,"<>")-COUNTIFS(Sheet3!$A$2:$A$20,$A2,Sheet3!$B$2:$B$20,"<>"))),"")))

 

[jasonb75]

I agree with your interpretation, Peter. My rushed formula had the wrong column references in the countif ranges.

Book1
	A	B	C	D	E	F	G	H
1
2	345	Gear2	Manol2	Gear3	Manol3	Gear4	Manol4
3	888	Heim2	Watson2	Heim3	Watson3	Heim4	Watson4
4	980	Rerer2	Rerer3	Rerer4
5	323	Steph2	Steph3	Steph4
6	455	hellen2	hellen3	hellen4
Sheet1

Cell Formulas
Range	Formula
B2:H6	B2	=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))),IFERROR(INDEX(Sheet3!$B:$B,AGGREGATE(15,6,ROW(Sheet3!$B$1:$B$20)/(Sheet3!$A$1:$A$20=$A2)/(Sheet3!$B$1:$B$20<>""),COLUMNS($B:B)-SUM(COUNTIF(Sheet2!$B$1:$B$20,$A2:A2)))),IFERROR(INDEX(Sheet4!$B:$B,AGGREGATE(15,6,ROW(Sheet4!$B$1:$B$20)/((Sheet4!$A$1:$A$20=$A2)*(Sheet4!$B$1:$B$20<>"")),COLUMNS($B:B)-SUM(COUNTIF(Sheet2!$B$1:$B$20,$A2:A2),COUNTIF(Sheet3!$B$1:$B$20,$A2:A2)))),"")))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[LearnExcl]

Thank you Peter for chiming in. At fırst sight it looked like James' formula seemed to be working with the first 100 rows of data. I will check again Monday thorougly and let you both know. Appreciate the support as always.

 

[Peter_SSs]

My rushed formula had the wrong column references in the countif ranges.

I think that your latest formula relies on specific circumstances that may or may not exist in the data sheets. I don't know what is possible with the OP's data but if you change cell B4 in Sheet2 to "Gear2" then the formula now returns incorrect results for code 345 and code 455.

 

[jasonb75]

I wouldn't really call it 'latest formula', it was the same one as before but with the column references corrected.
Admittedly I hadn't allowed for the situation that you have noticed, but we could spend all day adding extra bits to formulas for specifics that might not be relevant.

 

[LearnExcl]

Worked like a charm! Thank you both very much.