Find the Last Matching Value in a Range

To find the last matching value in a range using XLOOKUP, you can follow these steps:

1. Construct the XLOOKUP formula with the range and criteria. The formula syntax for XLOOKUP is as follows:cssCopy code=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
   • lookup_value: The value you want to find in the range.
   • lookup_array: The range of cells where you want to search for the value.
   • return_array: The range of cells from which you want to retrieve the matching value.
   • match_mode (optional): Specifies the match mode. You can use 0 for exact match or 1 for the closest match.
   • search_mode (optional): Specifies the search mode. You can use 1 for searching from the beginning of the range or -1 for searching from the end of the range.
2. Set the lookup_value to the value you want to find.
3. Set the lookup_array to the range where you want to search for the value.
4. Set the return_array to the range from which you want to retrieve the matching value.
5. Use search_mode of -1 to search from the end of the range.

Here’s an example to illustrate the process:

Suppose you have a list of employee names in cells A2:A7 and you want to find the last occurrence of the name “John”. The corresponding employee IDs are listed in cells B2:B7. You can use the following XLOOKUP formula:

=XLOOKUP("John", A2:A7, B2:B7, , -1)

The formula will search for “John” in the range A2:A7, and when it finds a match, it will return the corresponding value from B2:B7. The -1 as the search_mode ensures that the search starts from the end of the range.