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

Excel XLOOKUP Tutorial: Search Right to Left, Last Match, Not Found Message and Drop-Down List

By Last updated:

VLOOKUP has three limits: it only looks to the right, it only returns the first match, and it shows #N/A when nothing is found. XLOOKUP removes all three. It searches in any direction, can start from the bottom to find the last match, and lets you write your own "Not found" message. In this chapter we use the same student table as the VLOOKUP chapter and finish with a drop-down list, so nobody ever has to type a name again.

What you'll learn in this chapter

  • The six arguments of XLOOKUP
  • A basic lookup with three arguments
  • Lookup to the left (right to left)
  • A custom message instead of #N/A
  • First or last match with search_mode
  • Who has the highest percentage
  • Decimals: why an exact match fails, and match_mode 1 and -1
  • Wildcards with match_mode 2
  • A drop-down list with Data Validation

Practice file: excel-xlookup-practice.xlsx: every formula on the XLOOKUP sheet with its formula shown next to it, and a Your turn sheet.

Which Excel has XLOOKUP? Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. In older versions use INDEX and MATCH, which does the same jobs.

1. The table

Eleven students in A5:H15: roll (A), name (B), maths, science, english (C:E), total (F), result (G), percentage (H). Two students are called Salman: roll 104 (195, Pass, 65.00) near the top and roll 111 (126, Fail, 42.00) at the bottom.

2. The six arguments

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

  1. lookup_value: what you search for.
  2. lookup_array: the one column (or row) to search in.
  3. return_array: the column (or row) to return from. It can be anywhere: left, right, even on another sheet.
  4. if_not_found (optional): your message when nothing matches.
  5. match_mode (optional): 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
  6. search_mode (optional): 1 first to last (default), -1 last to first.

Only the first three are required. Notice there is no column number to count and no FALSE to remember: exact match is the default.

XLOOKUP: three required, three optional arguments 1 lookup_value what you search for J5 2 lookup_array one column to search B5:B15 3 return_array column(s) to return F5:F15 4 if_not_found your message "Not found" 5 match_mode 0 exact (default), -1 next smaller, 1 next larger, 2 wildcard 6 search_mode 1 first to last (default), -1 last to first Blue = required. Orange = optional. No column number to count; exact match is the default.

Figure 1. XLOOKUP takes a search column and a return column separately, so there is no column number to count and exact match is the default.

3. A basic lookup

With Zoya in J5:

=XLOOKUP(J5,$B$5:$B$15,$F$5:$F$15)

→ 241. Change the return column to $G$5:$G$15 for Pass, or $H$5:$H$15 for 80.33.

Classroom line

XLOOKUP works both ways: left to right, right to left, top to bottom, bottom to top.

4. Right to left: return a column on the left

The roll number is in column A, to the left of the names. VLOOKUP can't return it. XLOOKUP can:

=XLOOKUP(J6,$B$5:$B$15,$A$5:$A$15)

Zoya → 106. The lookup and return columns are separate arguments, so their order on the sheet does not matter.

Return a column on the LEFT A B C 4 roll name maths 5 101 Ravindra 78 6 102 Shraddha 92 7 103 Ruhi 34 8 104 Salman 88 9 105 Shahrukh 56 10 106 Zoya 67 11 107 Amir 40 12 108 Ravina 45 13 109 Raja 60 14 110 Rani 81 15 111 Salman 30 Yellow: found in B. Green: returned from A. =XLOOKUP("Zoya",$B$5:$B$15,$A$5:$A$15) → 106 VLOOKUP: the range must start at the name column, so A is out of reach Search column and return column are separate So their order on the sheet does not matter: left, right, above, below, or another sheet.

Figure 2. XLOOKUP searches the name column (B) and returns from the roll column (A), on its left. VLOOKUP cannot do this.

5. Your own "Not found" message

=XLOOKUP(J7,$B$5:$B$15,$G$5:$G$15,"Not found")

With the misspelt Shradha the cell shows Not found instead of #N/A. You can write any text: "Check the spelling", "No such student".

6. First or last match: search_mode

Two Salmans. By default XLOOKUP searches from the top (search_mode 1) and returns the first: Pass (roll 104). Set search_mode to -1 to search from the bottom:

=XLOOKUP(J9,$B$5:$B$15,$G$5:$G$15,"Not found",0,-1)

→ Fail (roll 111). The 0 before it is match_mode (exact); you have to fill it in to reach the sixth argument.

Classroom line

It shows one of the two, either the top one or the bottom one, but only one.

The last match is useful whenever newer rows are added at the bottom: the latest price of a product, the latest status of an order. To see all matches at once, use the FILTER function.

First match or last match: search_mode A B G 4 roll name result 5 101 Ravindra Pass 6 102 Shraddha Pass 7 103 Ruhi Fail 8 104 Salman Pass 9 105 Shahrukh Fail 10 106 Zoya Pass 11 107 Amir Pass 12 108 Ravina Fail 13 109 Raja Pass 14 110 Rani Pass 15 111 Salman Fail search_mode 1: top → down search_mode -1: bottom → up =XLOOKUP(J8,B5:B15,G5:G15,"Not found",0,1) → Pass (roll 104) =XLOOKUP(J9,B5:B15,G5:G15,"Not found",0,-1) → Fail (roll 111) The 0 is match_mode (exact). It must be filled in to reach the sixth argument.

Figure 3. search_mode 1 searches from the top and finds Salman (104, Pass); -1 searches from the bottom and finds Salman (111, Fail).

7. Who has the highest percentage?

MAX gives the highest percentage; XLOOKUP turns it into a name:

=XLOOKUP(MAX($H$5:$H$15),$H$5:$H$15,$B$5:$B$15)

→ Shraddha. The lookup value can be a formula, not just a typed value.

8. Decimals: why an exact match says "Not found"

Ravindra's percentage shows 76.33, so you type 76.33 and search the percentage column:

=XLOOKUP(J11,$H$5:$H$15,$B$5:$B$15,"Not found")

→ Not found. The cell actually stores 76.333333…; the two decimals are only a display format (Ctrl+1). 76.33 is not equal to 76.3333…, so the exact match fails.

Classroom line

Not found. How can it be not found? It is right there!

Three ways to handle it:

  1. match_mode 1, exact or next larger: =XLOOKUP(J12,$H$5:$H$15,$B$5:$B$15,"Not found",1). The smallest value that is 76.33 or more is 76.3333…, so you get Ravindra.
  2. ROUND the column inside the formula: =XLOOKUP(J14,ROUND($H$5:$H$15,2),$B$5:$B$15,"Not found") compares 76.33 with rounded values: Ravindra.
  3. Round the stored value: write the percentage as =ROUND(F5/300*100,2) so the cell really holds 76.33.

match_mode -1, exact or next smaller, answers a different question: "who is closest to 80% without going over?" =XLOOKUP(80,$H$5:$H$15,$B$5:$B$15,"Not found",-1) → Ravindra (76.33 is the largest percentage that is not above 80). Unlike VLOOKUP's TRUE, XLOOKUP's match modes -1 and 1 do not need the column to be sorted.

Shown 76.33, stored 76.333333… Cell H5 shows: 76.33 Formula Bar: 76.3333333333333 =XLOOKUP(76.33,H5:H15,B5:B15,"Not found") → Not found exact: 76.33 ≠ 76.3333… =XLOOKUP(76.33,H5:H15,B5:B15,"Not found",1) → Ravindra exact or next larger =XLOOKUP(76.33,ROUND(H5:H15,2),B5:B15,"Not found") → Ravindra compare rounded values =XLOOKUP(80,H5:H15,B5:B15,"Not found",-1) → Ravindra closest without going over 80 Ctrl+1 changes the look, not the value. Unlike VLOOKUP TRUE, match modes 1 and -1 need no sorting.

Figure 4. The cell shows 76.33 but stores 76.3333…, so an exact search for 76.33 fails. match_mode 1 (next larger) or ROUND finds Ravindra.

Ravindra Bagale's Tip

When a lookup says "not found" but you can see the value, click the cell and look at the Formula Bar. It shows what is really stored: extra decimals, a space at the end, or a number stored as text. The Formula Bar never lies; the cell display sometimes does.

9. Wildcards: match_mode 2

With match_mode 2, * means "any characters" and ? means "one character":

=XLOOKUP("Ra*",$B$5:$B$15,$B$5:$B$15,"Not found",2)

→ Ravindra, the first name that starts with "Ra". XLOOKUP still returns only one match; Ravina, Raja and Rani are ignored. For all of them, see FILTER with LEFT.

10. A drop-down list of names

Typing names leads to typos and #N/A. A drop-down list lets the user pick a name instead.

How:

  1. Select the input cell J5.
  2. Data › Data Tools › Data Validation.
  3. Allow: List.
  4. Source: =$B$5:$B$15.
  5. Click OK.

An arrow appears next to J5. Pick any name, and every XLOOKUP that reads J5 updates at once. Salman appears twice in the list because he is in the table twice; on real data, use a list of unique IDs.

Classroom line

Select the cell where you want the list, then Data menu, Data Tools, Data Validation.

A drop-down list with Data Validation 1 Select J5 2 Data › Data Tools › Data Validation 3 Allow: List 4 Source: =$B$5:$B$15 5 OK Data Validation Allow: List Source: =$B$5:$B$15 Zoya ▾ Ravindra Shraddha Ruhi ← pick a name K5, L5 … update at once No typing, no spelling mistakes, no #N/A.

Figure 5. Data › Data Validation › Allow: List, Source =$B$5:$B$15 puts a drop-down arrow on J5. Pick a name and the lookups update.

Ravindra Bagale's Tip

Build every lookup sheet as a small "search box": one input cell with a drop-down list, and the answers next to it. It looks professional, prevents spelling mistakes, and is exactly the kind of thing interviewers ask you to build live.

11. The whole chapter on one page

  1. =XLOOKUP(value, search column, return column): exact match by default.
  2. The return column can be on the left: right-to-left lookup.
  3. 4th argument: your own "Not found" text.
  4. 6th argument: 1 = first from the top, -1 = last from the bottom.
  5. Lookup value can be a formula: MAX(H5:H15) → who has the top percentage.
  6. Displayed decimals ≠ stored decimals: use match_mode 1/-1 or ROUND.
  7. match_mode 2 = wildcards, still one result.
  8. Data › Data Validation › List gives a drop-down input.
  9. Excel 2021, 2024, Microsoft 365 and Excel for the web; INDEX+MATCH in older versions.

12. Try at home

Try at home

Task 1: search box

  1. Make a drop-down in J5 and XLOOKUPs for roll, total, result and percentage.

Task 2: both Salmans

  1. Show the first Salman's total and the last Salman's total side by side (195 and 126).

Task 3: lowest percentage

  1. Use MIN inside XLOOKUP to find who has the lowest percentage. (Ravina, 41.67.)

Task 4: decimals

  1. Search 80.33 with exact match, with match_mode 1 and with ROUND. Which ones find Zoya?

Recap

In short

  1. XLOOKUP = search column + return column, exact by default.
  2. It can return a column on the left.
  3. if_not_found replaces #N/A with your message.
  4. search_mode -1 gives the last match.
  5. Stored decimals can make an exact match fail.
  6. A drop-down list removes typing mistakes.

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: XLOOKUP: first match, last match, not found. Example data table with Raja's last entry: ₹520; Raja's first entry: ₹300; A name not in A2:A4: Not found.
Card 1 of 2: XLOOKUP: first match, last match, not found · Download PNG
Revision card: XLOOKUP: left columns, wildcards, decimals. Example data table with Ravina's ID (column to the left): 101; ID for a name starting with Sha: 102; Find score 72.5: Ravina.
Card 2 of 2: XLOOKUP: left columns, wildcards, decimals · Download PNG

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

Loading practice tasks...

  1. VLOOKUP name (Easy): Name for ID "S103" with VLOOKUP. (sheet "Lookup")
  2. XLOOKUP value (Easy): The Monthly bill for ID "S103". (sheet "Lookup")
  3. Position (Easy): Position of "Neha Gupta" in B2:B9. (sheet "Lookup")
  4. Clean name (Easy): Trim A2. (sheet "Text")
  5. Proper case (Easy): A2 trimmed and in proper case. (sheet "Text")
  6. Code prefix (Easy): First 3 characters of B2. (sheet "Text")
  7. Code number (Easy): Last 4 characters of B3. (sheet "Text")
  8. INDEX and MATCH (Medium): ID for name "Neha Gupta" using INDEX and MATCH. (sheet "Lookup")
  9. Not found message (Medium): Data GB for ID "Z999", or "Not found". (sheet "Lookup")
  10. IFNA with VLOOKUP (Medium): VLOOKUP of "Z999" in A2:E9 column 4, or "Missing". (sheet "Lookup")
  11. City part (Medium): Characters 5 to 7 of B4. (sheet "Text")
  12. Name length (Medium): Length of A3. (sheet "Text")

See all practice tasks in the Task Hub →