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)

Compliant responsive placement adhering to Google Publisher Policies. Ads will display here upon adding your Publisher ID in .env.local.

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()