5 Functions that Return Multiple Results In a Lookup

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

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

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: