Here is the formula that is the basis of the solution ARRAY entered:
Formula:
=IFERROR(INDEX(IF(TMS!A$2:A$500<>"",TMS!A$2:A$500,""),SMALL(IF(TMS!$L$2:$L$500=$A$1,ROW(TMS!$L$2:$L$500)-MIN(ROW(TMS!$L$2:$L$500))+1),ROWS($1:1))),"")
INDEX has the following arguments which produces and intersection of ROW and COLUMN (the value that you want).
1. Array - this is a range of cells from which you want to extract a value.
2. The second argument is the ROW and in this formula it is being calculated by the combination of the SMALL and IF functions. The IF part is the condition that will produce an array for the SMALL function. In this case it is looking at the range TMS!$L$2:$L$500 for matches for the value in $A$1. ROW lists all the rows to be considered. This is followed by MIN(ROW... This combination gives a starting row but it is 1 row less than required so the +1 is added. We now have a list of rows and a starting point. This is the ARRAY part of the SMALL function whose arguments are ARRAY,K . The K part of the SMALL function is determined by the ROWS($1:1). This is a counter that increases by 1 with each row the formula is dragged down, finding the smallest, second smallest, third smallest values etc. All of this provides the rows that intersect with the INDEX range, one intersection at a time as the formula is dragged down with the fill handle.
IFERROR eliminates the display of error messages when the data runs out. The ,"") at the end of the formula is what the errors are replace with which in this case is a blank cell.
This is certainly easier to do than explain
Try this formula on small examples and it should become clear what is happening.
If anyone else has something to add/correct/explain please chime in.
LinkBack URL
About LinkBacks
Pulling data from master spreadsheet to another spreadsheet
Register To Reply
Re: Pulling data from master spreadsheet to another spreadsheet
Bookmarks