How to Use the INDEX and MATCH Function in Excel GA S REGULAR Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office 25 25 people found this article helpful

How to Use the INDEX and MATCH Function in Excel

Discover the potential of the powerful lookup combo

By Tim Fisher Tim Fisher Senior Vice President & Group General Manager, Tech & Sustainability Emporia State University Tim Fisher has more than 30 years' of professional technology experience. He's been writing about tech for more than two decades and serves as the VP and General Manager of Lifewire. lifewire's editorial guidelines Updated on January 6, 2021 Reviewed by Ryan Perian Reviewed by Ryan Perian Western Governors University Ryan Perian is a certified IT specialist who holds numerous IT certifications and has 12+ years' experience working in the IT industry support and management positions. lifewire's editorial guidelines Tweet Share Email Tweet Share Email MS Office Excel Word Powerpoint Outlook

What to Know

The INDEX function can be used alone, but nesting the MATCH function inside it creates an advanced lookup.This nested function is more flexible than VLOOKUP and can yield results faster. This article explains how to use the INDEX and MATCH functions together in all versions of Excel, including Excel 2019 and Microsoft 365.

What Are the INDEX and MATCH functions

INDEX and MATCH are Excel lookup functions. While they are two entirely separate functions that can be used on their own, they can also be combined to create advanced formulas. The INDEX function returns a value or the reference to a value from within a particular selection. For example, it could be used to find the value in the second row of a data set, or in the fifth row and third column. While INDEX could very well be used alone, nesting MATCH in the formula makes it a bit more useful. The MATCH function searches for a specified item in a range of cells and then returns the relative position of the item in the range. For example, it could be used to determine that a specific name is the third item in a list of names.

INDEX and MATCH Syntax & Arguments

This is how both functions need to be written in order for Excel to understand them: =INDEX(array, row_num, [column_num]) array is the range of cells that the formula will be using. It can be one or more rows and columns, such as A1:D5. It's required.row_num is the row in the array from which to return a value, such as 2 or 18. It's required unless column_num is present.column_num is the column in the array from which to return a value, such as 1 or 9. It's optional. =MATCH(lookup_value, lookup_array, [match_type]) lookup_value is the value you want to match in lookup_array. It can be a number, text, or logical value that's typed manually or referred to via a cell reference. This is required. lookup_array is the range of cells to look through. It can be a single row or a single column, such as A2:D2 or G1:G45. This is required. match_type can be -1, 0, or 1. It specifies how lookup_value is matched with values in lookup_array (see below). 1 is the default value if this argument is omitted. Which Match Type to Use Match Type What It Does Rule Example 1 Finds the largest value that's less than or equal to lookup_value. The lookup_array values must be placed in ascending order (e.g., -2, -1, 0, 1, 2; or A-Z;, or FALSE, TRUE. lookup_value is 25 but it's missing from lookup_array, so the position of the next smallest number, like 22, is returned instead. 0 Finds the first value that's exactly equal to lookup_value. The lookup_array values can be in any order. lookup_value is 25, so it returns the position of 25. -1 Finds the smallest value that's greater or equal to lookup_value. The lookup_array values must be placed in descending order (e.g., 2, 1, 0, -1, -2). lookup_value is 25 but it's missing from lookup_array, so the position of the next largest number, like 34, is returned instead. Use 1 or -1 for times when you need to run an approximate lookup along a scale, like when dealing with numbers and when approximations are okay. But remember that if you don't specify match_type, 1 will be the default, which can skew the results if you're really wanting an exact match.

Example INDEX and MATCH Formulas

Before we look at how to combine INDEX and MATCH into one formula, we need to understand how these functions work on their own.

INDEX Examples

=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1) In this first example, there are four INDEX formulas we can use to get different values: =INDEX(A1:B2,2,2) looks through A1:B2 to find the value in the second column and second row, which is Stacy.=INDEX(A1:B1,1) looks through A1:B1 to find the value in the first column, which is Jon.=INDEX(2:2,1) looks through everything in the second row to locate the value in the first column, which is Tim.=INDEX(B1:B2,1) looks through B1:B2 to locate the value in the first row, which is Amy.

MATCH Examples

=MATCH("Stacy",A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0) Here are four easy examples of the MATCH function: =MATCH("Stacy",A2:D2,0) is searching for Stacy in the range A2:D2 and returns 3 as the result.=MATCH(14,D1:D2) is searching for 14 in the range D1:D2, but since it's not found in the table, MATCH finds the next largest value that's less than or equal to 14, which in this case is 13, which is in position 1 of lookup_array.=MATCH(14,D1:D2,-1) is identical to the formula above it, but since the array isn't in descending order like -1 requires, we get an error.=MATCH(13,A1:D1,0) is looking for 13 in the first row of the sheet, which returns 4 since it's the fourth item in this array.

INDEX-MATCH Examples

Here are two examples where we can combine INDEX and MATCH in one formula:

Find Cell Reference in Table

=INDEX(B2:B5,MATCH(F1,A2:A5)) This example is nesting the MATCH formula within the INDEX formula. The goal is to identify the item color using the item number. If you look at the image, you can see in the "Separated" rows how the formulas would be written on their own, but since we're nesting them, this is what's happening: MATCH(F1,A2:A5) is looking for the F1 value (8795) in the data set A2:A5. If we count down the column, we can see it's 2, so that's what the MATCH function just figured out.The INDEX array is B2:B5 since we're ultimately looking for the value in that column.The INDEX function could now be rewritten like this since 2 is what MATCH found: INDEX(B2:B5, 2, [column_num]).Since column_num is optional, we can remove that to be left with this: INDEX(B2:B5,2).So now, this is like a normal INDEX formula where we're finding the value of the second item in B2:B5, which is red.

Lookup By Row and Column Headings

=INDEX(B2:E13,MATCH(G1,A2:A13,0),MATCH(G2,B1:E1,0)) In this example of MATCH and INDEX, we're doing a two-way lookup. The idea is to see how much money we made off of Green items in May. This is really similar to the example above, but an extra MATCH formula is nested in INDEX. MATCH(G1,A2:A13,0) is the first item solved in this formula. It's looking for G1 (the word "May") in A2:A13 to get a particular value. We don't see it here, but it's 5.MATCH(G2,B1:E1,0) is the second MATCH formula, and it's really similar to the first but is instead looking for G2 (the word "Green") in the column headings at B1:E1. This one resolves to 3.We can now rewrite the INDEX formula like this to visualize what's happening: =INDEX(B2:E13,5,3). This is looking in the whole table, B2:E13, for the fifth row and third column, which returns $180.

MATCH and INDEX Rules

There are several things to keep in mind when writing formulas with these functions: MATCH isn't case sensitive, so uppercase and lowercase letters are treated the same when matching text values. MATCH returns #N/A for multiple reasons: if match_type is 0 and lookup_value isn't found if match_type is -1 and lookup_array isn't in descending order, if match_type is 1 and lookup_array isn't in ascending order, and if lookup_array isn't a single row or column. You can use a wildcard character in the lookup_value argument if match_type is 0 and lookup_value is a text string. A question mark matches any single character and an asterisk matches any sequence of characters (e.g., =MATCH("Jo*",1:1,0)). To use MATCH to find an actual question mark or asterisk, type ~ first. INDEX returns #REF! if row_num and column_num don't point to a cell within the array.

Related Excel Functions

The MATCH function is similar to LOOKUP, but MATCH returns the position of the item instead of the item itself. VLOOKUP is another lookup function you can use in Excel, but unlike MATCH which requires INDEX for advanced lookups, VLOOKUP formulas only need that one function. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Use the Excel INDEX Function How to Use the XLOOKUP Function in Excel Finding the Location of Data With Excel's MATCH Function How to Use the ISBLANK Function in Excel How to Create an Excel Lookup Formula With Multiple Criteria How to Use the Round Function in Excel How to Combine the ROUND and SUM Functions in Excel How to Create an Excel Left Lookup Formula Using VLOOKUP How to Use the COUNTIF Function in Excel How to Use the Excel MID Function INDEX-MATCH vs. VLOOKUP in Excel How to Find Data with VLOOKUP in Excel Perform Multiple Calculations With Excel Array Formulas How to Use the Excel DATE Function How to Round Numbers Down in Excel With the ROUNDDOWN Function How to Combine Two Columns in Excel Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies