Vlookup with 2 or more lookup criteria and return multiple matches
In this article I'll show you how to lookup two or more values in a list and return (if possible) multiple matches.
The picture below shows you a table in column B and C, the search criteria is in column B and the results are in column G.
I am not using VLOOKUP at all in this array formula, the VLOOKUP 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.
The VLOOKUP function is not designed to look for multiple values and return multiple values.
Update 18 December 2020, the new FILTER function is now available for Excel 365 users, formula in cell E7:
This formula is entered as a regular formula, read here how the formula works in detail: Filter values based on criteria
The formula below is for earlier Excel versions, array formula in E7:
Recommended article
Recommended articles
This article demonstrates how to extract the largest number smaller than a given number based on a condition and criteria. […]
How to enter an array formula
- Copy the aray formula above (Ctrl + c)
- Double press with left mouse button on cell G3
- Paste (Ctrl + v)
- Press and hold Ctrl + Shift simultaneously
- Press Enter
- Release all keys
If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:
{=array_formula)}
Don't enter these characters yourself, they appear automatically.
Recommended articles
Array formulas allows you to do advanced calculations not possible with regular formulas.
How to copy the formula to cells below
- Select cell E7
- Copy cell (Ctrl + c)
- Select cell range E8:E9
- Paste (Ctrl + v)
How the array formula works in cell E7
You can easily follow a long as I explain the array formula, get the workbook. Select cell B13, go to tab "Formulas". Press with mouse on "Evaluate formula" button.
Press with mouse on "Evaluate" button show above to move to next step.
Step 1 - Count matching search criteria in column B
COUNTIF($E$3:$E$4, $B$3:$B$7)
becomes
COUNTIF({"US";"Asia"},{"US";"Europe";"US";"Europe";"Asia"})
and returns {1;0;1;0;1}
Recommended articles
Counts the number of cells that meet a specific condition.
Step 2 - Convert boolean array into corresponding row numbers
IF(COUNTIF($E$3:$E$4, $B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")
becomes
IF({1;0;1;0;1}, MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), "")
becomes
IF({1;0;1;0;1}, {1;2;3;4;5}, "")
and returns {1;"";3;"";5}
These are the row numbers that correspond to the matching values US and Asia in column B.
Recommended articles
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Step 3 - Returns the k-th smallest row number
SMALL(IF(COUNTIF($E$3:$E$4,$B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""),ROWS($A$1:A1))
becomes
SMALL({1;"";3;"";5}, ROWS($A$1:A1))
becomes
SMALL({1;"";3;"";5}, 1)
and returns 1.
Recommended articles
The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.
Step 4 - Return a value based on coordinate
INDEX($C$3:$C$7, SMALL(IF(COUNTIF($E$3:$E$4, $B$3:$B$7), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1)))
becomes
INDEX($C$3:$C$7, 1)
becomes
INDEX({"Pen";"Eraser";"Paper";"Pen";"Paper clip"}, 1)
and returns Pen in cell E7.
Recommended articles
Gets a value in a specific cell range based on a row and column number.
Get excel *.xlsx file
Vlookup with multiple search conditions and return multiple matches.xlsx
Vlookup and return multiple values category
This post explains how to lookup a value and return multiple values. No array formula required.
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
This article demonstrates an array formula that searches two tables on two different sheets and returns multiple results. Sheet1 contains […]
Excel categories
46 Responses to “Vlookup with 2 or more lookup criteria and return multiple matches”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Oscar, on the first formula, any reason why you have multiplied with ))*(SEARCH(search_tbl, TRANSPOSE(INDEX(tbl, , 1, 1))))? As this step looks redunant?
Thanks!
I forgot this post.
Using countif() instead of search() reduces formula size.
The reason why I multiplied two search() in the first place, was to remove any cells that contained the search criteria, I was looking for exact matches.
Does anyone know how to do a vlookup of three columns to pull a single record?
Andy,
Can you elaborate?
Match a single criterion in any of three columns?
Match three different criteria in each column?
Match any of three different criteria in any column?
Andy,
See this post: https://www.get-digital-help.com/2010/01/10/vlookup-of-three-columns-to-pull-a-single-record/
Hello Oscar,
I tried the formulas couple times and they do not work. I want Vlookup to add all the group A $ and placed on Sheet2 group A. For example:
Sheet1:
GRP $
A 2
A 2
A 2
B 3
B 3
B 3
C 4
C 4
C 4
Sheet2:
Grp $
A ???
B ???
C ???
if I add manually group A total is $6 and B total is $9 and C total is $12.
Thank you very much,
newsuteuser2011
newsiteuser2011,
or create a pivot table on sheet2
Oscar, what happen if I need to use IF(ISERROR(Vlookup)) and the datas are pretty big for every month. Is there a way to request Vlookup to pickup more than the first data?
Thank you,
Newsiteuser2011
newsiteuser2011,
Try the formula in this post: How to return multiple values using vlookup in excel
I have a large database with 40 columns, I wish to extract a few column data into separate sheet for printing. The goal is actually a male name list and a female name list with another criteria from a column that is confirmed. Which page shows the examples?
Thank you
Alex Chiok
Alex Chiok,
Excel tables are easy and fast!
https://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx
Oscar,
I am using this formula and have found that it does not produce all of the results from the data file.
My data sheet contains:
Column (A) - Account Number
Column (B) - Date
Column (C) - Notes
There are 1 - 15 instances of the account number in Column (A).
The instances may be on different or the same day (date) in column (B).
The notes in Column (C) are all different.
The results being returned are limited to only two rows per date.
So if I have 5 Notes on 01/01/2012....the results are only returning 2 of them...it appears to be the two middle rows.
Can you help.
Thanks for the great formula!!
I should have included the formula mentioned above just to be clear:
=INDEX(COMMENTS!$I$2:$I$15000, SMALL(IF((GET_COMMENTS!$E$3=COMMENTS!$A$2:$A$15000)*(GET_COMMENTS!$E$3=COMMENTS!$A$2:$A$15000), ROW(COMMENTS!$A$2:$A$15000)-MIN(ROW(COMMENTS!$A$2:$A$15000))+1, ""), ROW(A1)))
Oscar,
Nevermind, I'm loosing it. Too many hours in front of a spreadsheet. :)
beth,
Get the Excel file:
beth.xlsx
Thanks, This is so useful!!
your explanation makes wonders happen.
But can I ask how do you learn this kind of stuff?
Richard,
Thanks!!
But can I ask how do you learn this kind of stuff?
Inspired by others. Trial and error. As far as I know there are no array formula books out there.
Hi Oscar,
On the above example, is there any way I can tranpose the results against the original search ref. as oppose to the vertical lists so that I don't have just a long string of values. That way I can quickly match the corresponding values with the original search ref.
Hope that makes sense!
Emma,
I believe this post is what you are looking for: https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/#horizont
Thanks for the references to excel on your website, they have been very helpful. I am trying to alter your formula without any avail.
I am trying to perform a lookup with two criteria and return multiple values which matches the search. I have two worksheets on named Source Data and Available Filler by Page. Source Data has four columns: Book #, Filler Name, Page #, Size
I neeed to lookup the book # and corresponding size to return the page numbers which are available for them horizontally, but i have only been able to return only one criteria because I am not using the AND function correctly inside the IF statement. Here is the formula I have been using:
=IFERROR(INDEX('Source Data'!$C$2:$C$10000,SMALL(IF(AND('Available Fillers by Page'!$B$7='Source Data'!$A$2:$A$10000,'Source Data'!$D$2:$D$10000='Available Fillers by Page'!$E$8),ROW('Source Data'!$A$2:$A$10000)-MIN(ROW('Source Data'!$A$2:$A$10000))+1,""),COLUMN(A1))),"")
Any assistance would be great. Thank you in advance.
I found a formula on another page but how do I get the formula to list the values horizontally. Here is the new formula I used:
=IFERROR(INDEX('Source Data'!$C$2:$C$10000, SMALL(IF(('Available Fillers by Page'!$B$7='Source Data'!$A$2:$A$10000)*('Available Fillers by Page'!$E8='Source Data'!$D$2:$D$10000), ROW('Source Data'!$C$2:$C$10000)-MIN(ROW('Source Data'!$C$2:$C$10000))+1), COLUMN(1:1))),"NFA")
This formula only lists the first value when copied horizontally.
Nevermind my last post, I got it. Once again thank you for the website. I will leep it as a favorite because I know I will be needing it another time.
Dominica Smith,
I am happy you found what you were looking for.
Hi Oscar,
Thanks for the example. i am not sure how to modify it so it meets my need.
Going back to the example, i want to write a formula to display "2" if "pen" AND "$1,50" are the criteria because they exists 2 times...
Hope this clarify. Thanks in advance.
Cheers,
Celic
Celic,
Formula:
=COUNTIFS(B2:B6,B9,C2:C6,C9)
Hi sir I need your help I have spread sheet in coloum A is tel# B is name C is eye glass rx ,.....etc this for the customer report and one customer bought the glass many times always he is using the same tel# each time he had diffrent priscription this all the result is in sheet#1 in sheet # 2 I want find the his file report according to tel# I am useing the vlookup formula to find the report when I use the vlook up I got the first report only how can I find if customer purchase 10 times one by one result with the same tel# my english is not really good thanks for your time Mehaboob [email protected]
mehaboob
Read this post:
https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/#multiple
Hi Oscar,
i am hoping you could please assist me with the following, i have looked through many of your posts but cannot seem to find a solution.
I am trying to match 2 cell values, and return a third value from a source sheet in vertical rows.
the formula i am trying is:
=INDEX('2014'!B3:B16,SMALL(IF($D$2='2014'!F3:F16)*('Feb 14'!E2='2014'!A3:A16),MATCH(ROW('2014'!B3:B16),ROW('2014'!B3:B16))),ROW(A1))) +ctrl + shift +enter
where '2014' is the source sheet
'2014'!B3:B16 is the column with the values i want to return (name)
$D$2 is the city name ='2014'!F3:F16 is the location column
$E$2 is the month name ='2014'!A3:A16 is the date column
I have tried other formulas too but nothing seems to be working, this seems to be the closest to what i am after.
If you could help it would be greatly appreciated.
Thanks.
Holly, you have forgotten absolute cell references.
=INDEX('2014'!$B$3:$B$16, SMALL(IF($D$2='2014'!$F$3:$F$16)*('Feb 14'!$E$2='2014'!$A$3:$A$16), MATCH(ROW('2014'!$B$3:$B$16), ROW('2014'!$B$3:$B$16))), ROW(A1)))
I am not sure about these cell references: $D$2 and $E$2. It depends on what you are doing.
Oscar this is a great post thanks! I have a question similar to the original post.
If the stores names that these items were purchased at were in cells A2:A6 for example- Walmart in A2, Target in A3, CVS in A4, A5 and A6. Also assume that in cell A7 was Walmart, B7 was Pen, and C7 was $3,25
I need a formula to say if the pen was purchased at Walmart how much did it cost? Instead of search criteria being Pen and Paper I would like the search criteria to be Walmart and Pen. The return would be "$1,50" and "$3,75"
Thanks in advance!
correction: I had one typo the return would be "$1,50" and "$3,25"
John,
Array formula in cell B14:
=INDEX($C$2:$C$7, SMALL(IF(($B$10=$A$2:$A$7)*($B$2:$B$7=$C$10), MATCH(ROW($C$2:$C$7), ROW($C$2:$C$7)), ""), ROW(A1)))
Hi, I need some help!
I am trying to create a way to search different kits at one time. Let's say I have three columns, one for "Kits", one for "Products", and one for "Quantities". Is there a way that I can search multiple kits at one time to bring up the different products and quantities? My data spreadsheet has several data points with over 3,000. What is the easiest way to go about this? Thanks!
Can you please e-mail me so I can attach the file that I need to have these search entries for? Every time I try creating or editing the spreadsheet I get a "#VALUE" entry instead of pulling from my data.
Oscar, I hope you can help me. I need to do this formula (preferably with the dynamic range name like you have here)but without the use of the COUNTIF? Ive tried combinations of SUM (IFs) but can not figure it out. The reason is with Countif, sumif and etc you need the source file open... I need to do the multiple lookups when the source file of the table is closed. Is this possible?
=INDEX(tbl, SMALL(IF(COUNTIF(search_tbl, INDEX(tbl, , 1, 1))>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)), 2)
I am working in a railway project as planner. How can i create a dynamic strip chart in excel?
Assume total length be 10 kilometers and each cell of 100 meters.
If I update the progress from 9.01 kms to 9.08 kms in the table, how the cell gets highlighted corresponding to the entered chainages.
Sankalp,
Read this: https://www.get-digital-help.com/2017/07/06/track-progress/
How do I lookup lot ref no ranges in between in below data set. For eg lookup for "SEC48" would be "Lot1"
MIN MAX Lot Ref No
SEC1 SEC50 Lot1
SEC51 SEC101 Lot2
SEC102 SEC152 Lot3
SEC153 SEC203 Lot4
SEC204 SEC254 Lot5
SEC255 SEC305 Lot6
Maulik
Thanks for a great comment.
Read this article: https://www.get-digital-help.com/2018/02/26/match-a-range-value-containing-both-text-and-numerical-characters/
Hi Oscar,
Thank you for this formula. This is great! I’m trying to do something similar but can’t seem to make it work. I’m hoping you can help me figure it out. I’m trying to compare column A on 2 sheets (Sheet1 and Sheet2) and if it finds a match then compare column C ($ amount) of that row on each sheets and display the difference of column C in column D.
If column A has an ID # with contains letters, numbers, and symbols but isn't always a unique ID # and column C has a $ amount, find the match and put the $ amount difference in column D.
So if the ID # and $ amount match exactly, the difference would be zero, if the ID # matches but the $ amount doesn’t, the difference would be the $ amount of one sheet less the $ amount on the other sheet, and if there is no match the $ difference would be the full $ amount.
Is there any way to use your formula to do this?
Thanks in advance for your help!
Hi, I've been struggling with something for a few days now and I'm not even sure that I can do it in Excel? I've tried various forms of INDEX MATCH and MMULT and anything else that I thought might work. The concept is simple. I need containers in various cabinets for a home remodel. The catalog that I am perusing has TONS of options and I have specific dimensions that I need to satisfy for each cabinet, drawer, etc. So, I have a spreadsheet that lists my actual dimensions in three columns (width, depth, height) and all of the possible options from the catalog along with columns for their dimensions (width, depth, height). I need to find which catalog trashcan would best fit my space. I need to find a dimension for width that matches MY space's width (less than or equal to), same for the depth and height. BUT, I need to do this for all three dimensions AND get Excel to return the product number that best fits. Am I asking too much from Excel? I realize that a database may serve this purpose better but I'm dragging my feet on going with a db.
actual measurements catalog
A B C D E F G
1 width depth height product# width depth height
2 241.3 457.2 393.7 311 111 230 343
3 312 143 211 275
4 313 211 423 275
5 314 230 305 310
6 315 255 400 413
So, product #311 works but it is not the best choice (too small). #315 works for depth but not for width or height. If I were looking for the correct one, I might pick #313 as the best fit but #314 also works. I'd be thrilled if Excel would pick either or both of those LOL. Obviously, my spreadsheet is much larger with too many options for me to look through myself. If I could get excel to return ALL products that fit the criteria (all product dimensions are smaller than the actual space dimensions), I'd be happy.
Any advice?
Sir,
I have a problem,
I have to find out the time of occurance(in Column D) according to Rate(in Column B)
(Rate is in multiple times)
and according to Quantity (in Column C)
(quantity is also repeating )
Hello, I need to do a search like this but my search criteria is a date range, For example March 1st to March 31st. I need to return all the entries/rows between that range.
Thanks Benjamin
pls help , i have 2 list both have starting and ending , i want to find out the gap in second list which length is mising with number also. ex i have
a b
5 to 10
12 to 20
25 to 30
c d
5 to 8
14 to 18
26 to 30
i want result like
e f
8 to 10
12 to 14
18 to 20
25 to 26
Looking to do a 2-column nested vlookup to return a 3rd column value
So...
A1 B1 C1
Apple Car 19
Apple Bike 24
Orange Boat 10
Banana Skates 1
Search Terms: Apple (in A1-A5) + Car (B1-B5)
Result=19
I've looked through all your Vlookup pages but cannot find
one formula with 2 separate search terms 1 in each column to return a third value.
Here's hoping you can save my life again!
Thanks a bunch for all your work, you are literally the only array website I've seen anywhere!