Xlookup by SeekheIndia

Syntax of XLOOKUP

The syntax of XLOOKUP is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value you want to search for.
• lookup_array: The range or array where you want to search for the lookup_value.
• return_array: The range or array that contains the values you want to return.
• [if_not_found] (Optional): The value to return if no match is found.
• [match_mode] (Optional): Defines the type of match (exact or approximate).
• [search_mode] (Optional): Defines the search mode (search from the first item or last item).

Examples for Each Argument

1. Lookup Value

Example: Find an employee’s salary based on their ID.

• Data:
  • Employee ID: 101, 102, 103, 104
  • Salary: 5000, 6000, 5500, 7000
• Formula:
  =XLOOKUP(103, A1:A4, B1:B4)
  • lookup_value: 103 (Employee ID to search)
  • lookup_array: A1

(Employee IDs)

• return_array: B1

(Salaries)

• Result: 5500

2. Lookup Array

Example: Find a product’s price based on its name.

• Data:
  • Product Name: Apple, Banana, Cherry, Date
  • Price: 1.00, 0.50, 2.00, 1.50
• Formula:
  =XLOOKUP(“Cherry”, C1:C4, D1:D4)
  • lookup_value: “Cherry” (Product name to search)
  • lookup_array: C1

(Product names)

• return_array: D1

(Prices)

• Result: 2.00

3. Return Array

Example: Find the employee’s department based on their name.

• Data:
  • Employee Name: Alice, Bob, Carol, Dave
  • Department: HR, Finance, IT, Marketing
• Formula:
  =XLOOKUP(“Carol”, E1:E4, F1:F4)
  • lookup_value: “Carol” (Employee name to search)
  • lookup_array: E1

(Employee names)

• return_array: F1

(Departments)

• Result: IT

4. If Not Found

Example: Return a custom message if a product is not found.

• Data:
  • Product: Laptop, Mouse, Keyboard, Monitor
  • Price: 800, 20, 50, 150
• Formula:
  =XLOOKUP(“Printer”, G1:G4, H1:H4, “Product Not Found”)
  • lookup_value: “Printer” (Product to search)
  • lookup_array: G1

(Products)

• return_array: H1

(Prices)

• [if_not_found]: “Product Not Found” (Custom message)
• Result: “Product Not Found”

5. Match Mode

Example: Find the closest match if an exact match is not found.

• Data:
  • Scores: 50, 60, 70, 80
  • Grade: F, D, C, B
• Formula:
  =XLOOKUP(75, I1:I4, J1:J4, “Not Found”, 1)
  • lookup_value: 75 (Score to search)
  • lookup_array: I1

(Scores)

• return_array: J1

(Grades)

• [match_mode]: 1 (Find the closest match greater than or equal to 75)
• Result: B

6. Search Mode

Example: Search from the end of the list.

• Data:
  • Task: Task A, Task B, Task C, Task D
  • Completion Date: 01/01/2023, 05/01/2023, 10/01/2023, 15/01/2023
• Formula:
  =XLOOKUP(“Task B”, K1:K4, L1:L4, “Not Found”, 0, -1)
  • lookup_value: “Task B” (Task to search)
  • lookup_array: K1

(Tasks)

• return_array: L1

(Completion Dates)

• [search_mode]: -1 (Search from last to first)
• Result: 05/01/2023

Practice Assignment

1. Create a Table:
   • Set up a table with various data points such as employee IDs, names, products, and prices.
2. Apply XLOOKUP:
   • Use XLOOKUP to find specific values based on different lookup values, arrays, and match modes.
3. Analyze Results:
   • Verify the results by comparing them with the data in the table and using different optional arguments.

Pro Tip: XLOOKUP simplifies complex lookups by combining functionalities from multiple older functions into one versatile formula. For more advanced Excel tips and to enhance your skills, Anjni Computer Education offers comprehensive guides and tutorials.