How VLOOKUP Returns Items Repeated N Times

Among many misconceptions about the VLOOKUP function is that it cannot return items repeated n times.

This is not true!

For example, Using the data below, can you return the names of the customers who have bought 3 times using VLOOKUP?

Here are the steps:

1. Create a helper column that counts the number of repeats per customer.
2. Use VLOOKUP to look at the n times from the column and repeat the name.

=VLOOKUP(
SMALL(
      IF(tblSupp[Repeat]=$F$2,ROW(tblSupp[Repeat])),
ROW(A1)),
   CHOOSE({1,2},ROW(tblSupp[Repeat]),tblSupp[Customers Name]),
       2,FALSE)

How It works:

=COUNTIF($B$1:B2,[@[Customers Name]]) creates a list of numbers a customer is being repeated. By making B1 absolute, the COUNTIF function aggregates the count from the top of the list up to the bottom

The only way VLOOKUP can return multiple items is by looping through the row where the items are.

The SMALL function helps loop through the rows that meet the criteria being tested by the IF function

IF(tblSupp[Repeat]=$F$2,ROW(tblSupp[Repeat])), returns only row numbers where the criteria (F2) is met.

The SMALL function returns these row numbers one by one starting with the smallest (ROW(A1)=1).

Since we are using row numbers as our lookup values, use the CHOOSE function to return a table array with 2 columns.

The left column is all REPEATS row numbers and the other customer name.

CHOOSE({1,2},ROW(tblSupp[Repeat]),tblSupp[Customers Name])

DOWNLOAD WORKSHEET

RELATED ARTICLES

VLOOKUP Exact Partial Match (Multiple Results)

5 Functions that Return Multiple Results In a Lookup