VLOOKUP Multiple data lines

[michaelpashley]

Hi, and apologies but I could not find the answer on historic threads.

I am trying to pull data through using vlookup where there are multiple entries within the table. I have been using OFFSET and MATCH to move on cells, using the below formula:

=IFERROR(VLOOKUP($K$14,OFFSET(REPORT!$B3,MATCH($K$14,REPORT!$B3:B20269,0),0,20000,5),5,0),"")

Basically, I want to pull all individual data entries from column F when the data in column B matches K14 on another sheet.

this works well when the range is limited to the data I need being at the top, but on a much larger range where the data is further into it (I am using a 20,000 line range) the formula brings the same data up over and over.

Question is; can anyone advise how to amend the "B3" entry on the formula to pick up on the next cell down from the last result instead of just the next cell on the list?

 

[Special-K99]

Try this

=IFERROR(INDEX($F$1:$F$20000,SMALL(IF(($B$1:$B$20000=$K$14),ROW($B$1:$B$20000)),ROW(B1)),1),"")
Array formula, use Ctrl-Shift-Enter
and copy down the column as far as row 20,000

 

[Aladin Akyurek]

K13:

=COUNTIFS(REPORT!B3:B20269,K14)

K15, control+shift+enter, not just enter, and copy down:

=IF(ROWS($K$15:K15)>$K$13,"",INDEX(REPORT!$F$3:$F$20269,SMALL(IF(REPORT!$B$3:$B$20269=$K$14,row(REPORT!$F$3:$F$20269)-ROW(REPORT!$F$3)+1),ROWS($K$15:K15))))