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.
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.
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
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
INDEXfunction to return a value from the rangeC$3:C$7. - It uses the
SMALLfunction in combination withIF,MATCH, andROWfunctions to extract values based on specific criteria in column$B$3:$B$7. - The
IFfunction checks if the value in cell$B$11matches any values in the range$B$3:$B$7. If there is a match, theMATCHfunction 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.
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.
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