Ravindra BagaleCourses & study guides मराठी Track your progress

Excel INDEX and MATCH Function Explained (with XMATCH): Lookup Without XLOOKUP

By Last updated:

XLOOKUP is in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. Many offices still run older versions, and interviewers love to ask "how would you do this without XLOOKUP?" The answer is INDEX and MATCH: two simple functions that, together, do everything VLOOKUP does and more, in every version of Excel. We finish with XMATCH, MATCH's newer sibling, which can search from the bottom.

What you'll learn in this chapter

  • MATCH: find the position of a value
  • Position inside a range vs sheet row number
  • INDEX: get the value at a position
  • INDEX + MATCH together
  • Lookup to the left
  • A two-way lookup: row and column
  • XMATCH and the last match
  • A Not found message with IFNA

Practice file: excel-index-match-xmatch-practice.xlsx: every formula on the INDEX MATCH sheet with its formula shown in column M, and a Your turn sheet.

1. The table

The same eleven students as the XLOOKUP chapter: roll (A), name (B), maths (C), science (D), english (E), total (F), result (G), percentage (H), data in rows 5 to 15. Two different students are called Salman: roll 104 (Pass) and roll 111 (Fail).

2. MATCH: where is it?

MATCH does not return a value. It returns a position: "the value is the n-th item in this range".

=MATCH(lookup_value, lookup_array, match_type)

=MATCH("Zoya",$B$5:$B$15,0)

→ 6. Zoya is the 6th name in B5:B15. Her sheet row is 10, but MATCH counts inside the range you give it, starting from 1. If the range started at B4 (the header), the answer would be 7.

match_type 0 = exact match. Always type the 0. If you leave it out, MATCH uses 1 (approximate, for sorted data) and can give a wrong position without any error.

MATCH returns a position inside the range B 4 name 5 Ravindra 6 Shraddha 7 Ruhi 8 Salman 9 Shahrukh 10 Zoya 11 Amir 12 Ravina 13 Raja 14 Rani 15 Salman 1 2 3 4 5 6 7 8 9 10 11 Red = position in B5:B15. Grey = sheet row. =MATCH("Zoya",$B$5:$B$15,0) → 6 (sheet row 10, but item 6 of the range) =MATCH("Zoya",$B$4:$B$15,0) → 7 (header counted as 1) 0 = exact match Always type 0 for names and IDs. Without it MATCH assumes a sorted list.

Figure 1. MATCH counts inside the range: Zoya is item 6 of B5:B15, even though she sits in sheet row 10.

3. INDEX: what is at that position?

INDEX works the other way round: give it a range and a position, and it returns the value there.

=INDEX($B$5:$B$15,6)

→ Zoya. For a single column you only need the row position. For a block you can add a column position too: =INDEX($C$5:$E$15,6,2) → 90, Zoya's science mark (row 6, column 2 of C:E).

4. INDEX + MATCH together

MATCH finds where, INDEX fetches what. Put MATCH inside INDEX as the position:

=INDEX($G$5:$G$15,MATCH(J7,$B$5:$B$15,0))

With Zoya in J7:

  1. MATCH(J7,$B$5:$B$15,0) → 6.
  2. INDEX($G$5:$G$15,6) → Pass.

Change the first range to $H$5:$H$15 for the percentage: 80.33. Change the name and both parts update. Give J7 a drop-down list (Data › Data Validation › List, Source =$B$5:$B$15) and you have a search box that works in any version.

MATCH finds the position, INDEX fetches the value B G 4 name result 5 Ravindra Pass 6 Shraddha Pass 7 Ruhi Fail 8 Salman Pass 9 Shahrukh Fail 10 Zoya Pass 11 Amir Pass 12 Ravina Fail 13 Raja Pass 14 Rani Pass 15 Salman Fail Step 1 MATCH(J7,$B$5:$B$15,0) Zoya → position 6 Step 2 INDEX($G$5:$G$15,6) item 6 → Pass Together: =INDEX($G$5:$G$15, MATCH(J7,$B$5:$B$15,0)) → Pass Return column on the left? Only INDEX changes: =INDEX($A$5:$A$15,MATCH(J9,$B$5:$B$15,0)) → 106 Works in every Excel version.

Figure 2. MATCH finds the position of Zoya in the name column (6); INDEX returns item 6 of the result column: Pass.

5. Lookup to the left

VLOOKUP can't return a column to the left of the search column. INDEX+MATCH doesn't care, because the two ranges are separate:

=INDEX($A$5:$A$15,MATCH(J9,$B$5:$B$15,0))

Zoya → 106, her roll number from column A.

Classroom line

One alternative to XLOOKUP is the INDEX and MATCH functions.

Ravindra Bagale's Tip

The two ranges in INDEX+MATCH must start on the same row and have the same size: $A$5:$A$15 with $B$5:$B$15, never $A$4:$A$15 with $B$5:$B$15. If one starts a row higher, every answer comes from the row above, with no error to warn you.

6. A two-way lookup: row and column

Use one MATCH for the row (which student) and another for the column (which subject):

=INDEX($C$5:$E$15,MATCH("Zoya",$B$5:$B$15,0),MATCH(J10,$C$4:$E$4,0))

With science in J10: the row MATCH gives 6, the column MATCH finds "science" in the headers C4:E4 at position 2, and INDEX returns 90. Type english and you get 84. One formula answers "any student, any subject".

Two-way lookup: a row MATCH and a column MATCH B C D E 4 name maths science english 5 Ravindra 78 85 66 6 Shraddha 92 88 90 7 Ruhi 34 71 58 8 Salman 88 35 72 9 Shahrukh 56 49 30 10 Zoya 67 90 84 11 Amir 40 62 47 12 Ravina 45 28 52 13 Raja 60 40 38 14 Rani 81 76 89 15 Salman 30 55 41 =INDEX($C$5:$E$15,MATCH("Zoya",$B$5:$B$15,0), MATCH(J10,$C$4:$E$4,0)) 1 MATCH("Zoya",$B$5:$B$15,0) → row 6 2 MATCH("science",$C$4:$E$4,0) → column 2 3 INDEX($C$5:$E$15,6,2) → 90 J10 = "english" → 84. J10 = "maths" → 67. Change the name or the subject; the formula stays.

Figure 3. Two-way lookup: MATCH on the names gives row 6, MATCH on the headers gives column 2, and INDEX returns the crossing cell: 90.

7. Duplicates and XMATCH

=MATCH("Salman",$B$5:$B$15,0) → 4, the first Salman. MATCH always finds the first.

XMATCH (Excel 2021, Excel 2024, Microsoft 365 and Excel for the web) is the newer version:

  1. Exact match is the default, so =XMATCH("Salman",$B$5:$B$15) → 4 without typing 0.
  2. Its fourth argument is a search mode: -1 searches from the last item to the first.

=XMATCH("Salman",$B$5:$B$15,0,-1)

→ 11, the second Salman. Inside INDEX:

=INDEX($G$5:$G$15,XMATCH(J14,$B$5:$B$15,0,-1))

→ Fail, the result of the last Salman (roll 111).

XMATCH: exact by default, and can search from the bottom B G 4 name result 5 Ravindra Pass 6 Shraddha Pass 7 Ruhi Fail 8 Salman Pass 9 Shahrukh Fail 10 Zoya Pass 11 Amir Pass 12 Ravina Fail 13 Raja Pass 14 Rani Pass 15 Salman Fail 1 2 3 4 5 6 7 8 9 10 11 =MATCH("Salman",$B$5:$B$15,0) or =XMATCH("Salman",$B$5:$B$15) → 4: first from the top (roll 104, Pass) =XMATCH("Salman",$B$5:$B$15,0,-1) → 11: first from the bottom (roll 111, Fail) =INDEX($G$5:$G$15,XMATCH(J14,$B$5:$B$15,0,-1)) → Fail XMATCH: Excel 2021, 2024, Microsoft 365 and Excel for the web.

Figure 4. MATCH finds the first Salman at position 4. XMATCH with search mode -1 starts from the bottom and finds position 11.

A lookup always returns one answer. To list every Salman with his result, use FILTER (next chapter).

8. Not found

=MATCH("Shradha",$B$5:$B$15,0) → #N/A, because the misspelt name is not in the list, and INDEX passes the error on. Wrap the whole formula in IFNA:

=IFNA(INDEX($G$5:$G$15,MATCH(J16,$B$5:$B$15,0)),"Not found")

Classroom line

If your Excel doesn't have XLOOKUP, remember these functions; they can be asked in interviews.

9. Formatting changes the look, not the value

Zoya's percentage shows 80.33 because of the number format, but INDEX returns the stored value, 80.3333… If you need the value itself rounded (for a report or for an exact search), use =ROUND(F5/300*100,2) in the percentage column. Formatting with Ctrl+1 only changes how a number looks.

10. INDEX+MATCH vs VLOOKUP vs XLOOKUP

VLOOKUP INDEX+MATCH XLOOKUP
Excel versions all all 2021, 2024, Microsoft 365, web
Return a column on the left no yes yes
Column number to count yes no no
Inserting a column breaks it can (number shifts) no no
Last match no with XMATCH -1 search_mode -1
Not found message IFNA IFNA built in

Ravindra Bagale's Tip

In an interview, explain INDEX+MATCH in one sentence: "MATCH finds the position of the value, and INDEX returns the value at that position from the column I want." Then write it on the board. Interviewers care more about that clear sentence than about the formula itself.

11. The whole chapter on one page

  1. =MATCH(value, range, 0) returns a position inside the range.
  2. =INDEX(range, position) returns the value at that position.
  3. =INDEX(return column, MATCH(value, search column, 0)) = a lookup in every Excel version.
  4. Return columns can be on the left.
  5. Two MATCHes inside INDEX = a two-way lookup.
  6. XMATCH: exact by default; search mode -1 finds the last match.
  7. Both ranges must start on the same row and be the same size.
  8. IFNA shows "Not found" instead of #N/A.

12. Try at home

Try at home

Task 1

  1. Find Rani's position with MATCH (10), then her total with INDEX+MATCH (246).

Task 2

  1. Return Shahrukh's roll number with INDEX+MATCH (105).

Task 3

  1. Two-way lookup: Raja's maths mark (60). Then change the subject cell to english (38).

Task 4

  1. Show the percentage of the last Salman with INDEX and XMATCH (42.00).

Recap

In short

  1. MATCH = where (a position). INDEX = what (a value).
  2. INDEX+MATCH works in every version of Excel.
  3. It looks left as easily as right.
  4. Two MATCHes = row and column lookup.
  5. XMATCH -1 finds the last match.
  6. Always use 0 for an exact MATCH.

Quick revision cards

Quick revision

Revise this chapter in two minutes. Save the cards on your phone or download them as PNG files. All names and numbers are made up for practice.

Revision card: INDEX, MATCH and XMATCH. Example data table with Where is Shahrukh?: 2; First person from Pune: Amir; Position of the last Pune: 4.
Card 1 of 2: INDEX, MATCH and XMATCH · Download PNG
Revision card: INDEX+MATCH vs VLOOKUP vs XLOOKUP. Covers Looks left; Exact by default; Old Excel; Two-way lookup. With a photo of Ravindra Bagale and a short tip.
Card 2 of 2: INDEX+MATCH vs VLOOKUP vs XLOOKUP · Download PNG

More cards for every chapter: Ravi Sir's Cards.

Loading practice tasks...

  1. VLOOKUP name (Easy): Name for ID "P103" with VLOOKUP.
  2. XLOOKUP value (Easy): The Price for ID "P103".
  3. Position (Easy): Position of "Emma Schmidt" in B2:B9.
  4. INDEX and MATCH (Medium): ID for name "Emma Schmidt" using INDEX and MATCH.
  5. Not found message (Medium): Units sold for ID "Z999", or "Not found".
  6. IFNA with VLOOKUP (Medium): VLOOKUP of "Z999" in A2:E9 column 4, or "Missing".
  7. XMATCH (Medium): Position of ID "P103" in A2:A9.
  8. Slab rate (Hard): Rate for E2 from the slab table G2:H6.
  9. Slab amount (Hard): E3 times its slab rate.
  10. Two-way lookup (Hard): Value in A2:E9 at the row of "Emma Schmidt" and column "Units sold".
  11. Lookup the top (Hard): Name with the largest Units sold.

See all practice tasks in the Task Hub →