Looking Up Values In Excel – xlookup

What is XLOOKUP?

XLOOKUP is a versatile lookup function introduced in newer versions of Excel (beginning with Excel 365 and Excel 2019). It allows you to search for a value in a range or array and return a corresponding value from the same position in another range or array. XLOOKUP is particularly useful for performing exact matches, as it provides greater flexibility and functionality compared to older lookup functions like VLOOKUP and HLOOKUP.

Performing Exact Matches with XLOOKUP:

Exact matching means finding an exact value in your data, without any variations or approximations. XLOOKUP makes it easy to perform exact matches by allowing you to specify the exact match parameter.

Here’s how you can use XLOOKUP to perform exact matches in Excel:

Syntax of XLOOKUP:

The basic syntax of the XLOOKUP function is as follows:

=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 Excel should search for the lookup value.
  • return_array: The range or array from which to return a corresponding value.
  • [if_not_found]: Optional. Specifies the value to return if no exact match is found.
  • [match_mode]: Optional. Specifies the type of match to perform (exact match, less than, greater than, etc.).
  • [search_mode]: Optional. Specifies the search mode (first-to-last, last-to-first, etc.).

Let’s break down the available options for both match_mode and search_mode parameters in the XLOOKUP function in Excel:

Match Modes:

  1. Exact Match (0):
    • This is the default match mode if the parameter is omitted.
    • Excel searches for the exact value specified in the lookup_value.
    • If an exact match is found, Excel returns the corresponding value.
    • If no exact match is found, Excel returns the if_not_found value (if specified) or an error.
  2. Less than (-1):
    • Excel searches for the largest value that is less than or equal to the lookup_value.
    • If found, Excel returns the corresponding value.
    • If no matching value is found, Excel returns the next smaller value.
  3. Greater than (1):
    • Excel searches for the smallest value that is greater than or equal to the lookup_value.
    • If found, Excel returns the corresponding value.
    • If no matching value is found, Excel returns the next larger value.
  4. Exact Match or Next Smaller (-2):
    • Similar to exact match mode (0), but if no exact match is found, Excel returns the next smaller value.
  5. Exact Match or Next Larger (2):
    • Similar to exact match mode (0), but if no exact match is found, Excel returns the next larger value.

Search Modes:

  1. First-to-Last (1):
    • Excel searches for the first occurrence of the lookup_value in the lookup_array.
    • If found, Excel returns the corresponding value.
    • If multiple matches are found, Excel returns the first one.
  2. Last-to-First (-1):
    • Excel searches for the last occurrence of the lookup_value in the lookup_array.
    • If found, Excel returns the corresponding value.
    • If multiple matches are found, Excel returns the last one.

These match modes and search modes provide flexibility in how you want Excel to perform the lookup operation, allowing you to tailor the behavior of the XLOOKUP function to your specific needs. Whether you’re looking for an exact match, the nearest value, or the first/last occurrence, you can choose the mode that best suits your data analysis requirements.

Performing an Exact Match:

To perform an exact match with XLOOKUP, simply set the [match_mode] parameter to 0 or omit it entirely. This tells Excel to search for an exact match only.

=XLOOKUP(lookup_value, lookup_array, return_array, 0)

For example, if you’re searching for the value “Apple” in a list of fruits, Excel will return the corresponding value from another column (e.g., the price of apples) only if “Apple” is found exactly as entered, without any variations.

Handling Non-Exact Matches:

If you want Excel to return a specific value when no exact match is found, you can specify it using the [if_not_found] parameter. For example, you could display a message like “Not found” or “N/A” when no exact match is found.

Benefits of Using XLOOKUP for Exact Matches:

  • Flexibility: XLOOKUP offers greater flexibility compared to older lookup functions, allowing you to perform exact matches more easily.
  • Accuracy: By specifying the exact match parameter, you can ensure that Excel returns precise results, eliminating the risk of errors or inaccuracies.
  • Efficiency: XLOOKUP simplifies the lookup process, saving you time and effort when searching for exact matches in your data.

Example Scenario:

Suppose we have a list of student names and their corresponding test scores in an Excel spreadsheet. We want to use the XLOOKUP function to find the test score for a specific student. However, there may be cases where the exact student name is not found in the list, and we need to handle these scenarios appropriately.

Example Usage of XLOOKUP Function:

  1. Exact Match (0):
    • =XLOOKUP("Bob", A2:A6, B2:B6, 0)
      • Returns 90, the test score for Bob.
  2. Less than (-1):
    • =XLOOKUP("Derek", A2:A6, B2:B6, , -1)
      • Returns 88, the test score for David (the largest value less than “Derek”).
  3. Greater than (1):
    • =XLOOKUP("Chris", A2:A6, B2:B6, , 1)
      • Returns 78, the test score for Claire (the smallest value greater than “Chris”).
  4. First-to-Last (1):
    • =XLOOKUP("Eve", A2:A6, B2:B6, , , 1)
      • Returns 92, the test score for Eve (the first occurrence of “Eve” in the list).
  5. Last-to-First (-1):
    • =XLOOKUP("Eve", A2:A6, B2:B6, , , -1)
      • Returns 92, the test score for Eve (the last occurrence of “Eve” in the list).

Conclusion:

In this example, we’ve demonstrated how the different match modes and search modes in the XLOOKUP function can be used to perform various lookup operations in Excel. Whether you need to find an exact match, the nearest value, or the first/last occurrence, XLOOKUP provides the flexibility to handle different scenarios efficiently and accurately.

Share: TwitterFacebookLinkedin