Monday, 27 January 2025

The XLOOKUP function in Excel 20 examples

 The XLOOKUP function in Excel is a powerful tool for searching and retrieving data from a table or range. It replaces older functions like VLOOKUP, HLOOKUP, and INDEX/MATCH with more flexibility and simplicity. Below are 20 examples of how to use the XLOOKUP function:


Basic Syntax

=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 to search in.

  • return_array: The range or array to return a value from.

  • [if_not_found]: (Optional) What to return if no match is found.

  • [match_mode]: (Optional) Specify the type of match (exact, wildcard, etc.).

  • [search_mode]: (Optional) Specify the search direction (first-to-last, last-to-first, etc.).


Examples

1. Basic Exact Match

Look up the price of a product by its ID.

excel
Copy
=XLOOKUP("A123", A2:A10, B2:B10)
  • Searches for "A123" in A2:A10 and returns the corresponding value from B2:B10.


2. Handle Not Found

Return "Not Found" if the lookup value doesn't exist.

excel
Copy
=XLOOKUP("XYZ", A2:A10, B2:B10, "Not Found")

3. Case-Insensitive Match

Look up a value without case sensitivity.

excel
Copy
=XLOOKUP("apple", A2:A10, B2:B10, , 0)

4. Wildcard Match

Use wildcards (* or ?) for partial matches.

excel
Copy
=XLOOKUP("app*", A2:A10, B2:B10)
  • Finds the first value in A2:A10 that starts with "app".


5. Search from Bottom to Top

Search the lookup array from the last item to the first.

excel
Copy
=XLOOKUP("A123", A2:A10, B2:B10, , , -1)

6. Return Multiple Columns

Return values from multiple columns.

excel
Copy
=XLOOKUP("A123", A2:A10, B2:D10)
  • Returns the entire row from columns B to D.


7. Lookup with Multiple Criteria

Use concatenation or arrays for multiple criteria.

excel
Copy
=XLOOKUP(1, (A2:A10="Category1")*(B2:B10="Subcategory1"), C2:C10)

8. Approximate Match

Find the closest match for a numeric value.

excel
Copy
=XLOOKUP(85, A2:A10, B2:B10, , 1)
  • Returns the closest value less than or equal to 85.


9. Reverse Lookup

Look up a value in a column and return a value from a row.

excel
Copy
=XLOOKUP("A123", A2:A10, B1:D1)

10. Dynamic Array Return

Return multiple values using dynamic arrays.

excel
Copy
=XLOOKUP("A123", A2:A10, B2:D10)
  • Returns all matching rows in columns B to D.


11. Lookup with Dates

Find a value based on a date.

excel
Copy
=XLOOKUP(DATE(2023,10,15), A2:A10, B2:B10)

12. Nested XLOOKUP

Use XLOOKUP inside another XLOOKUP.

excel
Copy
=XLOOKUP("A123", A2:A10, XLOOKUP("Category1", B2:B10, C2:C10))

13. Lookup with Logical Operators

Use logical operators in the lookup array.

excel
Copy
=XLOOKUP(TRUE, A2:A10>50, B2:B10)
  • Returns the first value in B2:B10 where A2:A10 is greater than 50.


14. Lookup with Array Input

Look up multiple values at once.

excel
Copy
=XLOOKUP({"A123","B456"}, A2:A10, B2:B10)
  • Returns an array of results for "A123" and "B456".


15. Lookup with Spill Range

Return results that spill into adjacent cells.

excel
Copy
=XLOOKUP("A123", A2:A10, B2:D10)
  • Spills results into multiple cells.


16. Lookup with Error Handling

Handle errors gracefully.

excel
Copy
=XLOOKUP("A123", A2:A10, B2:B10, IFERROR("Not Found", "Error"))

17. Lookup with Array Formula

Use XLOOKUP in an array formula.

excel
Copy
=XLOOKUP(A2:A10, D2:D10, E2:E10)
  • Returns an array of results for each value in A2:A10.


18. Lookup with Conditional Formatting

Highlight cells based on XLOOKUP results.

excel
Copy
=XLOOKUP(A2, A2:A10, B2:B10) > 100
  • Use this in conditional formatting to highlight cells where the result is greater than 100.


19. Lookup with Text Functions

Combine XLOOKUP with text functions like LEFT or RIGHT.

excel
Copy
=XLOOKUP(LEFT(A2, 3), A2:A10, B2:B10)
  • Looks up the first 3 characters of A2 in A2:A10.


20. Lookup with Dynamic Ranges

Use XLOOKUP with dynamic named ranges.

excel
Copy
=XLOOKUP("A123", MyDynamicRange, B2:B10)
  • MyDynamicRange is a named range that adjusts automatically.


These examples demonstrate the versatility of XLOOKUP in Excel. You can adapt these formulas to suit your specific data and requirements!

include data

Sure! Let’s go through 5 practical examples of the XLOOKUP function with sample data included. This will help you understand how to apply XLOOKUP in real-world scenarios.


Example 1: Basic Exact Match

Task: Look up the price of a product based on its ID.

Product IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=XLOOKUP("B456", A2:A4, C2:C4)

Result0.50 (the price of Banana).


Example 2: Handle Not Found

Task: Look up the price of a product, but return "Not Found" if the product doesn’t exist.

Product IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=XLOOKUP("XYZ", A2:A4, C2:C4, "Not Found")

ResultNot Found (since "XYZ" doesn’t exist in the list).


Example 3: Wildcard Match

Task: Find the price of a product whose name starts with "App".

Product IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=XLOOKUP("App*", B2:B4, C2:C4)

Result1.20 (the price of Apple).


Example 4: Approximate Match

Task: Find the discount percentage for a given purchase amount.

Purchase AmountDiscount %
00%
1005%
50010%
100015%

Formula:

excel
Copy
=XLOOKUP(750, A2:A5, B2:B5, , 1)

Result10% (the closest match less than or equal to 750).


Example 5: Return Multiple Columns

Task: Retrieve the product name and price for a given product ID.

Product IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=XLOOKUP("C789", A2:A4, B2:C4)

ResultCherry and 2.00 (the product name and price for "C789").


Example 6: Lookup with Multiple Criteria

Task: Find the price of a product in a specific category.

CategoryProduct NamePrice
FruitApple1.20
FruitBanana0.50
VegetableCarrot0.80

Formula:

excel
Copy
=XLOOKUP(1, (A2:A4="Fruit")*(B2:B4="Banana"), C2:C4)

Result0.50 (the price of Banana in the Fruit category).


Example 7: Lookup with Dates

Task: Find the sales amount for a specific date.

DateSales Amount
2023-10-01500
2023-10-02700
2023-10-03600

Formula:

excel
Copy
=XLOOKUP(DATE(2023,10,2), A2:A4, B2:B4)

Result700 (the sales amount for October 2, 2023).


Example 8: Lookup with Spill Range

Task: Retrieve all details for a specific product ID.

Product IDProduct NamePriceCategory
A123Apple1.20Fruit
B456Banana0.50Fruit
C789Cherry2.00Fruit

Formula:

excel
Copy
=XLOOKUP("B456", A2:A4, B2:D4)

ResultBanana0.50, and Fruit (all details for "B456").


Example 9: Lookup with Array Input

Task: Look up prices for multiple product IDs at once.

Product IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Formula:

excel
Copy
=XLOOKUP({"A123","C789"}, A2:A4, C2:C4)

Result1.20 and 2.00 (prices for "A123" and "C789").


Example 10: Lookup with Dynamic Ranges

Task: Look up the price of a product using a dynamic named range.

Product IDProduct NamePrice
A123Apple1.20
B456Banana0.50
C789Cherry2.00

Steps:

  1. Create a named range (e.g., Products) for A2:A4.

  2. Use the formula:

excel
Copy
=XLOOKUP("B456", Products, C2:C4)

Result0.50 (the price of Banana).


These examples with data should help you understand how to use XLOOKUP effectively in Excel

No comments:

Post a Comment