INDEX and MATCH
INDEX and MATCH look up a value as a pair: MATCH finds the position of the item you name, and INDEX returns whatever sits at that position in another column.
Example
Read it from the inside out: find Cigar's position in B2:B7, then return the value at that position in E2:E7.
How INDEX and MATCH work together
A lookup asks two questions. Where in the list is the thing I want? And what is written next to it? MATCH answers the first and INDEX answers the second, which is why the two are almost always used together.
MATCH on its own returns a number, not a value. =MATCH("Cigar",Items!B2:B7,0) returns 4, because the cigar is the 4th name in that list. INDEX on its own does the reverse: =INDEX(Items!E2:E7,4) goes 4 cells down the Unit Value column and returns 10.
Put MATCH inside INDEX and the position is worked out for you. Change "Cigar" to another item and the formula finds that item's row instead.
The column you search and the column you return are two separate ranges. That is what makes the pair flexible: the answer can sit to the left of the search column, on another sheet, or anywhere else, as long as both ranges cover the same rows.
Syntax
INDEX
INDEX(array, row_num, [column_num], [area_num])
- Where the answer is
array(required) - The range or array to read from.
- Which position
row_num(required) - Row position within the array; 0 returns the whole column.
- Which column, for a block
column_num(optional) - Column position; 0 returns the whole row.
-
area_num(optional) - Which area, if the reference has several.
Returns: The value (or reference) at the given position.
MATCH
MATCH(lookup_value, lookup_array, [match_type])
Arguments in [square brackets] are optional.
- What to find
lookup_value(required) - What to find.
- Where to look
lookup_array(required) - A single row or column to search.
- Exact or nearest
match_type(optional) -
The third argument of MATCH decides how it searches. For lookups in a list of names or IDs, use 0.
0Exact match only. The list can be in any order.1 (or left out)The largest value that is less than or equal to the one you want. The list must be sorted smallest to largest.-1The smallest value that is greater than or equal to the one you want. The list must be sorted largest to smallest.0 with *With 0, an asterisk stands for any run of characters.0 with ?With 0, a question mark stands for any one character.
Returns: The position of the match within the array.
More examples
Each picture shows only the columns that formula reads.
-
Return a column to the left
What is the cigar's item ID, when the IDs sit to the left of the names?
Excel shows I04AB1Item IDItem2I01Boxer cartridge3I02Briar pipe4I03Candle5I04Cigar6I05Clay pipe7I06Commonplace bookItemsMATCH still searches the names in column B, but INDEX now returns from column A. The cigar's row gives back its ID, I04. Left or right of the names makes no difference to INDEX.
-
Find a row and a column at once
How many candles are there, picking both the row and the column by name?
Excel shows 20BCDEFGHI1ItemCategoryQtyUnit ValueOwnerKept AtAcquiredInsured2Boxer cartridgeTools12$3.50P01L0111/2/2024FALSE3Briar pipeSmoking7$4.25P01L016/18/2021FALSE4CandleHousehold20$2.00P03L011/6/2025FALSE5CigarSmoking5$10.00P01L012/11/2025FALSE6Clay pipeSmoking15$1.75P01L019/30/2023FALSE7Commonplace bookPapers9$6.00P01L014/15/2019FALSEItemsGive INDEX a block, B2:I7, and two MATCHes. The first finds Candle in the names (position 3). The second finds Qty in the header row (also position 3, after Item and Category). INDEX returns the cell where that row and column cross: 20.
-
=MATCH("Qty",Items!B1:I1,0)3
-
-
Match two conditions
Which smoking item has over 5 in stock?
Excel shows Briar pipeBCD1ItemCategoryQty2Boxer cartridgeTools123Briar pipeSmoking74CandleHousehold205CigarSmoking56Clay pipeSmoking157Commonplace bookPapers9ItemsEach test in brackets gives TRUE or FALSE for every row, and multiplying them gives 1 only where both hold. MATCH then looks for the first 1. The briar pipe (7) comes first; the clay pipe also passes, but MATCH stops at the first hit.
-
Show your own message when nothing matches
What happens when you ask for an item nobody owns?
Excel shows Not in the roomsBE1ItemUnit Value2Boxer cartridge$3.503Briar pipe$4.254Candle$2.005Cigar$10.006Clay pipe$1.757Commonplace book$6.00ItemsThe Items list has no deerstalker, so MATCH returns #N/A and INDEX passes it on. Wrapping the pair in IFNA swaps that error for words of your choice, here Not in the rooms.
-
=INDEX(Items!E2:E7,MATCH("Deerstalker",Items!B2:B7,0))#N/A
-
-
Look up on a different sheet
How much does a cab to Scotland Yard cost, from the Places sheet?
Excel shows 1.75BF1PlaceCab Fare2221B Baker Street$0.003Scotland Yard$1.754Diogenes Club$1.255Barts$2.006Paddington$0.757Waterloo Station$2.258Simpson's$1.509Lyceum Theatre$1.5010Pondicherry Lodge$5.5011Briony Lodge$1.0012Saxe-Coburg Square$1.7513Reichenbach Fall$48.0014Baskerville Hall$18.5015Stoke Moran$3.2516Copper Beeches$6.75PlacesBoth ranges point at the Places sheet: MATCH searches the place names in B and INDEX returns the fare from F. Scotland Yard's fare is 1.75. The formula itself can live on any sheet.
INDEX and MATCH or VLOOKUP?
VLOOKUP always searches the first column of its table and returns a column to the right of it, picked by counting. Ask it for the cigar's ID in column A and it searches column A for the word Cigar, finds nothing and returns #N/A. INDEX and MATCH return I04, because you pick the column to search and the column to return one at a time.
Pointing at the answer column also means there is nothing to count. VLOOKUP needs the answer's place in the table as a number, such as 5 for Unit Value in A2:I7; INDEX takes the column itself, E2:E7.
XLOOKUP, in Excel 2021 and Microsoft 365, does the same job in one function and has its own not-found argument. INDEX and MATCH still work in every version, which matters for files shared with older copies of Excel.
Watch out for
Leave out the 0 and MATCH guesses
Without a third argument MATCH assumes the list is sorted and looks for the nearest match. The place names are not sorted, so the formula for the Diogenes Club returns Barts' fare of 2 instead of 1.25, and nothing on the sheet flags it. Add the 0 for an exact match every time.
-
=INDEX(Places!F2:F16,MATCH("Diogenes Club",Places!B2:B16))2 -
=INDEX(Places!F2:F16,MATCH("Diogenes Club",Places!B2:B16,0))1.25
Both ranges must start on the same row
MATCH counts positions from the top of its own range. Start the search range at B1 and MATCH finds the cigar at position 5. Position 5 of E2:E7 is E6, one row too low, so you get the clay pipe's 1.75, not 10.
-
=INDEX(Items!E2:E7,MATCH("Cigar",Items!B1:B7,0))1.75
MATCH ignores case
Search for "cigar", all small letters, and MATCH still lands on Cigar, so the answer is 10. Capitals make no difference to it.
-
=INDEX(Items!E2:E7,MATCH("cigar",Items!B2:B7,0))10
MATCH needs one row or one column
Give MATCH a block two columns wide, such as B2:C7, and it returns #N/A even though Candle is in it. Search a single column, and use a second MATCH for the column if you need one.
-
=MATCH("Candle",Items!B2:C7,0)#N/A
A number stored as text is not a number
The boxer cartridges' quantity is the number 12. Search for "12" in quotes and MATCH looks for text, so it finds nothing. Search for 12 without quotes, or make both sides the same type.
-
=MATCH("12",Items!D2:D7,0)#N/A
Match and search options
match_type: exact match or nearest value
The third argument of MATCH decides how it searches. For lookups in a list of names or IDs, use 0.
| Value | What it does | Measured example | Excel shows |
|---|---|---|---|
0 | Exact match only. The list can be in any order. | =MATCH("Candle",Items!B2:B7,0) | 3 |
1 (or left out) | The largest value that is less than or equal to the one you want. The list must be sorted smallest to largest. | =MATCH(35,Data!J1:J5) | 3 |
-1 | The smallest value that is greater than or equal to the one you want. The list must be sorted largest to smallest. | =MATCH(35,{50,40,30,20,10},-1) | 2 |
0 with * | With 0, an asterisk stands for any run of characters. | =MATCH("Ca*",Items!B2:B7,0) | 3 |
0 with ? | With 0, a question mark stands for any one character. | =MATCH("?riar pipe",Items!B2:B7,0) | 2 |
Questions people ask
Is it better to use VLOOKUP or INDEX and MATCH?
INDEX and MATCH can return a column to the left of the one they search, where VLOOKUP gives #N/A. The pair also points at the answer column directly, so there is no column number to count or update.
-
=INDEX(Items!A2:A7,MATCH("Cigar",Items!B2:B7,0))I04 -
=VLOOKUP("Cigar",Items!A2:I7,1,FALSE)#N/A
Which is better, XLOOKUP or INDEX and MATCH?
XLOOKUP does the same lookup in one function and has a built-in not-found argument, but it needs Excel 2021 or Microsoft 365. INDEX and MATCH work in every version of Excel.
What is the difference between INDEX and MATCH?
MATCH returns a position: the cigar is item 4 of B2:B7. INDEX returns the value at a position: item 4 of E2:E7 is 10. Together they turn a name into a value.
-
=MATCH("Cigar",Items!B2:B7,0)4 -
=INDEX(Items!E2:E7,4)10
Should I use INDEX and MATCH or SUMIFS?
They answer different questions. INDEX and MATCH return the first matching value, which can be text such as Briar pipe. SUMIFS adds up every matching row and always gives a number, such as 12 for the smoking items worth 4 or more each.
-
=INDEX(Items!B2:B7,MATCH(1,(Items!C2:C7="Smoking")*(Items!D2:D7>5),0))Briar pipe -
=SUMIFS(Items!D2:D7,Items!C2:C7,"Smoking",Items!E2:E7,">=4")12
Where it works
- Excel
- INDEX and MATCH are both in every version of Excel from 2007 on.
- Google Sheets
- Google Sheets has INDEX and MATCH with the same names and the same everyday behavior.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .