Most lookup functions by default return either the 1st or the last match item. How do you return all matching multiple results in a lookup?

How do you return all the customers who bought the product below?

USING VLOOKUP TO RETURN MULTIPLE RESULTS

=VLOOKUP(SMALL(IF(tblPdts[Product ID]=$F$3,ROW(tblPdts[Product ID]),””),ROW(A1)),
CHOOSE({1,2},ROW(tblPdts[Customer Name]),tblPdts[Customer Name]),2)

The trick here is to first get a list of row numbers with the criteria and then create a 2 column table with row numbers and customer names.

SMALL(IF(tblPdts[Product ID]=$F$3,ROW(tblPdts[Product ID]),””),ROW(A1)) the SMALL function loops through the rows as you drag down the formulas

USING INDEX TO RETURN MULTIPLE RESULTS

=INDEX(tblPdtz[Customer Name],
SMALL(
IF(tblPdtz[Product ID]=$F$3,ROW(tblPdtz[Product ID])-ROW(tblPdtz[[#Headers],[Product ID]]),””),
ROW(A1)))

Unlike the VLOOKUP example above, when generating the customer rows for INDEX, you have to deduct the header row for standardization purposes. Else INDEX will lookup the wrong value.

ROW(items)-ROW(header)

USING XLOOKUP TO RETURN MULTIPLE RESULTS

Like the VLOOKUP function above, in XLOOKUP you do not have to standardize the rows.

=XLOOKUP(
SMALL(IF(TblProd[Product ID]=$F$3,ROW(TblProd[Product ID]),""),ROW(A1)),
ROW(TblProd[Customer]),TblProd[Customer])

SMALL(IF(TblProd[Product ID]=$F$3,ROW(TblProd[Product ID]),””),ROW(A1)) returns row numbers that contain the Product ID “FUR-TA-10000577”. This becomes our lookup item

Our Lookup array then becomes corresponding customer rows ROW(TblProd[Customer])

Finally, our lookup value is the customer name TblProd[Customer]

USING THE FILTER FUNCTION TO RETURN MULTIPLE RESULTS

FILTER Function is the ultimate Lookup function in Excel

=FILTER(TblPdtz1[Customer Name],TblPdtz1[Product ID]=F3)

USING POWER QUERY TO RETURN MULTIPLE RESULTS

The Only Trick in this power query filter is Converting the Criteria table data to a singular value by Drilling down on it.

DOWNLOAD WORKSHEET

References:

Print Friendly, PDF & Email

Do you want to excel in Excel?

Or

Seeking Microsoft Certification?

Every Week Receive Resources To Get you Closer to Your Goal!

No Huff! No Fluff! No Spam!

You are on your way to Excelling in Excel and Becoming Microsoft Certified!

Share This

Spread the Good News!

Do me favour, please? Share this with your friends!

Thanks For Commenting!

Awesome for you to comment! Please share the post with your friends.