## Lookup with multiple criteria and display multiple search results using excel formula

** 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:**

### 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.

Copy cell D6 and paste it down as far as needed.

**The array formula in cell E6:**

**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.

**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, "", "", "", "", "", "", "")

**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

**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.

**Download excel sample file for this tutorial.**

multiple-criteria-lookup-with-multiple-results.xls

(Excel 97-2003 Workbook *.xls)

**Functions used in this article**

**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

**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

**ROW(**reference**)** returns the rownumber of a reference

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ISNUMBER(**value)

Checks whether a value is a number and returns TRUE or FALSE

**SUM(**number1,[number2],**)
**Adds all the numbers in a range of cells

**SEARCH()** Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

*This blog article is one out of five articles on the same subject.*

**Search for a cell value in an excel table****Search for a text string in an excel table****Lookup with multiple criteria and display multiple search results using excel formula****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**

Read more related articles in the archive.

### 46 Responses to “Lookup with multiple criteria and display multiple search results using excel formula”

### Leave a Reply

**How to add vba code to your comment:**

[vb 1="vbnet" language=","]

your code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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: http://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: http://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:

http://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: http://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?

http://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 filemultiple-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:

http://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