To Lookup Batch No's from Multiple Tabs in Another File

[fareedexcel]

Dear Bosco,

Thanks for the code. I tried in the spreadsheet but again there need to be a tweak to be done in the aggregate function replace with small and if function. I tried changing in the spreadsheet ending up with no results.

 

[bosco_yip]

Dear Bosco,

Thanks for the code. I tried in the spreadsheet but again there need to be a tweak to be done in the aggregate function replace with small and if function. I tried changing in the spreadsheet ending up with no results.

Hi,

1] For the SMALL+IF array formula, I put it in Col A.

In A3, array (CSE) formula copied down :

=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF(C$3:J$13<>"",ROW($3:$13)/1%+COLUMN(C:J)*100001),ROW(A1)),4),"R0C00"),),"")

2] See attached file.

Regards
Bosco

 

[fareedexcel]

Thanks Bosco (Excel Ninja) for your extended support. Appreciate your effort and help. One Formula has almost has 8 Unique Functions. That's great.