## Lookup multiple values in a range and return multiple corresponding values

*Article updated on February 20, 2018*

In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula to lookup values in range using **one criterion** and return (if possible) multiple adjacent values.

In this blog post I´ll show you how to lookup values in range using **two or more criteria** and return (if possible) multiple adjacent values using an array formula.

If you can rearrange your data, read this post:

Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

You can use a lot easier array formula if you rearrange your data.

**Array formula in B22:**

**1**) + CTRL + SHIFT + ENTER

copied down as far as needed.

Read an explanation of this array formula here:

Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel

**Named ranges**

tbl (B3:G8)

search_tbl (B18:B19)

What is named ranges?

### How to implement array formula to your workbook

Change the named ranges. Increase search_col range to add more lookup values. The bolded value is the specified (adjacent) column number you want extract from a matching value.

### Download excel sample file for this tutorial.

Filter-cells-using-two or more-criteria-in-excel.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

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

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

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

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

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

**COLUMN(**reference**)
**returns the column number of a reference

Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]### 11 Responses to “Lookup multiple values in a range and return multiple corresponding values”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA 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

Thanks for all the examples you've provided. I've been looking for a lookup like this one for some time and this does almost what I want. Unfortunately, I can't make enough sense of what happens internally to adapt it exactly - it feels like trying to visualise 4 dimensions in a 3 dimensional world. Ideally, I would like the results to appear in column-row order rather than the row-column order that appears here but can't work out how to do this let alone whether it is even possible. I also can't work out why various bits related to the column number in the index function are multiplied or divided by 16384. Can anyone point me in the right direction on this?

I'd also quite like to find a way of sorting the results by search term so that, for example, all the results for the first search term (here, Maine) are listed, followed by all the results for the second search term (here South Carolina), and so on. Again, is this possible?

I also found that changing the 1 prior to the /16384 had the same effect as changing the 1 after the *16384 but am not sure whether this was simply fortuitous or not.

Regards,

Ian

Ian,

Question 1:

I would like the results to appear in column-row order rather than the row-column order that appears here but can't work out how to do this let alone whether it is even possible.Answer 1: The results are in a column in the above example. I guess you want them in a row?

Copy this formula into a cell:

=INDEX(tbl, SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), COLUMN(A1)), (SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), COLUMN(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), COLUMN(A1)))*16384+1) + CTRL + SHIFT + ENTER. Copy the cell and paste it to the right as far as needed.

Sorry, I wasn't clear, although this helps a lot with a couple of other things I'm looking at.

In the example you used, the formula effectively searches each row in the table in turn so that the first result is from the third pair of columns (2009 March), the second result from the first pair of columns (2009 January), the third result from the second pair of columns (2009 February). What this does is return all relevant results from the first row, then all relevant resutls from the second row, and so on.

What I'm trying to do is to return all relevant result from the first pair of columns, then all relevant results from the second pair of columns, then all relevant results from the third pair of columns, and so on (there could be 4 or 5 pairs of columns, possibly more), where for each pair of columns the results are returned reading down the columns. This would produce a set of results for your example which looks like:

Johnson

Miller

Taylor

Lopez

and so on and if South Dakota was added to the list of criteria, the results would look like:

Johnson

Williams

Miller

Wilson

Taylor

Lopez

I hope that makes more sense. But is it possible to do it like that? Thanks for your help so far.

Regards,

Ian

Ian,

Try this:

=INDEX(tbl, (SMALL(IF(COUNTIF(search_col, tbl)>0, (COLUMN(tbl)-MIN(COLUMN(tbl))+1)+(ROW(tbl)-MIN(ROW(tbl))+1)/65536), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, COLUMN(tbl)-MIN(COLUMN(tbl))+1), ROW(A1)))*65536, SMALL(IF(COUNTIF(search_col, tbl)>0, COLUMN(tbl)-MIN(COLUMN(tbl))+1), ROW(A1))+1) + CTRL + SHIFT + ENTER. Copy cell and paste it down as far as needed.

Thanks for commenting!

Thank you. That is exactly what I was looking for. And, even better, I think I'm beginning to understand how these things work, as well.

Thanks again.

Ian

Hello, not sure if this post is followed any more, but I am wondering if it is possible to complete the search as described above and concatenate all results into one cell? (I realize depending on the search, we could be looking at hitting cell text limits)

Thanks for any help you can provide!

Nevermind! I searched some of the other posts on this site and found that a UDF would do the trick.

THANKS!

Hello, this seems to be fairly similar to what I am trying to do but cannot seem to figure it out. I have 3 columns essentially and am trying to lookup a value from one column and return another column's entry. The problem is that my one column contains addresses and it does not match exactly because it may be missing an "Ave" or somthing of that nature. Here is what I am working with:

A1 A2 A3

5555 E Oakton St 65899 5555 E Oakton

726 S Oakley Blvd 55321 726 S Oakley

Here's the function =VLOOKUP(A3, A1:A2, 2, False)

It will not return A2 because A3 is missing Blvd, but if I use True, it will return a value from the previous row (65899).

I was thinking if there is any way of splitting up A3 into 3 different columns and using an AND statement in the vlookup to try and find just the first 3 entries (726, S, and Oakley) but that did not seem to work either. Any help would be greatly appreciated.

I'm trying to find a formula which will provide a known part number if certain known criteria is met. Here is an example:

Column A Column B Column C Column D

Part No. Width Length Height

ABCD 1 2 3

EFGH 4 5 3

HIJK 3 6 7

All above information is in a table and is known. If I insert a 4 5 and 3, it provides Part No EFGH.

how do I remove #NUM! and need empty cell Please help

=INDEX(list, SMALL(IF(SMALL(IF(COUNTIF($A$53:A58, list)+ISBLANK(list)=0, COUNTIF(list, "<"&list)+1, ""), 1)=IF(ISBLANK(list), "", COUNTIF(list, "0, "", COUNTIF(list, "<"&list)+1)), INDEX(IF(ISBLANK(list), "", COUNTIF(list, "<"&list)+1), SMALL(IF(SMALL(IF(COUNTIF($A$53:A58, list)+ISBLANK(list)=0, COUNTIF(list, "<"&list)+1, ""), 1)=IF(ISBLANK(list), "", COUNTIF(list, "<"&list)+1), ROW(list)-MIN(ROW(list))+1), 1), , 1), 0), 1)

there are 128 original values (from 10 to 12000) with associated ranges (variable, to a maximum of 1% either side of each original value). We need to automatically identify whole number multiples or divisions of any one original value that are within min/max tolerance of any of the set of 128 original values, see by what whole multiple or division they relate, and be able to ignore the ones that don't qualify. This thread is the closest I have come to finding the answer. We are amateurs at math and excel; we would like the answer to help compose music in a microtonal scale. please can somebody help? thank you, best wishes