| | | This function should be used instead of one of the other LOOKUP functions when you need the position rather than the value. |
| | | The "lookup_value" is the value you want to match in the "lookup_array". |
| | | The "lookup_value" can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. |
| | | The "lookup_array" can be a cell reference or an array. |
| | | If "lookup_array" references more than a single column of cells, then #N/A is returned. |
| | | If "lookup_array" reference moe than a single row of cells, then #N/A is returned. |
| | | If "lookup_array" is not a single column of cells, a single row of cells or a single dimensional array, then #N/A is returned. |
| | | If "match_type" is left blank, then it is assumed to be 1. |
| | | If the "match_type" > 1, then it is assumed to be 1. |
| | | If "match_type" = 1, then "lookup_array" must be sorted into ascending order (-1, 0, 1, a, z, A, Z, FALSE, TRUE). |
| | | If "match_type" = 1, and "lookup_value" does not appear in the "lookup_array", then the position of the largest value less than it is returned. |
| | | If "match_type" = 0, then "lookup_array" does not have to be sorted. |
| | | If "match_type" = 0 and "lookup_value" does not exist in the "lookup_array", then #N/A is returned. |
| | | If "match_type" = -1, then "lookup_array" must be sorted into descending order (TRUE, FALSE, Z, A, z, a, 1, 0, -1). |
| | | If "match_type" = -1, and "lookup_value" does not appear in the "lookup_array", then the position of the smallest value greater than it is returned. |
| | | There is no distinction between uppercase and lowercase letters when matching text values. |
| | | Example 1 - What is the position of the value 10 in the sorted block of cells. |
| | | Example 2 - What is the position of the value 10 in the sorted block of cells. The default "match_type" is 1. |
| | | Example 3 - What is the position of the item "Cherry" in the sorted block of cells. |
| | | Example 4 - The default "match_type" is 1 so it returns the position of the largest value less than 13, which is 10. (list in ascending order) |
| | | Example 5 - The default "match_type" is 1 so it returns the position of the largest value less than "Cherrys" which is "Cherry". (list in ascending order) |
| | | Example 6 - The "match_type" is 0 and because the "lookup_value" does not exist, #N/A is returned. |
| | | Example 7 - The "match_type" is 0 and because the "lookup_value" does not exist, #N/A is returned. |
| | | Example 8 - The "match_type" is -1 so it returns the position of the smallest value greater than 13, which is 15. (list in descending order) |
| | | Example 9 - The "match_type" is -1 so it returns the position of the smallest value greater than "Cherrys" which is "Lemon". (list in descending order) |
| | | Example 10 - The "match_type" is 1 and because the "lookup_value" is smaller than the first item, #N/A is returned. |
| | | Example 11 - The "match_type is 1 and because the "lookup_value" is greater than the last item, 7 is returned. |
| | | Example 12 - The "match_type is -1 and because the "lookup_value" is smaller than the last item, 7 is returned. |
| | | Example 13 - The "match_type is -1 and because the "lookup_value" is greater than the first item, #N/A is returned. |
| | | Example 14 - If the "match_type" > 1, then it is assumed to be 1. |
| | | Example 15 - If "lookup_array" is a multi-dimensional array, then #N/A is returned. |
| | | Example 16 - If "lookup_array" refers to multiple rows or columns, then #N/A is returned. |
| | | For more working examples please refer to the MATCH Function page. |
| | | | A | B | C | D | E | | 1 | =MATCH(10,B1:B7) = 2 | 5 | 35 | Apple | Strawberry | | 2 | =MATCH(10,B1:B7,1) = 2 | 10 | 30 | Banana | Peach | | 3 | =MATCH("Cherry",D1:D7) = 3 | 15 | 25 | Cherry | Orange | | 4 | =MATCH(13,B1:B7) = 2 | 20 | 20 | Lemon | Lemon | | 5 | =MATCH("Cherrys",D1:D7) = 3 | 25 | 15 | Orange | Cherry | | 6 | =MATCH(13,B1:B7,0) = #N/A | 30 | 10 | Peach | Banana | | 7 | =MATCH("Cherrys",D1:D7,0) = #N/A | 35 | 5 | Strawberry | Apple | | 8 | =MATCH(13,C1:C7,-1) = 5 | | | | | | 9 | =MATCH("Cherrys",E1:E7,-1) = 4 | | | | | | 10 | =MATCH(2,B1:B7,1) = #N/A | | | | | | 11 | =MATCH(40,B1:B7,1) = 7 | | | | | | 12 | =MATCH(2,C1:C7,-1) = 7 | | | | | | 13 | =MATCH(40,C1:C7,-1) = #N/A | | | | | | 14 | =MATCH(20,B1:B7,10) = 4 | | | | | | 15 | =MATCH(2,{1,2;3,4;5,6}) = #N/A | | | | | | 16 | =MATCH("Cherry",D1:E2) = #N/A | | | | |
| |
