INDEX MATCH – multiple results

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

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.

Back to top

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.

Back to top

6. Alternative regular formula

Formula in cell F2:

Back to top

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

Formula in cell F3:

Back to top

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.

Back to top

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.

Back to top

8. Get Excel *.xlsx

INDEX MATCH Case sensitive.xlsx

Back to top

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.

B3:B12=E3

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

INDEX MATCH - Last value_v2

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:

Back to top

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.

Back to top

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.

Back to top

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

Back to top

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.

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

Back to top

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.

Back to top

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.

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.

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

Back to top

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.

Back to top

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.

Back to top

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

Formula in cell D12:

Back to top

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.

Back to top

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

Formula in cell D12:

Back to top

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.

Back to top

Get Excel *.xlsx

Back to top

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

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once
4. Release all keys

Back to top

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.

Back to top

Back to top

24. Find closest value - Excel 365

Dynamic array formula in cell E4:

Back to top

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

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(SORTBY(B3:B25, ABS(B3:B25-E2)),1) returns 42 in cell E4.

Back to top

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

Back to top

25.1 How to copy array formula

1. Select cell C4
2. Copy cell c4 (Ctrl + c)
3. Select cell range C4:C20
4. Paste (Ctrl + v)

Back to top

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

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

Back to top

Get excel file

find-closest-values.xls

Back to top

Back to top

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

Back to top

27. Find closest value based on criterion

Array formula in cell E1:

Back to top

Workbook Find closest value

Back to top

Workbook find closest value criterion

Back to top

Index match category

More than 1300 Excel formulas

Excel categories

Comments (62)