Lookup with multiple criteria and return multiple search results
Question:
How do I search a list containing First name column and a last name column? I want to search both columns at the same time with two different criteria (one criteria for each column) and then display multiple search results.
Answer:
I created two search fields. First and last name in E2 and G2. The search results are presented in column D and E. See picture below.
The array formula in cell D6:
Recommended article
Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]
Search each column for a string each and return multiple records – OR logic
How to create an array formula
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.
Array formulas allows you to do advanced calculations not possible with regular formulas.
Copy cell D6 and paste it down as far as needed.
The array formula in cell E6:
Recommended reading
Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Search for a text string in a data set and return multiple records
Explaining the array formula in cell D6
Step 1 - Find rows where all criteria match
=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))
Search() function returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive).
SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16) returns this array:
(#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 24, 6, #VALUE, #VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE)
(SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16)) contains only absolute references.
How to use the SEARCH function
Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.
How to use the SEARCH function
Step 2 - Identify numbers in array
=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))
ISNUMBER(value) checks whether a value is a number and returns TRUE or FALSE
ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16)))
becomes
ISNUMBER(#VALUE, #VALUE, #VALUE, #VALUE, #VALUE, 24, 6, #VALUE, #VALUE,#VALUE, #VALUE, #VALUE, #VALUE, #VALUE))
and returns this array:
(FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)
Step 3 - Convert boolean values into row numbers
=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))
IF() checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, "")
becomes
IF( (FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE) , ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, "")
becomes
IF( (FALSE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE) , (1,2,3,4,5,6,7,8,9, 10,11, 12,13, 14) , "")
and returns this array: ("","","","","", 6, 7, "", "", "", "", "", "", "")
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Step 4 - Return k-th samllest value
=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))
SMALL(array,k) returns the k-th smallest row number in this data set.
SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16)*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1))
becomes
SMALL(("","","","","", 6, 7, "", "", "", "", "", "", ""), ROW(A1))
becomes
SMALL(("","","","","", 6, 7, "", "", "", "", "", "", ""), 1)
and returns 6
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 5 - Return a value of the cell at the intersection of a particular row and column
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
=INDEX($B$2:$B$16, SMALL(IF(ISNUMBER((SEARCH($E$2, $B$2:$B$16))*(SEARCH($G$2, $A$2:$A$16))), ROW($A$2:$A$16)-MIN(ROW($A$2:$A$16))+1, ""), ROW(A1)))
becomes
=INDEX($B$2:$B$16,6) and returns Fernando in cell D6.
Gets a value in a specific cell range based on a row and column number.
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Search for a text string and return multiple adjacent values
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Search and display all cells that contain multiple search strings
Jerome asks, in this blog post Search for multiple text strings in multiple cells in excel : If the list […]
Search for multiple text strings in column – AND logic
Question: How do I search a list for two text strings and return a list with where both strings occurs? […]
Search columns for a string and return records – AND logic
Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]
Return multiple matches with wildcard vlookup
Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP: "The data in column A contains a series […]
Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]
Multiple wildcard lookups and include or exclude criteria
Dave asks: Hi Oscar, This is great and compliments your Multiple Criteria and Multiple Matches series of posts very well. […]
Row is returned if both search strings are found on the same row
Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]
Find the most urgent work orders
This animated picture shows you the most urgent work orders for a location. Type a location in cell F3 and […]
47 Responses to “Lookup with multiple criteria and return multiple search results”
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.
How would I go about looking up data in an cross ref table.
I have the header row (i.e. 24) value and the column (mm) value and want to return the x/y value. i.e I have 25/X and 9/Y item and want 1.8 to be returned.
(mm) 22 23 24 25 26 27 28 29
8 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
9 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
10 1.3 1.8 1.8 1.8 1.8 1.8 2.3 2.3
11 2.2 2.8 2.8 2.8 2.8 2.8 3.3 3.3
thanks
See this post: https://www.get-digital-help.com/2009/06/24/looking-up-data-in-a-cross-reference-table-in-excel/
Could this approach be expanded for more than 2 columns?
And if the values were numbers is there a way to display the values within a range between the values in 2 cells?
I don´t understand.
You want to search a range bigger than 2 columns?
If two numbers (or numbers between) match on any column on the same row, it is a match?
My comment is two seperate issues.
The first is if I can expand this equation set into more than two colums of data, say if I had a first, middle and last name column could I only display the values in which all three cases are true?
The second question is whether the equation can be adjusted to search and display a range of values, if the values were numerical, say 1 - 40 could I set a range of 20 - 25 (either in one or 2 cells) and get 6 values (inclusive)?
Apologies if it is hard to understand
Gavin, your first question.
See this blog post: https://www.get-digital-help.com/2009/08/13/lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-3/
Gavin, your second question:
https://www.get-digital-help.com/2009/08/16/search-and-display-a-range-of-values-in-excel/
I have a column "A" with a last name.. I have another columb with a date in it "E"... I need to be able to list the names from columb A on a second sheet whos columb e date has past....
thanks
todd B, see this post: https://www.get-digital-help.com/2009/08/26/list-names-whos-date-has-past-in-excel/
Well, I have been looking long for this wonderful idea, and previously I had to result in two step utilizing rank command etc etc..
Thank you for the work, and thenk the NET in general!
Back to what I had in mind, I wanted specificaly do this
assume a column b from 2 to 100, where the word "check" occures, there is dropdown list resticted option, among "cash" and "other".
next to it, there is a column c from 2 to 100 also with dates, Now all I want is to incorporate in that wonderful formula of yours for simple sorting, the condition to have value "check" on col b every time I include a date for to sort..and of course to include the option to have the cells blank when there is no value...after sorting..?
Thank you for your time, it has been so much fun doing stuff in excel!!!
am I asking for too much
also, while this formula modified for one constant filtration, either from small or family names lets say the "o" s, it does not alphabetize the results.
so what I am looking for is your classic short formula, single column, the one you have with pairs of letters...ee, wr, etc, but with a filter to screen the results too...
Is it possible to lets say pick the cell that is next to it horizontaly, and the cell next to it too, like the vlookup can do?
Ultimately I want to filtrate and indexize the dates, but also bring in the rest of the row, the name the ammount etc, associated with the date...
thank you once again
Can you upload an excel example file?
https://www.get-digital-help.com/contact/
Great solution. I was wondering, though, how you seemed to "group" the cells D6:D20 and E6:E20. I downloaded the Excel example and played around and noticed that changing something in D6 automatically changed every cell from D6 to D20. That's cool!
Feel free to e-mail me if you prefer. Thanks!
Art,
I use search() to find rows that match. INDEX() returns a value in a given range.
The formula in D6:D20:
= INDEX($B$2:$B$16, ...
The formula in E6:E20:
= INDEX($A$2:$A$16, ...
Thanks for commenting!
/Oscar
Is is possible to use Index/Match using multiple criteria as you listed above except display all of the search results in a list (data validation) so that I can select one of the results from the list?
This is amazing! I've been looking for a way to filter multiple outputs using excel code. Now to dissect the formula...
KW,
Thank you for your comment!
Hi, I need help with the multiple criteria as well. But it also involved in min/max value.
The Data as per shown below:
Value in USD
Weight in Kg Zone Zone Zone Zone Zone
Min Max A B C D E
2.50 25.0 7.95 8.79 9.93 10.50 8.23
25.50 50.0 7.66 8.50 9.64 10.21 7.94
50.50 100.0 6.24 7.08 8.22 8.79 6.52
100.50 500.0 5.12 5.96 7.10 7.67 5.40
I would like to lookup for the Price (the value that I need it to show up automatically) based on the specified Zone and the weight.
Example:
Zone Weight Price
E 24.00 8.23
A 30.00 7.66
C 50.50 8.22
Thanks in advance for your great help.
Liz,
Dear Oscar,
Thank you very much.
Deeply sorry for my really rate reply :")
Hi please help on the multiple criteria. I need as below:
Criteria:
1. Lookup the material code specified on column A to column C
2. Column D must not be empty
Once the above 2 conditions are satisfied, the result must display the latest date from column E
Reference:
A B C D E
1 Material code Date Material code Invoice ref Date
2 03405140F0 03405140F0 1800102639 7/2/2012
3 03405341F0 03405140F0 1800102639 7/2/2012
4 03406210F0 03405140F0 1800103010 7/6/2012
5 03405341F0 1800103010 7/2/2012
6 03405341F0 7/28/2012
7 03405341F0 1800101179 7/24/2012
8 03405341F0 8/24/2012
9 03405140F0 1800099691 7/26/2012
10 03405140F0 8/26/2012
11 03406210F0 1800099691 8/2/2012
Result to display must be:
B2 - 7/26/2012
B3 - 7/24/2012
B4 - 8/2/2012
Hi please help on the multiple criteria. Below are the criterias:
1. Lookup the material code specified on column A to column C
2. Column D must not be empty
Once the above 2 conditions are satisfied, the result must display the latest date from column E
Reference:
Column A
1 Material code
2 03405140F0
3 03405341F0
4 03406210F0
Column C
1 Material code
2 03405140F0
3 03405140F0
4 03405140F0
5 03405341F0
6 03405341F0
7 03405341F0
8 03405341F0
9 03405140F0
10 03405140F0
11 03406210F0
Column D
1 Invoice reference
2 1800102639
3 1800102639
4 1800103010
5 1800103010
6
7 1800101179
8
9 1800099691
10
11 1800099691
Column E
1 Date
2 7/2/2012
3 7/2/2012
4 7/6/2012
5 7/2/2012
6 7/28/2012
7 7/24/2012
8 8/24/2012
9 7/26/2012
10 8/26/2012
11 8/2/2012
Result to display in column B must be:
B2 - 7/26/2012 (from E9)
B3 - 7/24/2012 (from E8)
B4 - 8/2/2012 (from E11)
Thanks in advance
Michael,
Array formula in cell B2:
=MAX((A2=$D$2:$D$11)*$G$2:$G$11*($E$2:$E$11<>""))
How to create an array formula
1. Select cell B2
2. Paste formula in formula bar
3. Press and hold Ctrl + Shift
4. Press Enter
Copy cell B2 and paste to cell B3 and B4.
I have been trying to use this article to lookup data against 2 sets of criteria and return multiple results. However my results to be returned are not the same as my search cells i.e. I want to search on multiple criteria and if found bring back a different column in the row, but there will be multiple results that I need to display.
Can anyone help? It would be much appreciated
Thanks
rachel,
Array formula in cell F8:
=INDEX($D$3:$D$17, SMALL(IF(ISNUMBER((SEARCH($G$3, $C$3:$C$17))*(SEARCH($I$3, $B$3:$B$17))), ROW($B$3:$B$17)-MIN(ROW($B$3:$B$17))+1, ""), ROW(A1)))
I was using your code to track who applied for vaction days and take from a list and put in calender format. It worked great until I went to double digit numbers. It is picking up the 1 from 12 and putting the persons name on the 1, 2, and 12 of the month. Could you please help me?
Thanks
Dee
Deanna,
I don´t know how your spreadsheet looks like but I think you can make use of this formula:
=INDEX(cell_ref,MATCH(lookup_value,cell_ref,0))
Thanks for all your help you are great! It is hard to explain and you seem to get it with ease. :)
HAve a great day you are the best!!
Hello,
I have a question. I've used search, rank and vlookup and order to have a cell that I can used like a search engine trough my spreadsheet. The thing is that I can use it only to show one column with results of the searching process and I can't show more then one results. For example: a table from H5: k1000, ( in the column K I have some numbers) and in A B colmuns I've made the searching cell(engine) and the results are dependent of what I choose ( in one cell with data validation). The big problem is the results showed are not correlated with K column.
sorry... can somebody help me, please??
thank you
Andra,
read this:
Return multiple records
Hi! Thanks for the detailed nature of your explanations. I have found them so educational.
Do you know of a way to include certain criteria while excluding others? In your example above, could you have your formula search for the criteria given in $G$3 and $I$3, while excluding (hypothetical) criteria in $K$3 and $M$3? For example, say you wanted to include last names with the letter "o", and first names with the letter "e", while excluding any last names containing the letter "v" and excluding last names with the letter "q"? (of course you don't have any last names with the letter "q" in it, but just for the purpose of illustrating how to exclude multiple criteria...)
Does that makes sense?
Eager to hear any insights you might have. Thanks!
Carma,
Array formula in cell E10:
Array formula in cell F10:
Download excel file
multiple-criteria-lookup-with-multiple-results.xlsx
Hi, sorry for digging up an old post but I'd really appreciate your help on this.
I'm trying to search a range of cells for a range of values. Say I wanna search B1:B50 for values in D1:D20(in my case there's multiple matches, i.e. D3 appears multiple times in B1:B50).
For every match, I would like to input a corresponding value from column X to column C. (Back to my example: Say D3 matches B2,B3,B5,B7; so i need to map X3 to C2,C3,C5,C7).
Is this doable in cell/array formula? Or must I resort to macro? Anyways I hope I'm making sense here. Thanks!!
Ethan,
I think this post answers your question:
https://www.get-digital-help.com/2009/07/15/search-for-multiple-text-strings-in-multiple-cells-in-excel-part-2/
Hello again, I managed to solve it with vlookup. But thanks so much for your help =)
Hi, is there any way I can use vlookup to automatically post figure from journal voucher to general ledger? pls help me
Pls I need your help on how to automatically post figure from journal to general ledger using excel
Mufliu,
Can you explain in greater detail?
Hi, gud evening! I am having a trouble using excel formulas. I am doing accounting in excel and i don't know the formula to use.I want those journal entries to be posted automatically in the ledger.thank u
Dear Oscar. I'm sorry to reply on such old post but I was studying this scenario and the formulas used on it and thought it's a great solution. I have almost the same need. The only difference is that the result should be a list with only one instance of each record. For instance: I downloaded the sheet and inserted a row with a new entry for Fernando Gonzales and as I did it a new instance for the same name has appeared on the list. I needed it to appear there only once for each name. Just to explain my real scenario: I have 3 columns. DESCRIPTION - CATEGORY - VALUE. On the DESCRIPTION Column there can be a lot of repeated values ( Like ROBERT, JENNIFER, ROBERT, and so on ). On the category there can be a lot of values ( Like SALES, TRADE, RETURNS, MAINTENANCE, and so on ). The column Value contains the value for the operation. I need to have a separate sheet for each category and for each category I need to have the DESCRIPTION and TOTAL VALUE ( which is the sum of all the values for that description on that category ). For instance: On the main sheet I have 4 entries with the same name ( let's say JOHN ) on the DESCRIPTION Column. The category for these 4 entries is SALES and each VALUE for them is 100.00. So, I need to have a Sheet Called "SALES" ( which will be created previously ) and this sheet needs to have the following result: Only one entry with the DESCRIPTION "JOHN" and the TOTAL VALUE as "400.00" ( the sum of all the other 4 entries on the main sheet ). I wanted to do this without Pivot Tables and VBA. Is it possible? Thank you!
Andre,
Array formula in cell A10:
=INDEX($A$2:$A$7,MATCH(0,COUNTIF($A$9:A9,$A$2:$A$7),0))
Read more:
How to extract a unique distinct list from a column
Formula in cell B10:
=SUMIF($A$2:$A$7,A10,$C$2:$C$7)
Download excel file
Andre.xlsx
Oscar. Thank you for your help! I will apply this solution to my scenario! You are great! Cheers!
Hello
Hi Oscar,
I've two different sheets in which only one thing is unique i.e Name of the product(Title). So basically i need to find out the sku code which is linked with the product. I need that SKU code in single line break with comma. Please suggest me some formula
i have read your read your site and you come closer to what i am trying to do than anyone on the web- hopefully you will help me i work for a lawyer that wants all client payments on a spreadsheet - she wants a search box to pull up all rows that have the last name and display the results between the search box and the starting row of the spreadsheet - i cant get it to return the results at all please help see my example
{ name here } button ( optional)
want the results to populate here
''''''''''''''
''''''''''''''' 12 lines (no one ever pays more than once a month )
''''''''''''''
''''''''''''
A B C D E
1 date first name last name amount paid payment type
2 1/1/16 bob green 100 cc
3 1/3/17 andy red 50 cash
4 1/5/17 steve white 100 cash
5 1/8/16 tom white 100 cash
6 1/12/17 bob green 3000.00 cc
so when i search for green i will get all the greens that have paid through out the entire worksheet which has 719 rows and 5 columns i want the result to show in the area above the list and below the search box
Hi
my data single cell 12 values like this in below data i want to find out each cell min/max and average for this please give me formula for this thanks
3.78,3.79,3.77,3.78,3.78,3.77,3.78,3.79,3.79,3.79,3.79,3.78
3.51,3.51,3.51,3.50,3.51,3.51,3.52,3.51,3.51,3.51,3.51,3.51
3.61,3.61,3.61,3.61,3.61,3.61,3.60,3.61,3.61,3.59,3.61,3.61