3 Easy Ways to Use VLOOKUP to Return Multiple Values

Deepanshu Bhalla 2 Comments

This tutorial explains various ways to extract multiple values for a single lookup value in Excel.

The VLOOKUP function is used to find the first match in a column but there is a way to find multiple matches.

Here are some real-world scenarios where this can be useful:

  • Suppose you have an inventory database with multiple entries for each product. You want to see all the purchase records for a specific product.
  • Suppose you have employee details where one employee may have worked with multiple teams during their tenure. You need to extract all records of employees.

Sample Data

Suppose you have data in cells B3:D7 in your Excel Worksheet. You want values from column C and D based on a lookup value matching the column B.

Return Multiple Values For Lookup Value

Method 1: INDEX-SMALL Function

Enter the following formula in C11 and paste it down and to the right.

=INDEX(C$3:C$7, SMALL(INDEX(($B$11=$B$3:$B$7)*(MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)))+($B$11<>$B$3:$B$7)*1048577, 0, 0), ROW(A1)))  

B$3:B$7 - Lookup column
B11 - Lookup value
C$3:C$7 - Desired column from which you want the formula to return results

Explanation of the formula

The formula finds a value in column C based on whether a condition in column B is met. It then selects the kth smallest value from the filtered list, where k is based on the row number of cell A1.

  • =INDEX(C$3:C$7, SMALL(...)): This part means we want to get a value from cells C3 to C7. The SMALL function will help us decide which value to pick.
  • SMALL(INDEX(...), ROW(A1)): The SMALL function is like saying, "Give me the 'k'-th smallest value from a list." The 'k' value is determined by the ROW(A1) part.
  • INDEX(($B$11=$B$3:$B$7)*(MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)))+($B$11<>$B$3:$B$7)*1048577, 0, 0): This long part is like creating a special list of numbers to choose from. Here's what it does:
    • It checks if the value in cell B11 is the same as any value in cells B3 to B7 and puts a "TRUE" or "FALSE" for each row.
    • It makes a list of row numbers from 3 to 7.
    • It combines these two lists using multiplication and addition. If the value in B11 matches a value in B3 to B7, it keeps the row number; otherwise, it puts 1048577. This creates a new list.
  • Finally, the SMALL function picks the 'k'-th smallest value from the list created in step 3, where 'k' is determined by ROW(A1).

Method 2: FILTER Function

If you are an Excel 365 user, you can use the FILTER function. Remember this function is available only in Excel 365. In this case, we are looking for values in the range C3:C7, and we are using cell B11 as the reference to find them. The values we are searching for are in the range B3:B7. Copy and paste the following formula into cell C11 and then drag it down and to the right to fill the adjacent cells.

=FILTER(C$3:C$7, $B$11=$B$3:$B$7)

Method 3: Array Formula

In this method, we are using an array formula which is a bit complicated to manage so I would recommend using the previous two methods. Here we want to find values in the list from cells C3 to C7. We are using the information in cell B11 to do that, and the values we are searching for are in the list from cells B3 to B7. Just copy and paste the following formula into cell C11, and then drag it down and to the right to fill in the nearby cells.

=INDEX(C$3:C$7, SMALL(IF(($B$11=$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""),ROWS($A$1:A1)))

Note: Hit CTRL SHIFT ENTER to confirm the above formula as an array formula instead of simply pressing ENTER.

This formula is designed to create a list of values from column C$3:C$7 where the corresponding value in column $B$3:$B$7 matches the value in cell $B$11. As you drag the formula down, it extracts the next matching value from the list until there are no more matches.

  • This formula uses the INDEX function to return a value from the range C$3:C$7.
  • It uses the SMALL function in combination with IF, MATCH, and ROW functions to extract values based on specific criteria in column $B$3:$B$7.
  • The IF function checks if the value in cell $B$11 matches any values in the range $B$3:$B$7. If there is a match, the MATCH function is used to find the position of the matching value in the range $B$3:$B$7. If there's no match, an empty string ("") is returned.
  • The ROWS($A$1:A1) part generates a series of numbers (1, 2, 3, ...) as you drag the formula down. These numbers find out the kth smallest value to extract from the list.
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 2 Responses to "3 Easy Ways to Use VLOOKUP to Return Multiple Values"
  1. Deepanshu, I just implemented learnings from 4-5 of your articles to automate a few critical reports! Easily saved a minimum of 20 minutes a day with these changes! You're the man!

    ReplyDelete
  2. Ahh thank you for this article. I was able to adapt the formula to help me create a spreadsheet for tabulating and comparing data that I'll need to export on multiple separate occasions. The kicker is that every time I tabulate the data, there are different amounts of entries with identical value in the row, so I was able to throw an adaptable counter into this Index/Small combination to spit out all entries that had duplicates in a column. You saved me a lot of time!

    ReplyDelete
Next → ← Prev