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 6. Total comments: 342
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?
Hi svetlana,
Appreciate if you can help me in this situation for my survey data. I am doing this to calculate for Fuzzy Delphi method
I have:
survey data ---> (A)
Likert scale 1-7 ---> (B)
Decimal point scale in 3 columns ---> (C)
Empty cell putting (C) values after comparing (A) and (B)---> (D)
Example as below:
(C) (B)
------------------------------------------
decimal point scale likert scale
0.0 0.0 0.1 7
0.0 0.1 0.1 6
------------------------------------------
I have a list of survey data (A) and I want to compare with likert scale (B), if (A) is equal or more than (B), then I want to take all values in (C) and copy it in (D)
What function should be suitable to get the return values?
Thank you
(C)
decimal point scale
0.0 0.0 0.1
0.0 0.1 0.1
(B)
likert scale
7
6
Светлана, спасибо вам огромное за ваш форум. Начал составку очень трудного Excel spreadsheet сегодня, по знанию начиная почти с нуля по формулам. Последняя черта документа оставалась включить одну из этих формул упомянутых здесь. Туго было понимать но в конце концов понял где ошибка была, поправил и всё заработало! Как приятно что есть такие умные люди которые напишут как что делать ^^
Спасибо на добром слове, Егор! Приятно знать, что соотечественники нас тоже читают :)
I have a working formula to return multiple values from one Worksheet: "15".
Formula =IFERROR(INDEX('15'!$A$7:$A$41,SMALL(IF($I$1='15'!$G$7:$G$41,ROW('15'!$G$7:$G$41)-MIN(ROW('15'!G$7:$G$41))+1,""),ROW()-2)),"")
I would like the formula above to also go through each Worksheet and return multiple values.
I have created a working table name "Sheet_Name" that will dynamically list Worksheets 1 through 100 starting with Sheet 6, currently I have Worksheets: A, B, C, D, E, and 1 - 15 (data to return).
Sheet_Names Formula =OFFSET(SheetNames!$C$7,,,COUNT(SheetNames!$C7:$C106),1)
Well i have problem in using vlookup in pm mfsw worksheet
Hi,
I'm just wondering if Formula 3. Vlookup multiple matches based on multiple conditions can be used to retrieve data from other workbooks?
I've tried however have not been successful.
Many thanks
hello
Svetlana Cheusheva
Sorry to say but your formula does't work. they have some problem while applying in practical.
so can you help me ?
Hello!
Please feel free to download our sample workbook with all the formulas described in this tutorial, and try them on your data.
How do I get the formula to search for match part of the string like APP instead of the full word APPLE?
am doing food gram as per menu for one person, when it is 100 person the gram reduce same items =IFERROR(VLOOKUP($B$6:$B$32,GRAM!$A$1:$B$300,2,FALSE)," 0 " )
this formula will look out value in gram A1:A300 FOR SAY CHICKEN for one person , I NEED IT LOOK FOR MULTIPLE1 cells where i stored for 1 pax, 100 pax 200pax etc. it has diferent grams. kindly advise.
I have applied the array formula and it worked perfectly. I would now like to apply another formula to my listed results (in one column) in order to extract my distinct texts to the column next to it. My data from this formula has multiple texts vertically and I would like each unique data only repeated once (next to the first) in the next column and blanks following until the next unique text.
Or just extracting the distinct values off of this formula.
Is this possible?
I look forward to your response. Thank you.
i want to apply the formula on a sheet.
like i have criteria range and infront of value if my first criteria value is 0 than for same criteria v lookup should take the value for next criteria.
Regards
Yogendra
i want to extract two unique different column of data in to one cell from another sheet after searching through an array
a b c(ans)
1 4 2
2 3 1
3 4 2
4 2 4
Awesome tutorial, thanks a lot!
Hello Svetlana,
The issue I experienced not pulling the data was related to #N/A (error) in couple of cells. Once I replaced these values all worked fine.
Sorry for the formatting above, but it only became so after I posted the message. There are currently 7 columns, one with the A/A numbers and another 6 with data. A,B,C,D,E and F are the head titles of the 6 columns with the data.
Hello Svetlana.
I have the following table. Please have a look at my question following the table.
A B C D E F
1 EXCB11 EXCB10 0.202 0.79 0.159 0.159
2 EXCB10 EXCB09 0.117 0.72 0.084 0.244
3 EXCB09 EXCB08 0.042 0.61 0.026 0.269
4 EXCB08 EXSTMH05 0.016 0.2 0.273
5 EXSTMH07 EXSTMH09 0.016 0.5
6 EXSTMH08 EXSTMH07 ? ?
7 NEWCB12 NEWSTMH04 0.152 0.76 0.115
8 NEWSTM EXSTMH05 0.178 0.76 0.250
9 NEWCB14 NEWSTMH15 0.093 0.83 0.078
10 NEWSTMH15 NEWSTMH13 0.000 0.92
11 NEWSTMH13 EXSTMH08 0.000 0.76
12 EXSTMH0 EXSTMH05 0.019 0.59 0.089
13 EXSTMH05 TMNT DEVICE 0.000 0.59
14 TMNT DEVICE EXMH01 0.000 0.87 0.000
- For every cell of column A, I am trying to find the cells in Column B that have the same value with it. For example in the given table for Cell A13 that has the value EXSTMH05, I would like to find cells B4, B8 and B12 that also have the value EXSTMH05.
- Then, for each one of the cells that I found I want to get the values from the respective cell of Column F and sum them up. So, in the given example I would like to get the values from F4, F8 and F12 and find their sum. The result I am seeking in this example 0.273+0.250+0.089.
Can you help me? I have tried using various formulas but to no result.
Hi Svetlana,
I wanted to ask you for your help with my table:
https://we.tl/mMztWNG1lY
What I would like to display in Sheet 2, is the quantity of those topics, which meet the chosen criteria and also display all corresponding texts, links and clicks, one below the other, taking in account the two criteria chosen at the top: "Category" and "Language".
What we also have to take in account is that the categories, texts, links and clicks can be found in several columns and not just in one.
I really hope that you can help me with this issue. :)
Best regards,
Sanjin
Svetlana,
Spasiba, for your quick response. Everything is working well even on large volume of data. When the data is aligned well n-1 type of configuration it works well.
As for the "relative reference" I meant relative position. I tried multiple examples, the all worked if properly setup.
Cheers,
Sam
Hello,
I'm hitting a unique error with this formula. I have the formula on Sheet2, and am trying to pull the information from Sheet1, A4, titled "Expirations". In the "IF" statement, if I put the reference item to match the item in the first row of the table on sheet 1 (89), it pulls the information from A4 over after I hit Enter. If I change it match the item in row 2 (229), it doesn't pull the associated info from A5. Also, if I hit ctrl+shft+enter to use this as an array formula, it doesn't pull any results.
My ultimate goal is to pull over all of the rows that have a negative number in Column E, so if you can offer any guidance towards that, that would be awesome. :) But in the meantime, helping me understand why the array formula isn't working between worksheets, and why it only pulls over the first item when used as a standard formula, would be awesome. The formula I'm using is:
=IFERROR(INDEX(Expirations!A$4:A$2864, SMALL(IF(89=Expirations!$E$4:$E$2864,ROW(Expirations!A$4:A$2864)-3,""), ROW()-3)),"")
Thank you!
Kevin
Svetlana.
Great work. The formula works well even in Two workbooks format one for data and one for matching. Also worked when replacing relative values with Named Ranges. I extended the example to about 6000 rows, still works. Note the slight change with named ranges "D$2=Sellers, Row(Seller)" Using Row(Fruits) does not return results.
=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,""), ROW()-2)),"")
=IFERROR(INDEX(Fruits, SMALL(IF(D$2=Sellers, ROW(Sellers)-2,""), ROW()-2)),"")
My trouble is, I have a very large data set (about 20K+ rows) and I get only the first value repeating for the entire array in my real work. I can't figure out where am I going wrong.
Hi Sam,
Sorry, I do not quite understand the part about relative references. In the original formula, all ranges are fixed (absolute references), and in named ranges the references are also absolute by default. I've done the same replacement in the sample workbook, and it works just fine. Please see N3:Q5 in sheet "Return matches in columns". The updated file can be downloaded here.
Hi Svetlana Cheusheva, in your first formula with index and Row-2 example above. I do scores for softball league and I need to look up the teams identifier (e.g. 10a) and return the date of the game. However, the column range I need to look in houses the matchups for that day's games. e.g. 10a v 10b in a cell. Can I use wildcards in the array formula (e.g. *10a*) as my look up value vs just 10a. I tried doing "*10a*", but got a Num error (took out the iferror to see why blank). Do I have to input "*"&D3(being 10a)&"*"=C2:c27, etc.??? Any guidance here is much appreciated!