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

### Category: Vlookup

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

Comments(439) Filed in category: Excel, VLOOKUP and return multiple values

Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]

Comments(153) Filed in category: Excel, VLOOKUP and return multiple values

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

Comments(74) Filed in category: Excel, Vlookup

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

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

VLOOKUP and return multiple matches based on many criteria.

Comments(38) Filed in category: Excel, VLOOKUP and return multiple values

Lookup with criteria and return records.

Comments(35) Filed in category: Excel, VLOOKUP and return multiple values

Table of contents Lookup multiple values in different columns and return a single value Lookup multiple values in different columns […]

Comments(31) Filed in category: Excel, Vlookup

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

Comments(27) Filed in category: Add-in, Excel, Vlookup

Table of contents VLOOKUP and a condition VLOOKUP and a table VLOOKUP - Select a column with a drop down […]

Comments(26) Filed in category: Excel, Vlookup

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

Comments(21) Filed in category: Excel, Vlookup

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