## Lookup values in a range based on criteria and return multiple values

*Article last updated on November 21, 2017*

*I found a question in the comments section. You can find the question in this post: *Lookup values in a range using two or more criteria and return multiple matches in excel

**Question:** Unfortunately, I can't make enough sense of what happens internally to adapt it exactly.

*Here is what the post is about:*

How to lookup values in range using **two or more criteria** and return (if possible) multiple adjacent values using an array formula.

Array formula in B22:

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

copied down as far as needed.

**Named ranges**

tbl (B3:G8)

search_tbl (B18:B19)

What is named ranges?

**Question:** Unfortunately, I can't make enough sense of what happens internally to adapt it exactly.

**Answer: **Here is an explanation of what happens internally.

To filter specific values in a range I use INDEX().

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

Each cell in a given range has a row and column number. See row 2 and column K in the picture below.

To be able to extract the matching values we need to create an array containing row and column numbers.

Upper left cell in range: Row 1 and column 1 = 1,1

Next cell to the right: Row 1 and column 2 = 1,2

Next cell to the right: Cell E1: Row 1 and column 3 = 1,3

But what about row 10 and column 10? 10,10 but that equals 10,1 so excel can't distinguish between row 10, column 10 and row 10, column 1. How do we solve this?

Upper left cell in range: Row 1 and column 1 = 1 + 1/16384

Next cell to the right:Row 1 and column 2 = 1 + 2/16384

Next cell to the right:Row 1 and column 3 = 1 + 3/16384

Maximum columns in excel 2007 is 16384. That is why I use 16384.

Now each cell has a unique number. We can convert this number back to rows and columns. But how do we know what values to convert?

**Formula:** COUNTIF(search_col, tbl)

returns an array where 1 = TRUE and 0 = False. Compare pictures below.

Now we can create an array formula to extract rows:

IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1)

Sort the array:

SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1))

**{ 1, 2, 3, 4}**

**INDEX(**array,row_num,[column_num]**)**

**INDEX( tbl**,SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)),[column_num]

**)**

Next we create an array formula to extract column numbers (sorted by rows)**.**

SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))

**{ 1.00029705323194, 2.00005941064639, 3.00017823193916, 4.00029705323194}**

Remove row numbers from array:

SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1))

**{ 0.00029705323194, 0.00005941064639, 0.00017823193916, 0.00029705323194}**

Multiply by 16384 to create column array:

(SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)))*16384

**{5, 1, 3, 5}**

Finally adding 1 to each number in the array. We want to return the value to the right of each match. South Carolina => Taylor and so on.

(SMALL(IF(COUNTIF(search_col, tbl)>0, ((ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/16384)*1), ROW(A1))-SMALL(IF(COUNTIF(search_col, tbl)>0, ROW(tbl)-MIN(ROW(tbl))+1), ROW(A1)))*16384+**1**

**{6, 2, 4, 6}**

**INDEX(**array,row_num,[column_num]**)**

**INDEX(tbl**,{ 1, 2, 3, 4},{6, 2, 4, 6}**) = **{Taylor, Johnson, Miller, Lopez}

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.

Vlookup across multiple sheets

This blog post describes how to search two tables on two sheets and return multiple results. Sheet1 contains table1 and […]

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

In this post we are going to extract multiple text values. We are looking for names and the criteria are […]

Lookup and return multiple values on the same row from a range excluding blanks

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted […]

Use a drop down list to search and return multiple values

Ainslie asks: I have multiple worksheets in an excel book. I have a drop down menu on the worksheet entitles […]

Lookup multiple values in a range and return multiple corresponding values

In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula […]

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 […]

Vlookup with multiple matches returns a different value

Linda asks in this post: How to return multiple values using vlookup in excel I tried using the formula above […]

### 7 Responses to “Lookup values in a range based on criteria and return multiple values”

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hi,

This example has been explained in detail, its really a help. However, i do have one question if u can provide the solution.

There are three invoice no in A6,B6 & C6, and Product Price in J8 in file 1. And in file 2 i have Product codes. i want those product codes from file 2 to file 1 in column B8 matching the invoice no and the price in J8 in file 1.There are plenty of products so if the product price is not in invoice no column A6 then it maybe in B6 or it will be in C6.

Thanks in advance.

Ritesh

Ritesh,

download excel *.xlsx file

Ritesh.xlsx

I FAILED to try many time using the formula to match a 3rd tablet that contain in table 1 CODE,ITEM,PRICE and Table 2 CODE,QUANTITY,TOTAL PRICE, where the 3 table is CODE,ITEM,QTY,T_PRICE. why?

GD,

Hard to say without seeing your formula, please upload a workbook without sensitive information:

Upload

how do remove #NUM! 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)

I have 2cells with drop down lists containing data from two columns. I want to extract data from the 3rd coloumn if the cells matches the data in the same row of the table.

eg. A1 and B1 are drop down lists D,E and F are columns of a table. If A1=Dn and B1=En, Then C1=Fn.

Kindly help me on this

Arunlal

These two posts demonstrates how to match multiple values in a column each:

https://www.get-digital-help.com/2012/05/22/lookup-multiple-values-in-different-columns-and-return-a-single-value/

https://www.get-digital-help.com/2010/02/11/match-two-criteria-and-return-multiple-rows-in-excel/