When using Microsoft Excel for data analysis, you may often find yourself in situations when you need to get all matches based on a specific id, name, email address or some other unique identifier. An immediate solution that comes to mind is using the Excel VLOOKUP function, but the problem is that it can only return a single match. Continue reading
Comments on: How to Vlookup multiple values in Excel with criteria
by Svetlana Cheusheva, updated on
Comments page 5. Total comments: 342
When I replicate this example sometimes it works, sometimes its blank, and I cant figure out why. Any ideas on why the result sometimes comes out blank? No errors or anything just blank.
I'm doing this formula (the second one way up at the top)
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")
Sl.No ROLL NUMBER Student Name Application Id Course /Course Year Rtf Amount
1 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750
2 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750
3 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750
4 14G01A0101 A AJAY 201704689507 BTECH (CIVIL)/4 8750
5 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750
6 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750
7 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750
8 15G01A0201 A AKHILA 201705900172 BTECH (EEE)/3 8750
9 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 7450
10 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 3725
11 15242-EC-001 A AMRINBANU 201708033297 DIP (ECE)/3 3725
12 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750
13 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750
14 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750
15 17G01A0401 A B JANANI 201708256249 BTECH (ECE)/1 8750
16 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750
17 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750
18 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750
19 17G01A0301 A B RAJENDRAN 201708197050 BTECH (MECHANICAL)/1 8750
20 16G01A0401 A CHANDANA 201709098907 BTECH (ECE)/2 8750
21 16G01A0401 A CHANDANA 201709098907 BTECH (ECE)/2 26250
22 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725
23 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725
24 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725
25 16242-EE-002 A DILLI BABU 201709114468 DIP (EEE)/2 3725
I prepared this data based on the v look up fromula .now i need name wise total in from four coloumns in one cell in a separate row. To know the individual value. can you help for this
Thank you so much for this! This is exactly the help I needed. Also, I made a change to Formula 2 to make it a little more generic. Changed 'n' to row(first_formula_cell - 1).
Hi Svetlana,
When I applied the same example (as a training) in a blank sheet, it didn't work for me. After I read the comments I download the example file, I made a double click on a cell contains the formula to check it, I say ok, then press enter , as surprise I found the word displayed disappear, I don't know really why this is happening. I tried Online Excel to check maybe the excel version I had, has an issue or something.
Please Help
Thanks,
I forgot to say that a note showed up to me when I activate it , it said "inconsistent formula"
Ok the problem solved (CTRL+SHIFT+ENTER not just enter)
thanks
Res ID Date Feedback Res ID Date
18354576 7/17/2019 All good 18354576 7/17/2019
19165239 7/30/2019 All good 19165239 7/30/2019
18354576 7/31/2019 More Than 5 Error 18975236 7/31/2019
19167323 7/31/2019 All good 19167323 7/31/2019
18354576 7/31/2019 Less than 5 Errors 18785226 7/31/2019
19161337 7/31/2019 All good 19161337 7/31/2019
19153708 7/30/2019 Less than 5 Errors 19153708 7/30/2019
19162077 7/31/2019 All good 19162077 7/31/2019
19164149 7/31/2019 All good 19164149 7/31/2019
19164647 7/31/2019 Less than 5 Errors 19164647 7/31/2019
19166599 7/31/2019 All good 19166599 7/31/2019
19161016 7/31/2019 All good 19161016 7/31/2019
Hi
There is multiple entries in res ID and I want vlookup data same as it is in sheet, I mean res id 18354576 having 3 or more entries with different comment and different date.
How can I find exact comment with particular date for all entries.
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")
can anybody modify this so it does a partial text search
=IFERROR(INDEX($B$3:$B$13, SMALL(IF(ISNUMBER(SEARCH($D$2,$A$3:$A$13)), ROW($B$3:$B$13)-2,""), ROW()-2)),"")
IF YOURS WORKS FOR THE FIRST CELL BUT NOT THE REST, CLICK ON THE FORMULA BAR OF THE FIRST CELL AND CLICK - CTR SHFT ENTER - THEN DRAG THAT FORMULA. THE FORMULA SHOULD NOW HAVE {funky brackets}.
The one about Vlookup of multiple values is an absolute beauty. I have needed this function desperately and it worked exactly the way I have needed it. I don't know how to thank you. Still Thanks a million.
Goal: I would like to vlookup or index match an ID from another workbook using a vba button that then returns the ID name, City, Region, etc. My goal is that I have a button I search the ID # and it returns the criteria that I want that is located in another worksheet. I know to start it with the Workbooks.Open more focused on how to do the index match or vlookup to return the criteria from that sheet. Thanks
I stumbled upon this formula and spent about 2 hours trying to get it to work, but I finally did after seeing an error I was making in the comments (our fault for not reading fully)
My question is, using the very top example with sellers and produce, I manually need to change my cell reference from $D2 to $D3. My spread sheet contains over two hundred sellers and about a hundred different types of produce. So in a separate column I have my sellers, Adam being D2 and Robert D3. I can drag the formula for Adam all 100 rows.
But if I stop at row 25, and switch to D3 for Robert, I cannot copy them down the same way. I have to modify $D3 and ALSO the row to be -24 (when it was originally -2).
Is there a way around this? I'd rather only have to change one aspect instead of two if possible because there are so many. I can't set a predetermined spacing because it changes daily.
Thanks!
Side note, the formula I was using was this one below:
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")
You're the best! This saved me so many headaches, thank you so much!!
HI
When i use this code the excel have a error
=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")
the core of error begins from==>
IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,"")
i dont know what should i do :-(
thank you
i fund the issue
the problem was in
ROW()
cause of copy pasting the formula it reffer to the cell which is copied and it is not the rank that we want. so should change to 1,2,3 or "ROW()-cell row number+1"
Very helpful. Thank You!!!
Thanks a lot for your effort you saved my time.
Please give me the result column c, as completed or incompleted when lookup from column a into column b.
Column (a) Column (b) Result Column (c)
OTE3‐L1‐IM‐300992‐01 12-Apr Completed
OTE3‐L1‐IM‐300992‐01 13-Apr Completed
OTE3‐L1‐IM‐300992‐02 13-Apr Completed
OTE3‐L1‐IM‐300992‐02 Incompleted
OTE3‐L1‐IM‐300992‐03 Incompleted
OTE3‐L1‐IM‐300992‐03 Incompleted
OTE3‐L1‐IM‐300992‐03 Incompleted
OTE3‐L1‐IM‐300992‐03 Incompleted
Sally Oranges
Robert Apricots
Adam Bananas
Tom Apples
Robert Lemons
Tom Apricots
Sally Bananas
Adam Oranges
Tom Bananas
Adam Lemons
Sally Apples
X y
Please suggest what should I do if my range is extended till x & y. Simply changing range in your formula is not working.
This was a great tutorial, thank you very much! I got the formulas to work for my database, however I have an additional restriction. Bringing it to this example (I'm doing results in a ROW), let's say the products are listed as ORANGES - APRICOTS - BANANAS - APPLES - LEMONS. So if Adam sold Bananas, Oranges and Lemons, I want his results to show ORANGES - *blank* - BANANAS - *blank* - LEMONS. To make it easier to understand, my categories are time, so I need the results chronologically (6-10-12-14 hours). So if a result appears in 12 and 14, I want to see the 4 columns as blank-blank-result-result, but what I'm getting is result-result-blank-blank. Any thoughts?
Hi Francisco,
I guess you get precisely that result your are looking for by just stripping the SMALL function as follows bellow. It seems that the SMALL function is being used to fill up the blank cells so that the looked up products at the end they come one after another.
=IFERROR(INDEX($B$3:$B$13, IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), "").
I hope my reasoning sounds correct.
doing similar to Formula 1. Vlookup multiple values and return results in a column
However I have the data in a separate sheet called 'Eg Data' hence I changed my formula to read as follows:
=IFERROR(INDEX('Eg Data'!$B$3:$B$13,SMALL(IF(D$5='Eg Data'!$A$3:$A$13,ROW('Eg Data'!$A$3:$A$13)- MIN(ROW('Eg Data'!$A$3:$A$13))+1,""), ROW()-2)),"")
In example data all is the same as per example above in 'Formula 1. Vlookup multiple values and return results in a column' and using same cells (a3:b13)
the other only difference is that the name in my case 'Adam' is not in cell D2 as per example above but in D5 and I want the results in D6 no in D3 as per example above.
This seems to give a problem when I moved the name from D2 to D5 and results to be in D6 instead of D3
Does the formula need changing?
THANKS FOR YOUR HELP!!
This was so incredibly helpful! I looked at other tutorials prior to this, and none of them gave the background on "why" or "what" each step did so I couldn't manipulate the other examples to fit my needs. This was the only truly comprehensive guide I came across. Thank you so much!
Hello - I tried to implement Formula 1 in this article, Vlookup multiple values and return results in a column, using the same data entered in the same cells in an Excel 2019 spreadsheet. But the formula doesn't work. To see why, I stripped out the IFERROR code and just ran the INDEX function without error checking. This showed that the formula produces a #Value error in the result. Does anyone know why the example is not working for me? I'm truly stumped.
Hi Larry,
It's difficult to say what the problem might be without seeing your data and formula. IFERROR is a very simple function and usually it does not cause any issues.
You can download our sample workbook with working formulas and copy/past the desired formula from there to try it on your data.
I have a similar issue, happening with your sample workbook too. I open the sample workbook, all the formulas show exactly as on this site. If I then click in the formula bar and hit enter the calculated values that were there when I open the book disappear. The formula is still there it just returns the error result value. The only difference in the formula bar is that the {} brackets round the formula are no longer there after calculating the formula locally.
James,
You most likely need to re-apply the "Ctrl-Shift-Enter' command to let the formula know it is based on an ARRAY.
Put your cursor in the cell with the formula and press Control-Shift and Enter.
Just a thought...
i need help
Rujan Shakya Absent
Abhishekh Wosti Present
Santosh Adhikari Present
Kshitiz Pokhrel Present
Rujan Shakya Present
in above details need to just take present name to another sheet
can anyone please help me
I need to bring data from a master sheet where there is more than one entry, but might have slightly different data in a number of columns.
Your information was very helpful, thank you!!! I want to use the formulas to do an "or" statement. I am using the formula {=IFERROR(INDEX('CTE Detail'!$F$2:$F$900000,SMALL(IF('CTE Detail'!$M$2:$M$900000=auto!$F$1,ROW('CTE Detail'!$F$2:$F$900000)-1,""),ROW()-8)),"")}. This works as long as each program only has one subject code. I am bringing 2 spreadsheets together. What I am looking for is all of the students in certain classes by subject code (auto!$F$1). I have some programs that have multiple subject codes, how do I write the formula? I believe the * is for an "and" statement. Thank you,
Hi
I need some help with retrieving value from a table:
min max 1 2 3 4 5 6 7 8 9 10
0 8 0.00% 0.00% 2.50% 4.00% 5.00% 6.00% 7.00% 8.00% 9.00% 10.00%
9 10 5.00% 5.00% 7.50% 9% 10% 11% 12% 13% 14% 15%
11 24 7.50% 7.50% 10% 11.50% 12.50% 13.50% 14.50% 15.50% 16.50% 17.50%
25 49 10.00% 10.00% 12.50% 14% 15% 16% 17% 18% 19% 20%
50 99 12.50% 12.50% 15% 16.50% 17.50% 18.50% 19.50% 20.50% 21.50% 22.50%
100 199 15.00% 15.00% 17.50% 19% 20% 21% 22% 23% 24% 25%
200 >200 17.50% 17.50% 20% 21.50% 22.50% 23.50% 24.50% 25.50% 26.50% 27.50%
I am using the formula: =INDEX($E$7:$N$13,MATCH(C3,D7:D13,2), MATCH(C10,Sheet1!E6:N6,0))
However, everytime it is reading only the first line
Can you help me out. Thanks for reading.
Dear Svetlana
Your pages on Excel are really incredible! I learned a lot, but understand that there is still so much to learn!
I am searching for a solution to retrieve data from an input sheet. When one selects one or more search terms in another sheet (from a pre-defined list) I would like to see all the results that meet the conditions. It is very similar to your examples here, "Vlookup multiple matches based on multiple conditions". But instead of AND I would need OR. Eg: if the search term is 'apples', I would like to see Excel giving me a list of all the apple sales. If the search terms are 'apples' and 'bananas', I would like to see Excel giving me a list of all the sales of apples and bananas. I am searching for hours ans hours, but cannot manage it... Maybe you have the solution?
Thanks in advance!
=VLOOKUP(E5,A1:B8,2,0)&", "&INDEX(B1:B8,MATCH(E5,A1:A8,0)+2)
I need help to get Max value from set value but if any data is 0 ( zero) then return result should be 0(zero). Pls see below data & result what I want. Pls advise.
A 20
B 20
C 30
A 40
B 10
D 60
B 35
D 40
A 0
D 0
A 40
Result should be
A 0
B 35
C 30
D 0
This should do it. Assuming your data begins in cell A1. Make sure you hit Ctrl+Shift+Enter when finished as this is an array formula. {=IF(MIN(IF("A"=$A$1:$A$11,$B$1:$B$11,""))=0,0,MAX(IF("A"=$A$1:$A$11,$B$1:$B$11,"")))}
I need help to make a formula to retrieve information from a 'master' sheet (where information is entered manually) and populate specific cells in a 'target' sheet to avoid re-entering this information.
For example,
Master sheet
-----------
A B C D
1 Device ID Device MAC Hostname Service Tag
2 FS-001 00:00:00:00:00:00 device01 12345
Target Sheet
----------
A B C D E
1 Device ID Hostname IP address Device MAC Gateway
2 FS-001 device01 00:00:00:00:00:00
So, if on the 'Target' sheet A2 = 'Master' sheet A2's value of FS-001, then values from Master sheet B2 and C2 will be populated
in the cells of 'Target' sheet D2 and B2 respectively.
Have you found the formula for this? I need a similar one too.
Hi
I need some help to do retrieve information from an excel sheet and populate specific columns in another excel sheet within the same workbook. I tried this but I have given up with the formula to get it working.
For examle,
1. Sheet 1 is the master sheet where I enter the information into each cell.
For example,
Device ID Device MAC Hostname Service Tag
FS-001 Z9264F-ON 20040F0E3B4E frxsw-ds01 H5WMXC2
Good day !
Using the row array formula =IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), ""), let's say that Adam's first line column B is blank rather than "Bananas" - how can I skip the blank and show the next real value in cell E1? (in other words, E1 would be "Oranges")
Thanks!
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(($D3=$A$3:$A$13)*($B$3:$B$13""),ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(($D3=$A$3:$A$13)*($B$3:$B$13 <>""),ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,""),COLUMN()-4)), "")
Did you find the answer to this question?
Hi Svetlana,
It's a great post; well explained. thanks.
A question for you:
Is it possible to use the INDEX formula for an Excel table? I converted your Excel data to a table (Table1) and used the following formula but it does not work:
=INDEX(Table1[[#Headers],[Product]], SMALL(IF(D$2=Table1[[#Headers],[Seller]],ROW(Table1[[#Headers],[Seller]])-MIN(ROW(Table1[[#Headers],[Seller]]))+1,""), ROW()-2))
However, VLOOKUP works but as expected it only gives the first occurrence of the matching field:
=VLOOKUP(D$2, Table1,2,FALSE)
Any ideas? Many thanks.
Abbas
(E5:E19) Contians Text data and (F5:F19) contians Numric data.
I need total weight of each article. Data is as under:
Column E Column F
Beef Mince 2
Beef Boneless 10
Mutton Bone-In 2
Beef Boneless 4
Beef Boneless 5
Mutton Bone-In 3
Beef Bone-In 1
Mutton Mince 3
Mutton Bone-In 4
Mutton Mince 5
Mutton Bone-In 2
Mutton Mince 1
Sheep Carcass 6
Beef Mince 7
Mutton Carcass 2
Data Range (E5:E19) and (F5:F19)
Kindly provide the solution.
Awesome Its realy work , I have tried
hello svetlana,
would you help me, if i use this formula
=IFERROR(INDEX($B$3:$B$13,SMALL(IF(F$2=$A$3:$A$13,ROW($A$3:$A$13)- MIN(ROW($A$3:$A$13))+1,""), ROW()-2)),"")
with indirect function, where can i put indirect function? thank you for your guide.
Hi,
I have yet another problem.
So I have two excel workbooks that I want to connect.
I want for workbook B to show data from workbook A, based on a criteria of two same values in both workbooks.
eg. I write a code given to the pupil into A3 to A92 in book B. The formula should look for that code in cell range G3:G92 in book A and give me the same code form book A in cell B3 in book B.
I would then copy the formula trough the row because need name, surname, phone etc. and I don't want to input same data twice in cca 100 rows.
I have tryed IF function:
=IF(A3:A92='[PREDUPIS_prazni_prvaša lista A i B-auto.xlsm]Popis - svi'!$G$3:$G$92;'[PREDUPIS_prazni_prvaša lista A i B-auto.xlsm]Popis - svi'!$G$3:$G$92;"")
but the formula does't do what I need.
Can you help?
BOOK A
B C D E F G
PREZIME IME Ime oca Ime majke DATUM ROĐENJA ŠIFRA=CODE
Lopov Luka Ive Mari 13/12/2007 LLIM1
Marica Marko Luka Marija 15/4/2010 MMLM1
Kereta Rogic Ema - Maja 9/11/2013 KE-M9
BOOK B
A B C D E F G H
Code code Prezime Ime Spol Ime oca Ime majke Datum rođ.
Hi,
I need a formula to look up column A, find all the reference values in Column B and Column C, Compare the results and if a certain combination exists then return true else false.
A B C D
CGP1087 12/5/1998 0 1.2
CGP1087 12/5/1998 1.3 1.3
CGP1087 12/5/1998 0 1.4
CGP1087 12/6/1998 1.1 0
CGP1058 12/5/1998 1.4 0
Look up for CGP1087 check if there is ever a combination of 1.3 and 1.4 in Column C, Column D or Column C and D together
token no name o/date
1256 kan singh 15/10/2017 Tokan no 1256
1257 mukesh 14/10/2017 1st o date ?
1258 ram 17/10/2017 2nd o date ?
1256 kan singh 17/10/2017
Thank you so much for this explanation--I'm not an Excel expert and this looked really intimidating, but I was able to make it work. I've bookmarked your site for future reference!
It was really helpful. Thank you.
Table 1 Table 2
AMT result AMT Unique Value2
30 A 30 A
40 D 30 B
30 B 30 C
30 C 40 D
50 F 40 E
50 - 50 F
I need to pick 2nd column of 'Table 2' by comparing AMT column but the formula should pick only one unique value 1 time from 'Table 2'
These formulas have worked for me but now I have a situation where I would just like a partial lookup match. Trouble is these formulas won't allow for wild cards like vlookup does. Is there a workaround for that?
Example:
=IF(ISERROR(INDEX($AQ$1:$AV$500,SMALL(IF($AQ$1:$AQ$500="REGULAR PAY 32.0000 ",ROW($AQ$1:$AQ$500)),ROW(1:1)),3)),"",INDEX($AQ$1:$AV$500,SMALL(IF($AQ$1:$AQ$500="REGULAR PAY 32.0000 ",ROW($AQ$1:$AQ$500)),ROW(1:1)),3))
With this my "REGULAR PAY 32.0000 " must be an exact match when I'd just like to look for "REGULAR PAY*".
I can do it with vlookup of course:
=VLOOKUP("REGULAR PAY*",AQ1:AV500,3,FALSE)
But of course it only returns the first value it finds.
Hi
Thanks very much for your explanations and tips, I've found them really useful. I used the Vlookup multiple conditions to return matches in a row and it works perfectly.
As per your sample workbook, would there be a way of filtering out any duplicate values, for e.g. your return matches in a row for Seller Adam contains the Products 'Lemons' in both columns K and N - any way to stop that and just return one value of 'Lemons'?
Many thanks
Hello,
My vlookup is returning values that are not in the table array.
Any suggestions.
this really helped me alot, thank you!!
This is great. Thanks a lot
Hi,
I tried copy pasting the formula. Modified it for my specific columns and made sure to hit ctrl+shit+enter. The formula however only populates the first value (much like a regular VLOOKUP function. Can anyone please help?
Thanks!
Riyaz
The worksheet table have columns (Cell B:Q)that show "Supplier Names" and the rows (15 to 33)have "Item01; Item02;and so on"
What is required in Cell B2 if the "Item02" is typed-in, I want to find out the suppliers with the lowest 3 sales.
Thank you, Look forward to your earliest response
HI
I need help....
I need an IF and V lookup formula but with multi-pal ranges.
So IF A1 = DEL then look at Cell C1 to get the size and look at Cell D1 to get the area. Look up the area price according to the size of the container. Does that make any sense?