Puzzled... vlookup multiple matches (rows) only returns 2 matches where there are 3,4,5, matches

[dwilson38550m]

Hi,

I think I am almost there but need some help with a vlookup that has multiple matches and I would like to return all matches. I am trying to return all email addresses matching a customer account number. My problem is that the current formula only returns matches 1 and 2, but does not return matches, 3, 4 etc....is there a small adjustment anyone can suggest to the formula below?

Cell D5 identifies the number of matches I have for the reference field (customer name, cell A5)...so if I have 5 matches I need 5 results to be returned in each cell horizontally (G5, H5, I5, J5, K5).....G5 and H5 are Ok but nothing is returned in I5, J5, K5. I am looking up on a data table "Client Email List" with the key field customer name in column A and the value I want to return (email address) in column G (ie column 7).

=IF($D5>1,VLOOKUP($A5,OFFSET('Client Email List'!$A$1,MATCH($A5,'Client Email List'!$A$1:$A$10008,0),0,30,7),7,FALSE),"")


Thanks in advance

 

[Aladin Akyurek]

In G5 control+shift+enter, not just enter, copy across to K5...

=IF(COLUMNS($G$5:G5)>$D$5,"",INDEX('Client Email List'!$G$1:$G$10008,SMALL(IF('Client Email List'!$A$1:$A$1000=$A$5,
    ROW('Client Email List'!$G$1:$G$10008)-ROW('Client Email List'!$G$1)+1),COLUMNS($G$5:G5))))
​

​

<strike></strike>

 

[dwilson38550m]

Perfect. Works great now. Much appreciated.

 

[Aladin Akyurek]

Perfect. Works great now. Much appreciated.

You are welcome.