Mr Excel TamilExcel • PPT • Word • 500+ fx
100% Free MasterclassLookup & ReferenceBeginner Level

=XLOOKUP() Interactive Masterclass & Practice Simulator

Searches a range or an array for a match and returns the corresponding item from a second range or array.

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

Live Interactive Excel Spreadsheet (Full Width)

Type directly in the cells or use the formula bar (fx)
X
Excel Live Grid: =XLOOKUP() Practice Dataset
E2
fx
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

lookup_value: The value to search for.

🎯 Active Cell: Enter any formula or value into cell E2! Try =XLOOKUP("EMP-103", A2:A6, C2:C6)
Sheet: Sheet 1 (Practice Table)
#AEmp ID (A)BEmployee Name (B)CDepartment (C)DSalary ($) (D)EOutput: =XLOOKUP() (E)FColumn FGColumn GHColumn H
2
3
4
5
6
7
8
9
10
Formula Assistant
Cell: E2

Excel Calculation Rules:

  • Click on any cell across the entire sheet.
  • Type =B2*C2, =XLOOKUP(...), or any math!
  • Click other cells to insert cell addresses automatically.
  • Press Enter or click ✓ to evaluate live!

⚡ Pro Keyboard Shortcuts:

Enter CalculateTab Next CellF2 Edit CellEsc Cancel
Ready • Any Cell Formula Engine•Zoom: 100%
Advertisement Google AdSense Verified Slot

Google AdSense Responsive Unit (horizontal)

Publisher ID: ca-pub-7444400132160337. Ad unit ready for live ad serving upon Google approval.

Graded Practice Challenges (1)

Free & Unlocked
1

Exercise 1: Practice =XLOOKUP() in Live Simulator

+50 XP

🎯 Target Goal:

Lookup Department for Employee "EMP-103"

Syntax Parameters Breakdown

lookup_valueRequired

The value to search for.

lookup_arrayRequired

The array or range to search.

return_arrayRequired

The array or range to return.

[if_not_found]Optional

Where a valid match is not found, return this value.

[match_mode]Optional

0 for exact (default), -1 for next smaller, 1 for next larger, 2 for wildcard.

[search_mode]Optional

1 for first-to-last (default), -1 for last-to-first.

Top Pitfalls to Avoid:

  • Forgetting to close the parenthesis ) at the end of the formula.
  • Mismatching cell range sizes in lookup vectors or criteria arrays.

Corporate Pro Tips:

  • XLOOKUP defaults to exact match (no need for FALSE/0 like VLOOKUP).
  • Can look left or right without column index numbers.
  • Provide a custom 4th argument for "Not Found" message if match doesn't exist.
View All 151+ FunctionsNext: =VLOOKUP()