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

*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 Responses to “Explaining a formula: Lookup values in a range using two or more criteria and return multiple matches in excel”

### Leave a Reply

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

<code>your formula</code>

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

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)