VLOOKUP MATCH with returning multiple values

Question

ColumnA (All customers)     ColumnB (E-mail)     ColumnC (Specific customers)

CustNO                      MailAddress          CustNO
1                           [email protected]        2
2                           [email protected]      1
                            [email protected]      1

I am using the following function to return the value of ColumnB when the value of ColumnA matches in a row of ColumnC:

=VLOOKUP(C2;A2:A520;MATCH(B1;A1:C1))

The problem is, that ColumnC can contain multiple values of ColumnA as shown in the above example.

The function as above is only returning the value of ColumnB, for only the first match found of ColumnA and ColumnC. Any idea how I can return all of the values for also the multiple returning values, as shown in above example, ColumnC contains multiple rows with CustNO 1.

R3uK · Accepted Answer · 2017-02-15 11:05:03Z

2

you need to use the index and small functions to extract the 1st, 2nd, 3rd ... matching values.

=IFERROR(INDEX($A$2:$C$250,SMALL(IF(($A2:$A$250=$C$2),ROW($A$2:$A$250)-1),ROW(1:1)),2),"")

IMPORTANT: use CTRL+SHIFT+ENTER when entering this formula to make it an array formula.

note that this formula uses ROW(1:1) to extract the first match, this will change to 2:2, 3:3 etc as to auto fill down.

I don't know from your question where you want to display the results but I would suggest that you take the unique values from column C and and transpose them across the top of a new sheet and then put the lookup formula below each value so you have a list of email addresses for each customer number.

edited Feb 15, 2017 at 11:05

R3uK

14.6k8 gold badges49 silver badges81 bronze badges

answered Feb 15, 2017 at 10:57

Gordon

1,1751 gold badge7 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VLOOKUP MATCH with returning multiple values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related