# INDEX MATCH – multiple results

This article demonstrates how to work with multiple criteria using INDEX and MATCH functions.

#### Table of Contents

- INDEX MATCH - multiple results
- INDEX and MATCH - multiple criteria and multiple results
- INDEX and MATCH - multiple criteria and multiple results (Excel 365)
- Get Excel *.xlsx file
- How to do a case sensitive INDEX MATCH - array formula
- How to do a case sensitive INDEX MATCH - regular formula
- How to do a case sensitive partial match using INDEX and MATCH functions
- How to do a partial case sensitive match using two conditions in two columns
- INDEX MATCH - multiple criteria
- INDEX MATCH - partial text multiple conditions Excel 365
- INDEX MATCH - partial match multiple columns
- INDEX MATCH - Last value
- Extract the last value in a given cell range
- Return a hyperlink to the last value in a column
- Return the row number of the last value in a column
- Extract a corresponding value next to the last value in a column
- Find the last non-empty cell with short cut keys
- Match two columns and return another value on the same row (array formula)
- Match two columns and return another value on the same row (regular formula)
- Match two columns and return another value on the same row - case sensitive
- Match two columns and return another value on the same row - partial match
- Get Excel *.xlsx file
- Find the closest value
- Find closest value - Excel 365
- Find closest values
- Find closest values and return adjacent values
- Find closest value with a criterion

## 1. INDEX and MATCH - multiple criteria and multiple results

This section demonstrates how to use INDEX and MATCH functions to lookup and return multiple results. The lookup value is in cell E3, the lookup range is B3:B8.

Cells B3, B5, and B8 contains the lookup value, cell values in the corresponding cells in column C are returned. They are C3, C5, and C8.

There is actually a smaller formula that does the same thing: VLOOKUP - Return multiple values I also recommend the FILTER function if you are an Excel 365 user, the FILTER function is really easy to use.

The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the value in cell E3.

The matching rows are 3, 5 and 8 so the array formula returns 3 values in cell range E6:E8.

The formula above is an array formula, make sure you follow the instructions below on how to enter an array formula to make it work.

### 1.1 How to enter an array formula

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Now copy cell E6 and paste to cells below as far as needed.

### 1.2. Explaining formula in cell E6

#### Step 1 - Find matching values

The MATCH function matches a cell range against a single value returning an array.

MATCH($B$3:$B$8, $E$3, 0)

becomes

MATCH({"A"; "B"; "A"; "C"; "B"; "A"}, "A", 0)

and returns

{1; #N/A; 1; #N/A; #N/A; 1}.

If a value is equal to the search value MATCH function returns 1. If it is not equal the MATCH function returns #N/A.

The picture above displays the array in column A.

#### Step 2 - Convert array values to boolean values

The IF function cant process error values so to solve that I am going to use the ISNUMBER function to convert the array values to boolean values.

ISNUMBER(MATCH($B$3:$B$8, $E$3, 0))

becomes

ISNUMBER({1; #N/A; 1; #N/A; #N/A; 1})

and returns

{TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}.

The array is shown in column A, see picture below.

#### Step 3 - Identify rows

The IF function converts the boolean values into row numbers and blanks.

becomes

The MATCH and ROW functions calculate an array with sequential numbers, 1 to n, determined by the size of the cell range. In this case, $B$3:$B$8 has 6 values so the array becomes 1 to 6.

and returns

{1;"";3;"";"";6}.

The picture below shows the relative row numbers for cell range B3:B8.

#### Step 4 - Get the k-th smallest row number

To be able to return the correct value the formula must know which value to get. The SMALL function determines the value to get based on row number.

becomes

The ROWS function returns a number that changes when you copy the cell and paste to cells below.

and returns 1.

In the next cell below ROWS($A$1:A1) changes to ROWS($A$1:A2) and returns 2.

*Step 5 - Get values from column C using row numbers*

The INDEX function returns a value from a given cell range based on a row and column number.

becomes

The first cell value in cell range $C$3:$C$8 is 6, the INDEX function returns 6 in cell E6.

### 1.3. Get Excel file

## 2. INDEX and MATCH - multiple criteria and multiple results

This section demonstrates how to use INDEX and MATCH functions to match multiple conditions and return multiple results. The Excel 365 formula shown in section 2 is incredibly small, the new FILTER function is amazing.

The formula in cell C14 returns multiple values from column Item. It uses multiple criteria specified in C12:C13 and applied to column Color.

This formula can only retrieve one value per criteria, read this article to extract multiple values per criteria.

This should be an array formula, however, the second INDEX function makes this formula a regular formula.

### 2.1 Explaining formula in cell C14

#### Step 1 - Find relative position of specified conditions in C12:D12

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value*, *lookup_array*, *[match_type]*)

MATCH($C$12:$D$12, $B$3:$B$10, 0)

returns {3, 2}.

#### Step 2 - Get value based on the relative position

The INDEX function returns a value from a cell range, you specify which value based on a row and column number. However, in this case, it's used to convert the formula to a regular formula.

This is a workaround and it won't work in some array formula, it works fine in this one.

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

INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), )

returns {3, 2}.

#### Step 3 - Calculate k-th smallest number

The SMALL function returns the k-th smallest value from a group of numbers.

SMALL(*array*, *k*)

SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1))

becomes

SMALL({3, 2}, ROWS($A$1:A1))

The ROWS function counts the number of rows in a given cell reference, however, $A$1:A1 is a cell reference that grows automatically when you copy the cell and paste it to cells below. This makes the formula return a new value in each cell.

SMALL({3, 2}, ROWS($A$1:A1))

returns 2.

#### Step 4 - Get value

The INDEX function returns a value from a cell range, you specify which value based on a row and column number. However, in this case, it's used to convert the formula to a regular formula.

This is a workaround and it won't work in some array formula, it works fine in this one.

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

INDEX($C$3:$C$10, SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1)))

returns "C" in cell C14.

## 3. INDEX and MATCH - multiple criteria and multiple results - Excel 365

The new FILTER function is amazing, it returns multiple values based on boolean value TRUE or FALSE or their numerical equivalents.

Dynamic array formula in cell G3:

Excel 365 returns arrays automatically and deploys values to adjacent cells as far as needed, Microsoft calls this behavior "spilling".

### Explaining formula in cell G3

#### Step 1 - Count values based on criteria

The COUNTIF function counts values based on a condition or criteria.

COUNTIF(*range*, *criteria*)

COUNTIF(E3:E4, B3:B10)

returns {0; 1; 1; 0; 0; 0; 0; 0}.

#### Step 2 - Get values

The FILTER function lets you extract values/rows based on a condition or criteria.

FILTER(*array*, *include*, [*if_empty*])

FILTER(C3:C10, COUNTIF(E3:E4, B3:B10))

returns {"C"; "F"}.

### Get Excel *.xlsx file

INDEX and MATCH - multiple criteria and multiple results.xlsx

## 5. INDEX MATCH - Case sensitive

The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B using the value in cell F2, also considering letter casing, then return the corresponding value from column C.

The image above shows text values in cells B3:B8 with different upper and lower case letters. Cell range C3:C8 contains the corresponding amounts. The formula in cell F3 performs a case sensitive search using the input value in cell F2 against the text values in B3:B8.

Formula in cell F3:

Cell F2 contains "aAA" and the formula returns 4 from the amounts in cells C3:C8. The formula matches "aAA" to cell value "aAA" in cell B5, the corresponding value in cells C3:C6 is 4.

### How to enter an array formula

The formula above is an array formula. To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

If you prefer a regular formula, read "Alternative regular formula" below in this section.

### Explaining formula in cell F3

#### Step 1 - Compare lookup value with lookup column

The EXACT function allows you to compare values, if they are exactly the same the EXACT function returns TRUE. Note, the function is case-sensitive.

EXACT(F2,B3:B8)

becomes

EXACT("aAA",{"aaa"; "aaA"; "aAA"; "AAA"; "AAa"; "Aaa"})

and returns

{FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.

#### Step 2 - Identify the relative position of value TRUE in the array

The MATCH function finds a specific value in an array or cell range and returns its location, a number representing the position.

MATCH(TRUE,EXACT(F2,B3:B8),0)

becomes

MATCH(TRUE, {FALSE; FALSE; **TRUE**; FALSE; FALSE; FALSE}, 0)

and returns 3. TRUE is in the third position in the array.

#### Step 3 - Return corresponding value from column C

The INDEX function returns a value from an array or cell range based on the location. That is why the INDEX and MATCH functions work so well together.

INDEX(C3:C8,MATCH(TRUE,EXACT(F2,B3:B8),0))

becomes

INDEX(C3:C8,3)

becomes

INDEX({6;5;4;3;2;1},3)

and returns 4 in cell F3.

## 6. Alternative regular formula

Formula in cell F2:

## 7. How to do a case sensitive partial match using INDEX and MATCH functions

Formula in cell F3:

### 7. Explaining formula in cell F3

#### Step 1 - Perform a case sensitive search (partial match)

The FIND function returns a number representing the position of a specific substring in another string, reading left to right. Note, the FIND function is case-sensitive.

FIND(F2, B3:B8)

becomes

FIND("E", {"Cat"; "Horse"; "Snake"; "Tiger"; "Elephant"; "Mouse"})

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!}

Notice the error values in the array above, they appear if the substring is not found for each value in cell range B3:B8.

#### Step 2 - Identify numbers

The ISNUMBER function checks if a value is a number, it returns either TRUE or FALSE.

ISNUMBER(FIND(F2, B3:B8))

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!})

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.

#### Step 3 - Find the position of the first number in the array

The MATCH function returns a number representing the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value, lookup_array, [match_type]*)

MATCH(TRUE, ISNUMBER(FIND(F2, B3:B8)), 0)

becomes

MATCH(**TRUE**, {FALSE; FALSE; FALSE; FALSE; **TRUE**; FALSE}, 0)

and returns 5. TRUE is first found in position five in the array above.

#### Step 4 - Get corresponding value from the same row

The INDEX function returns a value from a given cell range based on a row and column number (optional).

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

INDEX(C3:C8, MATCH(TRUE, ISNUMBER(FIND(F2, B3:B8)), 0))

becomes

INDEX(C3:C8, 5)

becomes

INDEX({6;5;4;3;2;1}, 5)

and returns 2.

## 8. How to do a partial case sensitive match using two conditions in two columns

Formula in cell F3:

### 8.1 Explaining formula in cell F3

#### Step 1 - Perform a case sensitive search (partial match)

The FIND function returns a number representing the position of a specific substring in another string, reading left to right. Note, the FIND function is case-sensitive.

FIND(*find_text*,*within_text*, [*start_num*])

FIND(G4, B5:B10)

becomes

FIND("W", {"Brown"; "YeLLow"; "Pink"; "BLuE"; "BroWn"; "Black"})

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!}.

#### Step 2 - Perform a second case sensitive search (partial match)

FIND(G5, C5:C10)

becomes

FIND("M",{"small";"SMALL";"LaRgE";"SmAlL";"sMall";"small"})

and returns

{#VALUE!; 2; #VALUE!; #VALUE!; 2; #VALUE!}.

#### Step 3 - Multiply arrays

The formula returns a value if both substrings are found on the same row, to do that we need to apply AND logic.

The asterisk character lets you multiply values row by row, if both values are numbers the result is a number.

#VALUE! * #VALUE! = #VALUE!

#VALUE! * 2 = #VALUE!

2 * #VALUE! = #VALUE!

2*2 = 4

FIND(G4, B5:B10)*FIND(G5, C5:C10)

becomes

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!} * {#VALUE!; 2; #VALUE!; #VALUE!; 2; #VALUE!}

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 8; #VALUE!}.

#### Step 4 - Identify numbers

The ISNUMBER function checks if a value is a number, it returns either TRUE or FALSE.

ISNUMBER(FIND(G4, B5:B10)*FIND(G5, C5:C10))

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; 8; #VALUE!})

and returns {FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}.

#### Step 5 - Find the position of the first number in the array

The MATCH function returns a number representing the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value, lookup_array, [match_type]*)

MATCH(TRUE, ISNUMBER(FIND(G4, B5:B10)*FIND(G5, C5:C10)), 0)

becomes

MATCH(TRUE, {FALSE; FALSE; FALSE; FALSE; **TRUE**; FALSE}, 0)

and returns 5.

#### Step 6 - Get corresponding value on the same row

The INDEX function returns a value from a given cell range based on a row and column number (optional).

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

INDEX(D5:D10, MATCH(TRUE, ISNUMBER(FIND(G4, B5:B10)*FIND(G5, C5:C10)), 0))

becomes

INDEX(D5:D10, 5)

becomes

INDEX({1;2;3;4;5;6}, 5)

and returns 5.

### 8. Get Excel *.xlsx

INDEX MATCH Case sensitive.xlsx

## 9. INDEX MATCH with multiple criteria

This section demonstrates formulas that let you perform lookups using two or more conditions. The image above shows two conditions in cells B13:C13, the result is in cell D13.

The formula in cell D13 returns the first match where both cells meet the conditions on the same row.

The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula if possible, see the above picture.

The formula uses two conditions, one is specified in cell B13 and the other one in C13. It looks for the first condition in cell range B3 to B10 and the second condition in C3 to C10 if both conditions are met on the same row the corresponding name from cell range D3:D10 is returned.

### 9.1. Explaining formula in cell D13

#### Step 1 - Concatenate lookup values

The ampersand character concatenates both values you want to look for.

B13&C13

becomes

"East"&"A"

and returns "EastA".

#### Step 2 - Concatenate lookup columns

Then it concatenates the two cell ranges also using the ampersand character, the INDEX function makes it a regular formula.

B3:B10&C3:C10

returns {"WestC"; "EastE"; ... ; "WestH"}.

#### Step 3 - Make the array formula a regular formula

The INDEX function lets you convert some array formulas to regular formulas, this is one of them.

INDEX(B3:B10&C3:C10,)

The MATCH function then returns the relative position of the combined values, see picture above.

MATCH("**EastA**", {"WestC"; "EastE"; "WestD"; "EastG"; "WestB"; "**EastA**"; "WestF"; "WestH"}, 0)

and returns 6. The value is in the 6th position in the array.

#### Step 4 - Return value from the same row

INDEX(D3:D10, MATCH(B13&C13, INDEX(B3:B10&C3:C10, ), 0))

returns Jennifer in cell D13.

### 9.2 Array formula alternative

If you don't mind array formulas, the only advantage is that it is somewhat smaller, use this formula:

To enter an array formula press and hold CTRL + SHIFT simultaneously, then press Enter once. Release all keys.

The formula bar now shows the formula enclosed with curly brackets telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

## 10. INDEX MATCH - partial text multiple conditions Excel 365

The formula in cell D13 checks if a cell in B3:B10 contains text specified in cell B13 and on the same row, if the corresponding cell in C3:C10 contains the specified text in C13.

The formula returns a value from D3:D10 if both cells are on the same row and contain the given text strings.

Excel 365 formula in cell D13:

=FILTER(D3:D10,ISNUMBER(SEARCH($B$13,$B$3:$B$10)*SEARCH($C$13,$C$3:$C$10)))

This is a dynamic array formula that works only in Excel 365, it is entered as a regular formula, however, it spills values to cells below if needed.

For previous Excel versions, see this article: Lookup with multiple criteria and return multiple search results It uses INDEX MATCH to get values based on multiple partial conditions.

### 10.1 Explaining formula in cell D13

#### Step 1 - Search for partial text in cell range B3:B10

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

SEARCH(*find_text*,*within_text*, [*start_num*])

This formula is copied to cells below in order to get all matching values. We need to use absolute references to lock cell ranges to prevent cell ranges from changing as we copy the cell and paste to cells below.

SEARCH($B$13,$B$3:$B$10)

returns {#VALUE!; #VALUE!; ... ; 4}.

Note that the SEARCH function returns a error value if the string is not found in a cell value.

#### Step 2 - Search for second partial text condition in cell range C3:C10

SEARCH($C$13, $C$3:$C$10)

returns {6; 4; ... ; #VALUE!}.

#### Step 3 - Multiply arrays

We will mutiply both arrays to perform AND logic by using the asterisk character.

SEARCH($B$13, $B$3:$B$10)*SEARCH($C$13, $C$3:$C$10))

returns {#VALUE!; #VALUE!; ... ; #VALUE!}.

A number multipled with an error value returns a error value, however, a number multipled with a number returns a number.

#### Step 4 - Check if a value in the array is a number

The ISNUMBER function returns TRUE or FALSE based on the contents of the argument.

ISNUMBER(SEARCH($B$13, $B$3:$B$10)*SEARCH($C$13, $C$3:$C$10))

returns {FALSE; FALSE;...; FALSE}.

#### Step 4 - Extract values based on logical array

The FILTER function returns values/rows based on a condition or criteria, it is only availabe to Excel 365 subscribers.

FILTER(D3:D10, ISNUMBER(SEARCH($B$13, $B$3:$B$10)*SEARCH($C$13, $C$3:$C$10)))

returns {"Olivia"; "Jennifer"}.

## 11. INDEX MATCH - partial match multiple columns

The array formula in cell D13 extracts values from column D if the corresponding value in cell range B3:C3 contains the specified string in cell B13.

The condition in cell B13 is found in cells B4, B6, and C8. The corresponding values in column D are in D4, D6, and D8.

Array formula in cell D13:

### 11.1 Explaining formula in cell D13

#### Step 1 - Search cell range $B$3:$C$10 for string

The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)

SEARCH(*find_text*,*within_text*, [*start_num*])

SEARCH($B$13, $B$3:$C$10)

returns {#VALUE!,#VALUE!;... ,#VALUE!}

#### Step 2 - Identify numbers in array

The ISNUMBER function returns TRUE or FALSE based on the contents of the argument.

ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1

The MMULT function can't calculate boolean values like TRUE and FALSE, we need to convert them to their numerical equivalents. TRUE - 1 , and FALSE - 0 (zero)

returns {0,0; 1,0; 0,0; 1,0; 0,0; 0,1; 0,0; 0,0}.

#### Step 3 - Create an array containing 1

TRANSPOSE(COLUMN($B$3:$C$10)^0)

The COLUMN function returns row numbers based on a cell range.

COLUMN($B$3:$C$10)

returns {2,3}.

COLUMN($B$3:$C$10)^0

returns {1,1}.

TRANSPOSE(COLUMN($B$3:$C$10)^0)

becomes

TRANSPOSE({1, 1})

and returns {1; 1}.

#### Step 4 - Add values column-wise

The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.

MMULT(*array1*, *array2*)

MMULT(ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1, TRANSPOSE(COLUMN($B$3:$C$10)^0))

becomes

MMULT({0,0; 1,0; 0,0; 1,0; 0,0; 0,1; 0,0; 0,0}, {1; 1})

and returns {0; 1; 0; 1; 0; 1; 0; 0}.

#### Step 5 - Create an array from 1 to n

The ROW function lets you create numbers representing the rows based on a cell range.

The MATCH function finds the relative position of a given string in an array or cell range. This will create an array from 1 to n where n is the number of rows in cell range $D$3:$D$10.

MATCH(ROW($D$3:$D$10),ROW($D$3:$D$10))

and returns {1; 2; ... ; 8}. There are eight rows in $D$3:$D$10.

#### Step 6 - Replace 1 with corresponding row number

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(MMULT(ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1, TRANSPOSE(COLUMN($B$3:$C$10)^0)), MATCH(ROW($D$3:$D$10), ROW($D$3:$D$10)), "")

returns {""; 2; ""; 4; ""; 6; ""; ""}.

#### Step 7 - Extract k-th smallest row number

The SMALL function returns the k-th smallest value from a group of numbers.

SMALL(*array*, *k*)

SMALL(IF(MMULT(ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1, TRANSPOSE(COLUMN($B$3:$C$10)^0)), MATCH(ROW($D$3:$D$10), ROW($D$3:$D$10)), ""), ROWS($A$1:A1))

becomes

SMALL({""; 2; ""; 4; ""; 6; ""; ""}, ROWS($A$1:A1))

becomes

SMALL({""; 2; ""; 4; ""; 6; ""; ""}, 1)

and returns 2.

#### Step 8 - Get values based on row number

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

INDEX($D$3:$D$10, SMALL(IF(MMULT(ISNUMBER(SEARCH($B$13, $B$3:$C$10))*1, TRANSPOSE(COLUMN($B$3:$C$10)^0)), MATCH(ROW($D$3:$D$10), ROW($D$3:$D$10)), ""), ROWS($A$1:A1)))

returns "Sophia".

The following formula is an Excel 365 dynamic array formula:

Absolute cell references are not required, the formula returns an array that spills values to cells below automatically.

### 11.4. Get Excel file

## 12. Find last value in a column

This section demonstrates formulas that return the last value in a given cell range or column. The image above shows a formula in cell D3 that extracts the last value from cell range B3:B12.

Cell range B3:B12 is populated with values and empty cells in random order, this shows that the formula works fine with empty cells.

INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first match. I have shown before how to lookup all matching values in this post: INDEX MATCH – multiple results and this article: VLOOKUP and return multiple values

Today I will show you how to get the last matching value, the image above demonstrates this formula in cell E6. It looks for value BB and the last matching value is found on row 12, the corresponding value in column C is 10 and this value is returned in cell E6.

Array formula in cell E6:

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell E6

#### Step 1 - Check if values are equal to lookup value

The equal sig lets you compare a cell value to another cell value, in this case, I am comparing a cell against an entire cell range.

returns {FALSE; TRUE; FALSE; ... ; TRUE}

#### Step 2 - Divide 1 with array

This step is special to the LOOKUP function, it allows us to get the last matching value.

1/(B3:B12=E3)

returns {#DIV/0!; 1; #DIV/0!;... ; 1}.

#### Step 3 - MATCH function

The MATCH function ignores error values and matches the last number smaller than the lookup value, in this specific situation.

MATCH(2,1/(B3:B12=E3)) returns 10.

#### Step 4 - INDEX function

The INDEX function returns a value from a cell range based on row an column numbers.

INDEX($C$3:$C$12,MATCH(2,1/(B3:B12=E3))) returns 10.

### Get Excel *.xlsx file

## 13. Extract the last value in a given column

The formula in cell D3 lets you get the last value in column B, it works fine with blank cells in your list.

Formula in cell D3:

The formula is quite cpu-intensive since it is processing all cells in column B, there are more than a million cells in one column in Excel 2007 and later versions.

You can change the cell references if you know you will have a smaller list, this formula is easier for Excel to process:

### 13.1 Explaining formula

Then it divides 1 with the array creating errors for all empty cells.

The LOOKUP function ignores errors and tries to find a match. If every match is 1 and the lookup value is 2, the LOOKUP function returns last value in cell range. Normally the list must be sorted ascending for the LOOKUP function to work, however since every value is 1 there is no need to sort the list.

I am going to use the following formula because it will be easier to demonstrate:

LOOKUP(2,1/(B3:B12<>""),B3:B12)

#### Step 1 - Check if cells in column B are not equal to nothing

The less than and greater than signs are logical operators, they check if cell values in column B are not empty. The result is an array containing TRUE or FALSE.

B3:B12<>"" returns {TRUE;TRUE;TRUE; ... ;FALSE}

#### Step 2 - Divide 1 with array

1/(B3:B12<>"")

returns {1;1;1;... ;#DIV/0!}

Boolean value TRUE is equal to 1 and FALSE is equal to 0. You can't divide a value with zero so Excel returns an error (#DIV/0!).

#### Step 3 - Return value

LOOKUP(2,1/(B3:B12<>""),B3:B12) returns XH in cell D3.

### 13.2 What about errors?

I have added a few errors in column B, see the image above.

I am working with Excel 2016 and errors in column B seem to not be an issue.

## 14. Return a hyperlink to the last value in a column

The formula in cell D3, demonstrated in the image above, creates a hyperlink to the last cell in column B. Press with left mouse button on the hyperlink takes you to the last value in column B.

Formula in cell D3:

### 14.1 Explaining formula

#### Step 1 - Get path and workbook name

The CELL function gets information about the formatting, location, or the contents of a cell.

CELL("filename", A1)

returns C:\temp\[Find last value in listv2.xlsx]Hyperlink

#### Step 2 - Find the location of character [

We don't need the path in order to create a working hyperlink. This step returns the character of the first string that we do need.

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.

SEARCH("[", CELL("filename", A1)) becomes SEARCH("[", "C:\temp\[Find last value in listv2.xlsx]Hyperlink")

and returns 35.

#### Step 3 - Extract workbook and worksheet name

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

MID(text, start_num, num_chars)

MID(CELL("filename", A1), SEARCH("[", CELL("filename", A1)), LEN(CELL("filename", A1)))

returns "[Find last value in listv2.xlsx]Hyperlink"

#### Step 4 - Concatenate workbook name, worksheet name, and column

The ampersand character & lets you concatenate strings in an Excel formula.

MID(CELL("filename", A1), SEARCH("[", CELL("filename", A1)), LEN(CELL("filename", A1)))&"!$B$"

returns "[Find last value in listv2.xlsx]Hyperlink!$B$".

#### Step 5 - Calculate row number of the last vale in a column

The ROW function allows you to calculate the row number of the last value.

MID(CELL("filename", A1), SEARCH("[", CELL("filename", A1)), LEN(CELL("filename", A1)))&"!$B$"&LOOKUP(2, 1/(B:B<>""), ROW(B:B))

returns "[Find last value in listv2.xlsx]Hyperlink!$B$11"

#### Step 6 - Return the last value

LOOKUP(2, 1/(B:B<>""), B:B)

returns "XH".

#### Step 7 - Create a hyperlink

The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, cell range, or webpage.

HYPERLINK(MID(CELL("filename", A1), SEARCH("[", CELL("filename", A1)), LEN(CELL("filename", A1)))&"!$B$"&LOOKUP(2, 1/(B:B<>""), ROW(B:B)), LOOKUP(2, 1/(B:B<>""), B:B))

returns a hyperlink to cell B11.

## 15. Return the row number of the last value in a column

The formula in cell D3 returns a number representing the row number of the last value in column B.

Formula in cell D3:

## 16. Return adjacent value

This data set has two columns B and C. The formula in cell E3 returns the corresponding value of the last value in column B from column C.

Formula in cell E3:

The formula returns an adjacent value of the last value. In fact, it doesn't need to be adjacent, you can change the cell reference (C:C) as long as its starting point and ending point are the same as the first cell reference (B:B).

## 17. Find the last non-empty cell with short cut keys

The following steps decribe how to select the last non-empty cell in column B using keyboard keys.

- Press with the left mouse button on cell A1 to select it.
- Press and hold CTRL key.
- Press down arrow key once. This takes you to the last cell in column A, if all cells in column A are empty.
- Release all keys. Press right arrow key to move to the last cell in column B.
- Press and hold CTRL key.
- Press up arrow key once. This takes you to the last non-empty cell in column B. See the image above.

## 18. Match two columns and return another value on the same row

The array formula in cell D12 matches two values in two columns each and returns a value on the same row. First condition is specified in cell B12 and the second condition in cell C12.

The formula returns a value from cell range D3:D9 if both conditions are met on the same row. The example above returns cell value "NN" from cell D7.

Cell value "F" and "2" are found in cells B7 and C7, the corresponding value from cell D7 is returned to cell D12.

Formula in cell D12:

If you are looking for a way to compare two columns for differences or compare two columns for same values, please press with left mouse button on links.

You are not limited to formulas, conditional formatting allows you to compare two columns and highlight matches or compare two columns and highlight differences.

### 18.1 How to enter an array formula

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

Note that Excel 365 users can enter the formula as a regular formula.

### 18.2 Explaining formula

#### Step 1 - COUNT cells based on criteria

The COUNTIFS function counts rows where both values match, however, it returns an array that corresponds to the number of rows in cell range B3:D9.

COUNTIFS(B12, $B$3:$B$9, C12,$C$3:$C$9)

returns {0; 0; 0; 0; 1; 0; 0} , shown in column F in picture below.

Both values match in row 7.

#### Step 2 - Calculate the relative position

The MATCH function returns a number representing the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value, lookup_array, [match_type]*)

MATCH(1, COUNTIFS(B12, $B$3:$B$9, C12,$C$3:$C$9) ,0)

The MATCH function identifies the relative position of the matching values.

MATCH(1, {0;0;0;0;1;0;0} ,0)

and returns 5, 1 is the fifth value in the array.

#### Step 3 - Get value

The INDEX function returns a value from a given cell range based on a row and column number (optional).

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

INDEX($D$3:$D$9, MATCH(1, COUNTIFS(B12, $B$3:$B$9, C12,$C$3:$C$9) ,0))

The INDEX function returns the corresponding value from column D.

INDEX($D$3:$D$9, 5)

and returns BB in cell D12.

## 19. Match two columns and return another value on the same row - regular formula

This example is identical to the example above, however, it uses a regular formula mot an array formula.

Formula in cell D12:

The above formula is a regular formula, it is slightly larger than the first formula demonstrated above in section 1.

## 20. Match two columns and return another value on the same row - case sensitive

Formula in cell D12:

### 20.1 Explaining formula in cell D12

#### Step 1 - Identify values equal to value in cell B12 considering upper and lower case letters

The EXACT function lets you compare values also considering upper and lower cases.

EXACT(*text1*, *text2*)

EXACT(B12,$B$3:$B$9)

becomes

EXACT("F",{"D";"A";"f";"G";"F";"C";"a"})

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}.

#### Step 2 - Identify values equal to value in cell C12 considering upper and lower case letters

EXACT(C12,$C$3:$C$9)

becomes

EXACT(2, {4; 7; 2; 1; 2; 5; 7})

and returns

{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}.

#### Step 3 - Multiply arrays AND logic

To apply AND logic we must multiply the arrays using the asterisk character.

EXACT(B12,$B$3:$B$9)*EXACT(C12,$C$3:$C$9)

becomes

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE} * {FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}

and returns {0; 0; 0; 0; 1; 0; 0}.

#### Step 4 - Find position

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value*, *lookup_array*, [*match_type*])

MATCH(1, EXACT(B12,$B$3:$B$9)*EXACT(C12,$C$3:$C$9),0)

becomes

MATCH(1, {0; 0; 0; 0; 1; 0; 0},0)

and returns 5.

#### Step 5 - Get value based on row number

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

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

INDEX($D$3:$D$9, MATCH(1, EXACT(B12,$B$3:$B$9)*EXACT(C12,$C$3:$C$9),0))

becomes

INDEX($D$3:$D$9, 5)

becomes

INDEX({"DD"; "GG"; "FF"; "AA"; "BB"; "EE"; "CC"}, 5)

and returns "BB" in cell D12.

## 21. Match two columns and return another value on the same row - partial match

Formula in cell D12:

### 21.1 Explaining formula in cell D12

#### Step 1 - Find position of first condition in string

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right. The function is not doing a case-sensitive search.

SEARCH(*find_text*,*within_text*, [*start_num*])

SEARCH(B12, $B$3:$B$9)

becomes

SEARCH("esc",{"paper"; "investment"; "difference"; "mall"; "description"; "memory"; "judgment"})

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!}

Notice the #VALUE! error, this is what you get if the string is not found.

#### Step 2 - Find position of the second condition in string

SEARCH(C12, $C$3:$C$9)

becomes

SEARCH("iet", {"supermarket"; "psychology"; "media"; "football"; "variety"; "difficulty"; "mud"})

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!; #VALUE!}.

#### Step 3 - Multiply arrays

Both arrays must return a number in the same position in the array, this can be created using the asterisk sign and multiplying the arrays.

SEARCH(B12, $B$3:$B$9)*SEARCH(C12, $C$3:$C$9)

becomes

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 2; #VALUE!; #VALUE!}* {#VALUE!; #VALUE!; #VALUE!; #VALUE!; 4; #VALUE!; #VALUE!}

and returns

{#VALUE!; #VALUE!; #VALUE!; #VALUE!; 8; #VALUE!; #VALUE!}.

#### Step 4 - Find the numbers in the array

The ISNUMBER function checks if a value is a number, returns TRUE or FALSE.

ISNUMBER(*value*)

ISNUMBER(SEARCH(B12, $B$3:$B$9)*SEARCH(C12, $C$3:$C$9))

becomes

ISNUMBER({#VALUE!; #VALUE!; #VALUE!; #VALUE!; 8; #VALUE!; #VALUE!})

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE}.

#### Step 5 - Find the position of the first number in the array

The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.

MATCH(*lookup_value*, *lookup_array*, [*match_type*])

MATCH(TRUE, ISNUMBER(SEARCH(B12, $B$3:$B$9)*SEARCH(C12, $C$3:$C$9)), 0)

becomes

MATCH(TRUE, {FALSE; FALSE; FALSE; FALSE; **TRUE**; FALSE; FALSE}, 0)

and returns 5. TRUE is the fifth value in the array.

#### Step 6 - Get value based on position

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

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

INDEX($D$3:$D$9, MATCH(TRUE, ISNUMBER(SEARCH(B12, $B$3:$B$9)*SEARCH(C12, $C$3:$C$9)), 0))

becomes

INDEX($D$3:$D$9, 5)

and returns "BB". Value "BB" is the fifth value in D3:D9.

## Get Excel *.xlsx

## 23. Find the closest value

The image above demonstrates a formula in cell E4 that extracts the closest number to the given number in cell E2.

Array formula in cell E4:

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

### 23.2 Explaining array formula

#### Step 1 - Subtract numbers with search number

The minus sign lets you subtract numbers in an Excel formula.

B3:B25-E2

returns {-40; 34; ... ; 56}

#### Step 2 - Remove negative sign

The ABS function converts negative numbers to positive numbers.

ABS(number)

ABS(B3:B25-E2)

returns {40; 34; ... ; 56}

#### Step 3 - Find smallest number

The MIN function extracts the smallest number from in a cell range or array.

MIN(ABS(B3:B25-E2))

returns 1.

#### Step 4 - Find the relative position of the smallest number

The MATCH function returns the relative position of a given value in a cell range or array.

MATCH(*lookup_value*, *lookup_array*, [*match_type*])

MATCH(MIN(ABS(B3:B25-E2)),ABS(B3:B25-E2),0)

returns 12.

#### Step 5 - Get the number

The INDEX function returns a value based on a row and column number.

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

INDEX(B3:B25,MATCH(MIN(ABS(B3:B25-E2)),ABS(B3:B25-E2),0))

returns 42.

## 24. Find closest value - Excel 365

Dynamic array formula in cell E4:

### 24.1 Explaining formula

#### Step 1 - Calculate the difference between numbers and search number

The minus sign allows you to subtract numbers in an Excel formula.

B3:B25-E2

becomes

{3; 77; 7; ... ; 99} - 43

and returns {-40; 34; ... ; 56}

#### Step 2 - Convert negative numbers to positive numbers

The ABS function converts negative numbers to positive numbers.

ABS(number)

ABS(B3:B25-E2)

returns {40; 34; ... ; 56}

#### Step 3 - Sort numbers based on difference

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array. It sorts values by column but keeps rows.

It is located in the Lookup and reference category and is only available to Excel 365 subscribers.

SORTBY(*array, by_array1, [sort_order1], [by_array2, sort_order2]*,…)

SORTBY(B3:B25, ABS(B3:B25-E2))

returns {42; 48; ... ; 99}

#### Step 4 - Get first value in array

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

INDEX(SORTBY(B3:B25, ABS(B3:B25-E2)),1) returns 42 in cell E4.

## 25. How to find the closest values

The following array formula returns a list of numbers closest to the search number sorted from small to large.

Array formula in cell C4:

How to create an array formula

### 25.1 How to copy array formula

- Select cell C4
- Copy cell c4 (Ctrl + c)
- Select cell range C4:C20
- Paste (Ctrl + v)

### 25.2 Explaining formula

#### Step 1 - Subtract numbers with search number

The minus sign lets you subtract numbers in an Excel formula.

$A$2:$A$24-$E$1

returns {-40; 34; ... ; 56}.

#### Step 2 - Convert negative numbers to positive numbers

The ABS function converts any negative values to positive values.

ABS(*number*)

ABS($A$2:$A$24-$E$1)

returns {40; 34;... ; 56}

#### Step 3 - Extract the k-th smallest number

The SMALL function returns the k-th smallest number in a cell range or array.

SMALL(array, k)

The ROW function returns a number representing the row based on a cell reference.

ROW(ref)

SMALL(ABS($A$2:$A$24-$E$1), ROW(A1))

returns 1. Number 1 is the smallest number in the array.

#### Step 4 - Count based on condition

The COUNTIF function counts values based on a condition, in this case, a cell reference. Not a regular cell reference but an expanding cell reference that grows when you copy the cell to the cells below.

COUNTIF($C$3:C3, $A$2:$A$24)

returns {0; 0; ... ; 0}.

#### Step 5 - Count based on conditions

The COUNTIF function counts values based on a condition, in this case, based on multiple values.

COUNTIF($A$2:$A$24, $A$2:$A$24)

returns {1; 2; ... ; 1}.

#### Step 6 - Compare counts

The less than character lets you check if a value is smaller than another value, the result is a boolean value TRUE or FALSE.

COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24)

returns {TRUE; TRUE; ... ; TRUE}.

#### Step 7 - Replace TRUE with corresponding positive number

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF(COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), "A")

returns {40; 34; ... ; 56}.

#### Step 8 - Calculate the relative position

MATCH(*lookup_value*, *lookup_array*, [*match_type*])

MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), "A"), 0)

returns 11. 1 is found in position 11 in the array or cell reference.

#### Step 9 - Get value

The INDEX function returns a value based on a row and column number.

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

INDEX($A$2:$A$24, MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), "A"), 0))

becomes

INDEX($A$2:$A$24, 11)

and returns 44.

**Get excel file**

## 26. How to find closest values and return adjacent values

Array formula in cell D5:

Array formula in cell E5:

How to create an array formula

### 26.1 How to copy array formula

- Select cell D5
- Copy (Ctrl + c)
- Select cell range D6:D28
- Paste (Ctrl + v)

## 27. Find closest value based on criterion

I need a formulae which will first match the entries in column A wih entries in column C .then compare the date in column D with the dates in column B and then throw up the nearest date.

So my data sheet looks something like this:

Col A Col B Col C Col D Col E(result)

A 1/2/2013 A 5/1/2013 4/3/2013

A 2/2/2013 B 5/2/2013 5/5/2013

A 4/3/2013

B 5/5/2013

B 9/10/2013

As you can see the formulae first compares the entries in col C (A) with the entries in col A (all the A's) then it matches the date in col D (5/1/2013) with the dates pertaining to value A in col B.

The closest date then is 4/3/2013 which is the answer.

Array formula in cell E1:

**Workbook Find closest value**

### Workbook find closest value criterion

### Index match category

Table of Contents Lookup multiple values across columns and return a single value Lookup using multiple conditions Lookup a date […]

### Excel categories

### 62 Responses to “INDEX MATCH – multiple results”

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

hi oscar, i have a question that expands this topic ;)

above, there are 3 columns: [ID][OrderDate][Product].

search result would be in the columne where you put the formula in. <-- i call this [Result]

questions:

1) What if there are multiple columns? [ID][OrderDate][ProductA][ProductB][ProductC]?

this would output to [ResultA][ResultB][ResultC]

2) there are 3 methods above. I prefer Method 1 (plain 'ol formula only).

If I have 5000 (and growing) list of ID/OrderDates, which method would be practical?

thanks!

david,

1) You say you prefer the first formula:=INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)) + ENTER

I modified the above formula to get the other two matching cells:

=INDEX($D$3:$F$6, SUMPRODUCT(--($C$10=ID_num), --($C$11=OrderDate), ROW(ProductA)-MIN(ROW(ProductA))+1), COLUMN(A1)) + ENTER.

Copy cell and paste it to the right as far as needed.

Remember, this formula can only match one row. If multiple rows match you need another formula.

2) I think you answer your own question. If the first formula works and is reasonable fast, I´d also go with that one.

thx oscar, will try this out.

hi oscar,

i've tried the formula. it works but only if it's on the same sheet as the data.

if i move the formula on other sheet, it failed on the INDEX portion.

if u need a sample file, do let me know. i'll upload it somewhere for you :)

thanks!

assuming there is NO MATCH for ID and OrderDate ....by evaluating the formula when it's in INDEX portion, it gets

=INDEX($D$3:$F$6,0,1) <-- the row syntax=0 because there was no match.

however, it gets trickier.

if this formula is NOT within row 3-6, then it will generate an #VALUE error. (e.g. i put the formula at H8)

if i move this formula to a cell between row 3-6, the INDEX will pull the same value as per the row of the formula.

e.g. when the formula is at H4, the output is Green (D4). if formula at H5, the output is Yellow (D5).

so the question is, if there is NO MATCH for ID or OrderDate, how to output it as 0 (zero)?

thx :)

david,

Excel 2003:=IF(SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)=0, 0, INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1))) + Enter

Excel 2007:=IFERROR(INDEX(Product, SUMPRODUCT(--(C10=ID_num), --(C11=OrderDate), ROW(Product)-MIN(ROW(Product))+1)), 0) + Enter

hi oscar, thanks for your feedback.

i'm looking at Excel 2007 formula.

the IFERROR doesnt make any difference.

try this scenario:

1) move the B14 formula to H4

2) enter search ID = 4

3) u'll notice that it gives you the result as Green because the final formula step is =INDEX(Product,0,1) <-- the peculiarity occurs because the row syntax is 0.

Excel 2003's formula works though

david,

You are right, the excel 2007 formula is wrong.

Thanks!

Thank you, you have taken 2 days of frustration away.

Alfredo,

Thank you for commenting!

[…] https://www.get-digital-help.com/2017/08/16/find-last-value-in-a-column/ […]

I'm building a workbook to search for any results that may use up to 34 criteria. So far I've built a formula from the website to fill six criteria, and I've hit a snag. The formula is creating duplicates. I want to avoid creating a list of results with duplicate values, then building a separate formula to create a list of unique values. Is there a way to do that all in one formula?

Here's the formula: =INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1)))

Name = B3:B59

Category = AH3:AM59

Justin,

Yes, it is possible. If you enter the formula in cell F2 the formula becomes:

=INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category)*(COUNTIF($F$1:F1,Category)=0), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1)))

Any tips for doing this if theres multiple pairs of columns? Is it possible to concatenate results from multiple formulas of this kind into one column. For example lets say you were searching over 'n' pairs of the "text" and "amount" columns side by side, but still wanted the search results in a single column, like you have?

Joe Elizondo,

Yes, it is possible.

Array formula in cell C11:

=IFERROR(INDEX($C$3:$C$7, SMALL(IF(ISNUMBER(MATCH($B$3:$B$7, $C$9, 0)), MATCH(ROW($B$3:$B$7), ROW($B$3:$B$7)), ""), ROWS($A$1:A1))), INDEX($F$3:$F$7, SMALL(IF(ISNUMBER(MATCH($E$3:$E$7, $C$9, 0)), MATCH(ROW($E$3:$E$7), ROW($E$3:$E$7)), ""), ROWS($A$1:A1)-COUNTIF($B$3:$B$7, $C$9))))

The first formula didn't seem to work.

However, =INDEX(C3:C8, MATCH(TRUE, INDEX(EXACT(F2, B3:B8), ), 0))

worked for me.

Thank you :)

Sunil,

The first formula is an array formula. You probably didn't press CTRL + SHIFT + ENTER to create an array formula.

Hi Oscar,

This example helped me to get closer to what I am looking for, but not completely yet :).

In my case, I am looking to retrieve the sum of the results returned in a single cell.

First of all, I have converted your formula to column based:

The formula used is:

`={IFERROR(INDEX($C$14:$L$14,SMALL(IF(ISNUMBER(MATCH($C$13:$L$13,C$16,0)),MATCH(COLUMN($C$13:$L$13),COLUMN($C$13:$L$13)),""),ROWS($A$1:A1))),0)}`

Text A G E C E A B G C C

Amount 2 4 1 3 2 3 1 3 1 2

Search A B C D E F G H

Results 2 1 3 0 1 0 4 0

3 0 1 0 2 0 3 0

0 0 2 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

0 0 0 0 0 0 0 0

SUM 5 1 6 0 3 0 7 0

As you see I have to create many rows to find all matches of each value. In my real case, I have more than 100 columns to match, and I don't know how many matches I will have. So I look for a way to return the sum of all matches (A - H) in a single row.

I am also working on a solution within VBA as back-up.

I hope you can help me. Please feel free to contact me if you need additional information.

Kind regards,

Jorgen

Hi Jorgen

Try this:

Formula in cell J3:

=SUMPRODUCT((B2:G13=I3)*1)

Hi Oscar,

This was super useful, thanks! I'm trying to take this one step further and be able to return all match instances of a certain value while having to search through more than a single-column array. To work through this using your example, I added a second column of Amounts and modified your formula to look up a given Amount and return the Text values that match that Amount. I got this to work with your INDEX(SMALL(IF(ISNUMBER(MATCH())))) and can pull all of the Text values from both Amount columns. I've also managed to return only Text values with that Amount from Amount2 using INDEX(MATCH(INDEX(MATCH))), however this can only find the first instance in the array. What I'm really trying to do is a combination of these: return all of the Text values within the given Amount array, while narrowing the search to a specific column within the array. Do you have any tips for this?

Formula in cell F5

`=INDEX($B$2:$B$14, SMALL(IF(ISNUMBER(MATCH($C$2:$D$14, $F$2, 0)), MATCH(ROW($C$2:$D$14), ROW($C$2:$D$14)), ""), ROWS($A$1:A1)))`

Formula in cell G5

`=INDEX($B$2:$B$14, MATCH($F$2, INDEX($C$2:$D$14, 0, MATCH($G$2, $C$1:$D$1,0)),0))`

Thanks,

Jeremiah

Jeremiah,

I believe you are looking for this formula:

https://www.get-digital-help.com/2011/06/22/vlookup-a-range-in-excel/

Oscar,

Thanks for the reply. It looks like my image link didn't come through, trying again here:

https://imgur.com/a/FjajCCc

The article you referenced is close to what I'm looking for, but it doesn't allow me to narrow my search within the array to return all matches from only one desired column. Hopefully my example in the screenshot linked above will clarify this, the objective in cell H4 is what I'm trying to figure out a formula for.

Best,

Jeremiah

Jeremiah,

Formula in cell B14:

=INDEX($B$3:$B$6, SMALL(IF((INDEX($C$3:$E$6, 0, MATCH($C$10,$C$2:$E$2, 0))=$C$9)*(COUNTIF($B$13:B13, $B$3:$B$6)=0), ROW($C$3:$E$6)-MIN(ROW($C$3:$E$6))+1, ""), 1))

Hi Oscar,

Thank you very much for your article, it's very useful!

Regarding the last formula you've sent to Jeremiah, I'm also trying to have it working in a similar way but haven't been successful.

Basically instead of a match between a column and a row, I'm trying for the match of two columns to return the corresponding cells of a 3rd column.

Can you help me on this? Should I send what I have until now?

Best,

João

Table-1 Table-2

Number Name Number Name

10 A 10 search with Search result

11 B 13 C 13 #REF!

13 16 F

14 D 19 I

15 E 21 K

16

17 G

18 H

19

20 J

I have used this formula =+INDEX($D$3:$D12,MATCH($H4,$C$3:$C12,INDEX($G$3:$G7,MATCH($H4,$F$3:$F7,0))))

but is not working

I want there as 'C' because in Table-1 13 corresponding nothing so the formula should check with Table-2 with and result will be 'C'

Advance thanks

Hey Good Day,

can u do it if you have multiple person in same organization like

Org | Name | Badge | GC

649238 Rayn 64982 08

649238 Jhon 78421 11

649238 sara 76899 06

when i setup it with index match it gives me Rayn duplicated.

Rayn

The following link takes you to an article that demonstrates how to extract records based on a lookup value:

https://www.get-digital-help.com/how-to-return-multiple-values-using-vlookup-in-excel/#multiple

Thanks for the Tip, it already helped a lot, i just have the Problem, that my matches are not sorted from the earliest to the latest date. I don´t know why, because with the "SMALL" Funktion it should sort my matches by date beginning from the earliest date?

Flo

If column c contains dates that you want to extract based on a lookup value then use this array formula in cell E6:

=SMALL(IF($E$3=$B$3:$B$8, $C$3:$C$8, ""),ROWS($A$1:A1))

More examples here:

https://www.get-digital-help.com/2013/04/23/small-function-and-large-function/

This article demonstrates how to use multiple conditions:

https://www.get-digital-help.com/2017/12/15/small-function-multiple-criteria/

Oscar,

Your original set up almost answers my question, but I need to take it one step further. Instead of creating 3 rows in order to return the 6,4 & 1 values, I would like to sum all three into one cell. How do I do that.

https://i.postimg.cc/dVSffxhx/excell-1.jpg

https://i.postimg.cc/FHfpYsKT/excel2.jpg

So in my example, I would like to look up "B", and then lookunder the month of January, and sum up the number of "B"'s that is returned under that column. I just want the one row fo rth esum of B's. I dont want to have multiple cells that I still have to solve for, and I dont want to hid cells. Under the Cell for B, I might want to look up the Number of any letter in a particular month.

Does that make sense?

Thanks

Mike.

"Now copy cell E6 and paste to cells below as far as needed."

I would like to use data validation to allow the results to appear in a drop-down list in cell E6, instead of using the copy/paste instructions above. Is this possible?

Kyle

Yes, it is possible, however, you still need to extract the values using the formula perhaps on a different sheet.

I recommend a dynamic named range: Create a dynamic named range

Hello Oscar!

I'm new to excel and i just got employed in a firm that uses it a lot and im dealing with complex stuff so i really need some help.

I'm trying to make a table that will show me all the offers my employer made or is making by their status (i.e. in preparation, accepted, rejected etc).

This is the main table of offers https://imgur.com/1ilGTl0

What i need is: using the value in the right most column R(0-7) which shows the status of the offer, to get in,a new table, the left most column (column A) value (70-p/2019,72-p/2019) in an order without entering all that manually like im doin right now as shown in https://imgur.com/t8Kfp9N

I hope i was clear enough and that you can help me.

Looking forward to your response

Any idea why my formula is returning #N/A instead of the value I want? I am pretty sure it is working as intended because it should return 6 values and it is returning 6 #N/A and then #value. I am stumped. Is col A above a helper column? or is it all contained in the formula? Any assistance trouble shooting would be much appreciated!

Also, do you have any tips on how to write this for two variables? i.e return the corresponding customer if the variable is 5 or 6?

Adam,

Any idea why my formula is returning #N/A instead of the value I want?Make sure the cell references are correct. You need to enter the formula as an array formula.

Is col A above a helper column?No

or is it all contained in the formula?Yes

Any assistance trouble shooting would be much appreciated!I recommend you use "Evaluate Formula" tool found on tab "Formulas" on the ribbon.

Oscar, thanks so much! I really appreciate your assistance, the issue was the array formula, I overlooked that part in the initial article.

Thanks again!

what is wrong in this formula.

show #value

but in your excel sheet it shows correctly

=INDEX($L$2:$L$41,SMALL(IF(ISNUMBER(MATCH($M$2:$M$41,A2,0)),MATCH(ROW($M$2:$M$41),ROW($M$2:$M$41)),""),ROWS($A$1:A1)))

Thank you for publishing this wonderful guide. I am having one problem with it though. The formula returns the correct number of matches but each match listed is the first cell (cell O2) referenced in the index function. I need it to return the information matched for each cell from row 2.

=INDEX('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$O$2:$BS$2,SMALL(IF(ISNUMBER(MATCH('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$O$5:$BS$5,$B$7,0)),MATCH(ROW('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$O$5:$BS$5),ROW('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$O$5:$BS$5)),""),ROWS('[TUL_Heatmap_MES_All 2020.xlsm]2020'!$A$1:A1)))

May I ask How to Find the Last Match in a Range with a Wildcard? Thank you very much.

Grace,

Formula in cell E6:

=XLOOKUP(E3,B3:B12,C3:C12,"N/A",2,-1)

XLOOKUP is a new formula recently introduced to Excel 365 subscribers.

Grace,

Try this array formula if you own an earlier version of Excel.

=INDEX($C$3:$C$12,MATCH(2,1/SEARCH(E3,B3:B12)))

Dear Oscar

Thank you very much.

thanks again and again

Dear Oscar

Thank you very much.

thanks again and again

Guys how can I tell excel to ignore if the last match is NA?

Claudia,

(Array) formula in cell E6:

=INDEX($C$3:$C$12, MATCH(2, 1/((B3:B12=E3)*(NOT(ISNA(C3:C12))))))

Excel 365 subscribers do not need to enter the formula as an array formula.

Hi guys

How can I tell excel that I want second to last match as a result ???

This problem is difficult to explain, but I will try. I’m creating a database to keep track of stock option spread trades that have numerous legs. Some of the trades are simple and only having two legs, with one position being long, and the other being short. But, some of the trades have 8-10 legs, and this creates a problem in calculating the value of each leg. For this example, lets open a credit spread trade and go long an option, and go short an option. This is trade number 500 and each leg is recorded on a separate row. I want to invest $1000 per leg, so we need to take the ABS difference of the two fill prices and divide it $1000. This will tell us the quantity purchased. Eventually, we’ll close the short leg, and open a new short leg. And each time, we need to calculate a quantity by performing the above calculation with the long leg. This cycle of closing the short leg and opening a new short leg can continue until the long leg is finally closed, which ends the trade. So, let’s say we have 8-legs in this trade, the first row contains the long leg, and the 7 rows below contain the short legs. Each row has an ID of 500 to identify all 8-rows as trade number 500. Each time a short leg is closed, and a new short leg is opened, we need to scan the table for ID 500, then scan those rows to find the long leg. We then need to go over 5 columns to locate the long leg fill price to use in the new quantity calculation. Hope all that makes sense! Thanks, Jeff

Please can you explain why the MATCH lookup_value is 2?

Step3 example appears to repeat itself:

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

Penny's dropped, it's looking for the last number (1) smaller than the lookup value 2.

Maybe Step 3 should be:

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

becomes

MATCH(2,{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1},1)

Hi, I need to introduce a wildcard to this formula:

From this...

`MATCH($B$3:$B$8, $E$3, 0)`

To this...

`MATCH($B$3:$B$8, $E$3&"*", 0)`

But doing so breaks the formula. Do you have a workaround for this?

Hi Oscar, you are such a great resource. I was rocking right along until i copied the formula to the 12th column I received the #Num! error. Maybe I don't understand the ",ROWS($A$1:A1))),0)}" function. My data resides on two other sheets here is my formula:

=INDEX(STUDENT_A!$A$2:$A$6335, SMALL(IF(ISNUMBER(MATCH(STUDENT_A!$I$2:$I$6335, X$1, 0)), MATCH(ROW(STUDENT_A!$I$2:$I$6335), ROW(STUDENT_A!$I$2:$I$6335)), ""), ROWS(STUDENT_A!$A$1:$A1)))

Please advice. thanks

I got the exact same issue, also drawing from an external sheet. Any ideas?

Hello.

Thank you for publishing those awesome tutorials.

I am having a little problem here - I would like to test for the regular expression value (value you provided here in $E$3). It seems not to work.

I was trying to break the formulas to simple steps and everything seems to work fine if the value in the cell is exact match. Regular expressions seem to be treated as normal typing.

Do you know anything about it, maybe know a workaround?

Jakub

I believe you need VBA to be able to use regular expressions: https://www.get-digital-help.com/like-operator/

Hi Oscar,

Hoping you can help,

I'm trying to do an index and match multiple results across 2 groups with over 14,000 values to match.

I've tried to show an example below of what I'm trying to create.

I need to index a list of nested groups and users in sheet 1. Then match the group name they are a member of in sheet 2 with the same name in sheet 1 so that these values are pulled though to sheet 2 where the new group name is in a separate column.

DLG Domain 2 Returned Nested Groups`Users

DLG_1 Domain1\GG_Group

DLG_1 Domain2\GG_Group

DLG_1 User1

DLG_1 User2

DLG_2 Domain1\GG_Group

DLG_2 User1

DLG_2 User2

DLG_3 Domain1\GG_Group

DLG_3 Domain2\GG_Group

DLG_3 User1

DLG_3 User2

DLG_3 User3

Paul Quirie,

I think you are looking for this: https://www.get-digital-help.com/lookups-in-a-related-table-array-formula/

Dear Professor Oscar

I was wondering if there is a possibility, to simply ignore all text or blank cells and only search the maximum number value with the criteria in one column. It would also need the formula to be able to evaluate a cell which has mixed contents, like: "V20-001"

Here is the sample

Voucher criteria Result in Max Result in Min

V20-001 V20* V20-500 V20-001

X20-001 X20* X20-500 X20-001

V20-003

X20-100

V20-500

X20-500

X20-002

V20-002

I tried to use this formula:-=INDEX(A8:A51878,MATCH(2,1/SEARCH(A5,A8:A51878))) but it is not work.

Thank you very much.

Best regards,

Grace

Is there a way to ignore previous matched values? In the instance where there are multiple matches, that the formula will return the next matched value?

Many, many, many thanks! Exactly what I needed.

`=you:you are great, TRUE`

I have app list with corresponding wave list. Each app can belong to multiple wave

Eg:

app1 Wave1

app1 Wave2

app2 Wave1

app1 Wave5

app1 Wave6

I am looking for result like this

app1 wave1, wave2, wave5, wave6...

The formula in this tutorial is to do multiple match function but i dont have definite wave number. each app can belong to 1 or 10 wave. its kind of like looping through wave and finding a match. so far I just have index & match function in 4 and more cells which is not very clean. any assitance appreciated. Thanks

Hi Oscar,

Thanks for this great article; it has helped me a lot!

Is there a way to pull multiple results from 12 different tables?

I have a table for each of the 12 months, and I need to pull specific data (based on a single criterion) from all 12 tables into one annual table.

I saw your reply to Joe regarding two different tables, but I couldn't figure out how to modify it for 12 different tables.

Any help is appreciated; thanks!