Yes. In Excel, INDEX and MATCH work together to find a value by its position: MATCH locates the requested item, and INDEX returns the value at that position. With product codes in A2:A6, prices in C2:C6, and the code to search for in E2, use =INDEX(C2:C6,MATCH(E2,A2:A6,0)) to return the matching price. The final 0 asks MATCH for an exact match.
What INDEX and MATCH do
INDEX and MATCH solve two different parts of a lookup. MATCH searches a row or column and returns a number indicating where it found the item. INDEX uses a position number to return the value from a specified range. Combining them lets a formula find a record based on one field and return a related value from another field.
For example, a product list might store codes in one column and prices in another. If the code P-104 appears fourth in the code range, MATCH returns 4. INDEX then returns the fourth value in the price range. The formula does not need to know that P-104 is on worksheet row 5; it only needs the position within the selected ranges.
This is a formula, not a separate Excel command or add-in. It is useful for price lists, staff directories, inventory tables, and other data where you need to retrieve a value by a matching label. The lookup range and return range should normally cover the same number of rows or columns.
How the combined formula works
The syntax for MATCH is MATCH(lookup_value,lookup_array,match_type). The lookup value is what you want to find, the lookup array is where Excel searches, and match type determines the matching rule. For a typical text or ID lookup, use 0 for an exact match.
INDEX has an array form with the syntax INDEX(array,row_num,[column_num]). The array is the range containing the answer. The row number tells Excel which row within that range to return; the optional column number selects a column when the array has multiple columns. In a one-column return range, the row number supplied by MATCH is enough.
In a combined formula, MATCH is nested inside INDEX. Excel evaluates MATCH to get a position, then uses that position in INDEX. This is why the ranges must line up: if MATCH searches six rows but INDEX is pointed at a different six-row range, the returned position may refer to the wrong record even though the formula calculates.
Build a basic exact-match lookup
Suppose a worksheet contains this small product list: A2:A6 holds P-101, P-102, P-103, P-104, and P-105; C2:C6 holds 4.50, 1.25, 3.00, 12.00, and 8.50 in the same order. Enter P-104 in E2. With those ranges and that lookup value, enter this formula in F2: =INDEX(C2:C6,MATCH(E2,A2:A6,0)). The result is 12.00, the price on the same list row as P-104.
Here is the calculation in sequence:
- MATCH searches A2:A6 for the value in E2, P-104.
- Because P-104 is the fourth item in that range, MATCH returns 4.
- INDEX returns the fourth item in C2:C6, which is 12.00.
The formula uses relative references, so copying it down can be convenient when each row contains a different lookup value. If the lookup and data ranges need to remain fixed while the formula is copied, use absolute references. With codes in A2:A6, prices in C2:C6, and the lookup value in E2, an absolute-range version is =INDEX($C$2:$C$6,MATCH(E2,$A$2:$A$6,0)). The dollar signs lock the two data ranges while E2 can change to E3, E4, and so on.
Use INDEX and MATCH for a two-way lookup
A two-way lookup finds an item at the intersection of a row and a column. For example, assume A2:A4 contains Asha, Ben, and Cara; B1:D1 contains Jan, Feb, and Mar; and B2:D4 contains their monthly totals. If F2 contains Cara and G2 contains Mar, the formula can use one MATCH to find Cara’s row and another to find Mar’s column.
With the names in A2:A4, month headers in B1:D1, values in B2:D4, the requested name in F2, and the requested month in G2, enter =INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(G2,B1:D1,0)). If Cara’s March total in D4 is 160, the formula returns 160. The first MATCH supplies the row position within B2:D4; the second supplies its column position.
This pattern is helpful when a report’s row and column headings can both vary. It avoids writing a separate formula for every month or person. For correct results, make sure the header labels match the values in the input cells, and keep the data body aligned with both heading ranges.
Look left or search across a row
INDEX and MATCH are not limited to returning values to the right of the lookup column. MATCH can search one range while INDEX returns a value from another range on either side. For instance, if names are in B2:B6 and employee IDs are in A2:A6, you can search for a name and return its ID from the column to the left.
With IDs in A2:A6, names in B2:B6, and the requested name in E2, use =INDEX(A2:A6,MATCH(E2,B2:B6,0)). If the matching name is Jordan in the third position and A4 contains E-203, the result is E-203. This flexibility is one reason the combination is often used instead of VLOOKUP, which normally requires its lookup column to be the first column in its table range.
The same idea works horizontally. If a row of month names is in B1:F1, the corresponding row of values is in B2:F2, and the requested month is in H1, the formula can search the header row and return the value at the same position. With those ranges, use =INDEX(B2:F2,MATCH(H1,B1:F1,0)). If H1 contains Apr and April is the fourth header, the formula returns the fourth value from B2:F2.
Choose the right MATCH option
For most lookups, match type 0 is the safest choice because it requests an exact match and does not require the lookup range to be sorted. If no exact value is found, MATCH returns #N/A. Exact matching is appropriate for product codes, names, invoice numbers, and other labels where a nearby value is not an acceptable substitute.
MATCH also supports approximate matching, but the sort order matters:
- With match type 1, MATCH finds the largest value that is less than or equal to the lookup value. The lookup array must be sorted in ascending order.
- With match type -1, MATCH finds the smallest value that is greater than or equal to the lookup value. The lookup array must be sorted in descending order.
- With match type 0, MATCH seeks an exact match. In text searches, it can also use wildcard characters such as * for a string of characters and ? for one character.
Approximate matching can be useful for ordered thresholds, such as assigning a band based on a score, but it can silently give an unintended result if the range is not sorted as required. If you are uncertain about the order or need a precise label, use 0.
Fix common errors and unexpected results
#N/A usually means MATCH did not find the requested value. Check for misspellings, extra spaces, inconsistent IDs, and differences between numbers stored as text and numeric values. Also confirm that the lookup value is actually within the lookup range. When appropriate, TRIM can help remove ordinary leading or trailing spaces from text, though it does not correct every kind of hidden character.
A wrong but plausible answer often points to ranges that do not correspond. If the lookup range begins at row 2 but the return range begins at row 3, MATCH’s position will select a shifted result. Check that both ranges begin and end on the same records and that the data has not been sorted in one column without sorting the whole table.
#REF! can indicate that a referenced range was deleted or otherwise made invalid. A formula that returns a value from the wrong column may have an incorrect INDEX array or, in a two-way lookup, an incorrect column-header range. Review each range separately before changing the formula’s matching logic.
Text matching with MATCH is generally not case-sensitive, so a search for “abc” can match “ABC.” If case differences distinguish records in your data, ordinary INDEX/MATCH with MATCH type 0 is not enough to enforce case sensitivity; you need a different formula approach. Duplicate lookup values are another important detail: MATCH returns the position of the first match it encounters, not every matching row.
INDEX and MATCH compared with XLOOKUP
In Microsoft 365 for Windows, XLOOKUP is often a simpler option for a basic lookup. It combines searching and returning into one function and can search in either direction. For example, with product codes in A2:A6, prices in C2:C6, and the code in E2, a direct alternative is =XLOOKUP(E2,A2:A6,C2:C6). XLOOKUP is available in Microsoft 365 and Excel 2021 or later.
INDEX and MATCH remain useful when a workbook must work in older Excel releases that do not include XLOOKUP, or when you need to build a two-way lookup using row and column positions. They also make the separate search range and return range explicit. VLOOKUP is another familiar alternative, but its lookup column must be at the left edge of the table range and its return column is specified by a column number, which can be less flexible if the table layout changes.
There is no universal need to replace an existing INDEX/MATCH formula. If it is clear, correct, and compatible with the workbook’s users, keeping it may be sensible. For a new workbook used only in current Excel, compare the readability of XLOOKUP with the flexibility and compatibility requirements of the file.
Frequently Asked Questions
Is INDEX MATCH better than VLOOKUP?
It can be more flexible because MATCH can search a column while INDEX returns a value from a column on either side. VLOOKUP is straightforward for tables where the lookup field is the first column. The better choice depends on the workbook’s Excel versions, layout, and how easy the formula is for its users to maintain.
Do I need to sort data for INDEX and MATCH?
No sorting is required when MATCH uses 0 for an exact match. Sorting is required for the approximate match types 1 and -1, and the order must match the selected type. For ordinary ID or name lookups, exact matching is usually the appropriate starting point.
Why does INDEX MATCH return #N/A?
Most often, MATCH cannot find an exact equivalent of the lookup value. Check the spelling, spaces, number-versus-text formatting, and the lookup range. If the value appears more than once, MATCH normally returns the first occurrence rather than an error.
Can INDEX and MATCH return a value from a column to the left?
Yes. Point MATCH at the column containing the lookup value and point INDEX at the column containing the answer. The return column does not have to be to the right, as long as the two ranges line up row by row.
Is INDEX MATCH case-sensitive?
No. MATCH with exact-match type 0 does not distinguish uppercase from lowercase in ordinary text comparisons. If capitalization is meaningful in your data, use a case-sensitive lookup method rather than assuming this formula separates the values.
Related
- Excel to PowerPoint Linked Cells: How to Link and Update
- Convert Excel to PowerPoint: Methods, Steps, and Tips
- Convert PowerPoint to Excel: Steps, Options, and Limits
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula Bar: What It Shows and How to Use ItIn Excel, the Formula Bar is the field above the worksheet that shows the contents of the selected…
- Multiply Time by a Number in Excel: Formula and TipsTo multiply a time value by a number in Excel, enter a formula such as =A2*B2, where A2…
- Excel VLOOKUP Shortcut Keys: What Actually WorksExcel has no dedicated keyboard shortcut that inserts or runs VLOOKUP by itself. The quickest keyboard approach is…
- Excel SUMIFS With One Criterion: Formula and ExamplesTo sum values in Excel when one condition is true, use SUMIFS with one criteria range and one…
- Excel Formula to Return a Blank When No ValueYes. In Excel for Microsoft 365 on Windows, use an IF formula that returns an empty text string…
- Excel Find and Replace Within a Formula: Steps and TipsYes. In Excel for Microsoft 365 on Windows, press Ctrl+H, open Options if needed, set Look in to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.