How to return multiple values using vlookup in excel
The VLOOKUP function is designed to return only the corresponding value of the first instance of a lookup value. But there is a work-around to identify multiple matches.
Array formula in C8:
=VLOOKUP($B$8, INDEX(tbl, SMALL(IF($B$8=INDEX(tbl, , 1), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROW(1:1)), , 1), 2, FALSE) + CTRL + SHIFT + ENTER copied down as far as needed.
Named ranges
tbl (B2:C6)
What is named ranges?
How to implement array formula to your workbook
Change the named range. Change bolded values in the formula above.
Download excel file for this tutorial.
Vlookup.xls
(Excel 97-2003 Workbook *.xls)
Functions in this article:
VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in ascending order.
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
SMALL(array,k) returns the k-th smallest row number in this data set.
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text
ROW(reference) returns the rownumber of a reference
Read more articles about this topic:
- Using array formula to look up multiple values in a list
- Search for multiple text strings in multiple cells in excel
Ream more Excel – Search/Lookup
Related posts:
- Vlookup with 2 or more lookup criteria and return multiple matches in excel
- Vlookup of three columns to pull a single record
- Sum adjacent values using multiple lookup text values in a column in excel
- Lookup a value in a list and return multiple matches in excel
- Lookup values in a range using two or more criteria and return multiple matches in excel
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Lookup with multiple criteria and display multiple search results using excel formula, part 3
- Lookup with multiple criteria and display multiple search results using excel formula
- Search for multiple text strings in multiple cells in excel
- Return multiple values if in range in excel
November 13th, 2009 at 8:56 pm
Ok - I absolutely MUST comment on this! I've spent my entire day looking all over the web for help on doing a VLOOKUP to look up one value and return multiple corresponding values and have not found anything that has helped me as much as you have! =D You've made my day. Thank you for posting this!
~kenbra
November 14th, 2009 at 8:56 am
I am happy you found this post, but my advice is to take a look at this post instead: Using array formula to look up multiple values in a list. It has an array formula not as complicated as this one.
Thank you for your comment!
February 10th, 2010 at 5:34 am
Hi. I used this formula and it works great. However I like to know how the formulas I use work. I have spent a lot of time on the internet trying to break it down but this one has me stumped. I understand part, like the VLOOKUP and INDEX but I don't know how the rest fits in. Are you able to break this down for the dummies? If you have time it would be greatly appreciated.