Xlookup - not found on the desktop version

Maxmardig 

If the XLOOKUP function is not available because it is restricted to Microsoft 365 users and you need a non-VBA alternative that works on Excel versions prior to Microsoft 365, you can use a combination of INDEX and MATCH functions to achieve similar functionality. Here is how you can replace XLOOKUP with these functions:

Example Scenario

Assuming you have the following data:

• lookup_value is in cell A1
• lookup_array is in range B1:B10
• return_array is in range C1:C10

XLOOKUP Equivalent Using INDEX and MATCH

The general formula for XLOOKUP:

=XLOOKUP(A1, B1:B10, C1:C10, "Not Found")

Equivalent Formula Using INDEX and MATCH

You can use the following formula to achieve the same result:

=IFERROR(INDEX(C1:C10, MATCH(A1, B1:B10, 0)), "Not Found")

Explanation

• MATCH(A1, B1:B10, 0) finds the position of A1 in the range B1:B10.
• INDEX(C1:C10, ...) uses this position to find the corresponding value in the range C1:C10.
• IFERROR(..., "Not Found") handles cases where the lookup value is not found, similar to the [if_not_found] argument in XLOOKUP.

Steps to Implement the Formula

1. Identify the ranges for your lookup value, lookup array, and return array.
2. Use the INDEX and MATCH formula with the ranges identified.
3. Wrap the formula with IFERROR to handle not found cases.

Example with Real Data

Here is an example with some data:

• Lookup value is in cell A1 with the value "apple"
• Lookup array is in cells B1:B5 containing {"apple", "banana", "cherry", "date", "elderberry"}
• Return array is in cells C1:C5 containing {1, 2, 3, 4, 5}

In cell D1, you would enter:

=IFERROR(INDEX(C1:C5, MATCH(A1, B1:B5, 0)), "Not Found")

Handling Multiple Conditions (Advanced Use Case)

If you need to perform a lookup with multiple conditions, you can use an array formula (CTRL+SHIFT+ENTER on Windows or CMD+RETURN on Mac) to handle the criteria.

For example, if you need to find the value in C1:C10 based on two criteria in A1 and A2 corresponding to ranges B1:B10 and D1:D10:

=IFERROR(INDEX(C1:C10, MATCH(1, (B1:B10=A1)*(D1:D10=A2), 0)), "Not Found")

Remember to enter this as an array formula.

Conclusion

Using INDEX and MATCH functions provides a versatile and powerful way to perform lookups in Excel, which is compatible with older versions of Excel that do not support XLOOKUP. This method ensures that your formulas remain functional regardless of the Excel version you or your colleagues are using. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.