Vlookup multiple matches

[dwilson38550m]

Hi,

Is there a formula I can use to pick up/return all matches when a Vlookup is used rather than just the first correct match. If I have data in column A and B, and 4 matches for the identifier on column A how can I capture all 4 matches?. Note the matches are email addresses....so column A is a customer account, column B is a listing of customer accounts.

I tried offset and match but I struggle to capture where there are 4,5,6 matches?
Thanks

Thanks

 

[Excel Planet]

Hi,

There is an option of using array formulas to return multiple matches. Have a look at this here.

 

[Aladin Akyurek]

D1 houses an identifier of interest,

In D2 enter:

=COUNTIFS($A$2:$A$100,D$1)

In D3 control+shift+enter, not just enter, and copy down:

=IF(ROWS(D$3:D3)>D$1,"",INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=D$1,ROW($B$2:$B$100)-ROW($B$2)+1),ROWS(D$3:D3))))