Search code examples
excelmatchxlookup

How to return a specific text if a cell contains specififc text using XLOOKUP?

How to return corresponding texts from column E to column B. I know it is possible by the following lookup formula:

enter image description here

But since Excel has XLOOKUP right now and it being capable of whatever lookup can do, please explain how the same can be done with XLOOKUP.

Solution

  • The LOOKUP Function has the following syntax:

    LOOKUP(lookup_value, lookup_vector, [result_vector])

    The XLOOKUP Function has the following syntax:

    XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

    As such, you should be able to see that the first 3 parameters of XLOOKUP correspond to the three parameters of LOOKUP, with the primary difference that result_vector/return_array is no longer optional. If it was omitted from your LOOKUP, then you would need to replace it with either a copy of lookup_vector/lookup_array (if one of the dimensions of your array was 1), or with the last row/column of lookup_vector/lookup_array.

    (For arrays where neither dimension was 1, this gives more control — LOOKUP would swap between Horizontal or Vertical lookups depending on whether the array was wider than it was tall or not. This is part of why HLOOKUP and VLOOKUP were preferred, especially for dynamically sized arrays)

    As such, you might think that you should be able to just change =LOOKUP(..) to =XLOOKUP(..) and be done with it… except you would be wrong.

    The issue is in those 3 new optional arguments — specifically, in the second one, match_mode. This has 4 possible values:

    match_mode (Optional) Specify the match type:
    ​ 0 - Exact match. If none found, return #N/A. This is the default.
    -1 - Exact match. If none found, return the next smaller item.
    ​ 1 - Exact match. If none found, return the next larger item.
    ​ 2 - A wildcard match where *, ?, and ~ have special meaning.

    You see, LOOKUP functioned as "if none found, return the next smaller item", while XLOOKUP defaults instead to "if none found, return #N/A".

    This means that, in order to make XLOOKUP behave like LOOKUP used to, you need to specify a match_mode of -1. Like so:

    =XLOOKUP(1.1,1/COUNTIF(A1,"*"&$D$1:$D$3&"*"),$E$1:$E$3,,-1)

    (You may also notice that I have left if_not_found unspecified, so that it stays as the default value — this is done by putting the 2 commas with nothing between them)