Uploaded xlsx Into Google Sheets

[limo2088]

I just uploaded a fully working excel spreadsheet into google sheets, most of the formulas work fine, except for the most important one. They all work flawlessly on the excel file itself, and they all work fine when I uploaded it into google sheets. Except for the one. Is there some sort of a context issue in the formula below that would not allow google to see it properly? This formula works fine in excel on the desktop but when the spreadsheet got uploaded it just return a blank on the Cell its intended for.

=IFERROR(INDEX(T:Z,AGGREGATE(15,6,ROW(B$3:B$100)/(ISNA(MATCH(INDEX(T$3:Z$100,0,R$2),S$3:S$7,0))),ROWS(B$3:B3)),R$2),"")

 

[jasonb75]

Sheets doesn't have the aggregate function. Try this one instead. You will need to array confirm it with Ctrl Shift Enter.

=IFERROR(INDEX(T:Z,SMALL(IF(ISNA(MATCH(INDEX(T$3:Z$100,0,R$2),S$3:S$7,0)),ROW(B$3:B$100)),ROWS(B$3:B3)),R$2),"")

 

[limo2088]

Sheets doesn't have the aggregate function. Try this one instead. You will need to array confirm it with Ctrl Shift Enter.

=IFERROR(INDEX(T:Z,SMALL(IF(ISNA(MATCH(INDEX(T$3:Z$100,0,R$2),S$3:S$7,0)),ROW(B$3:B$100)),ROWS(B$3:B3)),R$2),"")

Fantastic, what about this one?

IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW(E$2:E$50)/ISNA(MATCH(E$3:E$25,D$2:D$50,0)),ROWS(F$2:F6))),"")

 

[jasonb75]

Should be able to switch it around the same as the first one. One thing that seems odd is that this one is starting from the 4th match, so you might need to change F6 to F2 at the end of the formula.

=IFERROR(INDEX(E:E,SMALL(IF(ISNA(MATCH(E$3:E$25,D$2:D$50,0)),ROW(E$2:E$50)),ROWS(F$2:F6))),"")

 

[limo2088]

Should be able to switch it around the same as the first one. One thing that seems odd is that this one is starting from the 4th match, so you might need to change F6 to F2 at the end of the formula.

=IFERROR(INDEX(E:E,SMALL(IF(ISNA(MATCH(E$3:E$25,D$2:D$50,0)),ROW(E$2:E$50)),ROWS(F$2:F6))),"")

I apologize I am a bit slow, this is the exact formula with the exact cell locations, I tried chaning it to what you have and I get a blank cell

=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$3:C$25)/ISNA(MATCH(C$3:C$25,B$3:B$22,0)),ROWS(D$2:D2))),"")

 

[jasonb75]

I'll try and break down how to change it for you but I can write formulas much easier than I can explain how they work. It's probably best if I just highlight one change at a time.

First copy the part in bold
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$3:C$25)/ISNA(MATCH(C$3:C$25,B$3:B$22,0)),ROWS(D$2:D2))),"")
and insert it closer to the end of the formula as shown here (note that I've also added an extra closing bracket and comma at the end of the inserted part).
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$3:C$25)/ISNA(MATCH(C$3:C$25,B$3:B$22,0)),ROW(C$3:C$25)),ROWS(D$2:D2))),"")

Next remove the section shown in bold here,
=IFERROR(INDEX(C:C,AGGREGATE(15,6,ROW(C$3:C$25)/ISNA(MATCH(C$3:C$25,B$3:B$22,0)),ROW(C$3:C$25)),ROWS(D$2:D2))),"")
and replace it with SMALL(IF( as shown to get your final formula

=IFERROR(INDEX(C:C,SMALL(IF(ISNA(MATCH(C$3:C$25,B$3:B$22,0)),ROW(C$3:C$25)),ROWS(D$2:D2))),"")

Finally, something that I forgot to mention with the formula in post 4, whenever you use this method it must be array confirmed with Ctrl Shift Enter, the same as you did for the one in post 2.