Xlookup & Vlookup

 The VLOOKUP function in Excel is a powerful tool used to search for a value in the first column of a table and return a value in the same row from a specified column. Here’s a brief overview:

Syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Parameters

lookup_value: The value you want to search for.

table_array: The range of cells that contains the data.

col_index_num: The column number in the table from which to retrieve the value.

range_lookup: Optional. Use TRUE for an approximate match or FALSE for an exact match.

Example

If you have a table with employee IDs in the first column and their names in the second column, you can use VLOOKUP to find an employee’s name based on their ID:

=VLOOKUP(123, A2:B10, 2, FALSE)

This formula looks for the ID 123 in the range A2:B10 and returns the corresponding name from the second column.

Key Points

Exact Match: Use FALSE for an exact match.

Approximate Match: Use TRUE for an approximate match.

Leftmost Column: The lookup value must be in the first column of the table array.

Right Lookup: VLOOKUP can only search to the right of the lookup column12.

The XLOOKUP function in Excel is a versatile and powerful tool that can replace VLOOKUP, HLOOKUP, and even INDEX-MATCH functions. Here’s a brief overview:

Xlookup

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

Parameters

lookup_value: The value you want to search for.

lookup_array: The range or array to search within.

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

if_not_found: Optional. The value to return if no match is found.

match_mode: Optional. Specifies the type of match:

0 for an exact match (default).

-1 for an exact match or the next smaller item.

1 for an exact match or the next larger item.

2 for a wildcard match.

search_mode: Optional. Specifies the search mode:

1 for a search starting at the first item (default).

-1 for a reverse search starting at the last item.

2 for a binary search on sorted data in ascending order.

-2 for a binary search on sorted data in descending order.

Example

If you have a table with product IDs in one column and their prices in another, you can use XLOOKUP to find the price of a specific product:

=XLOOKUP("P123", A2:A10, B2:B10, "Not Found", 0, 1)

This formula looks for the product ID “P123” in the range A2:A10 and returns the corresponding price from the range B2:B10. If “P123” is not found, it returns “Not Found”.

Key Points

Flexible Lookups: XLOOKUP can search both vertically and horizontally.

Exact and Approximate Matches: It supports exact, approximate, and wildcard matches.

Error Handling: You can specify a custom message if no match is found.

Bidirectional Search: It can search from the first or last item, and even perform binary searches on sorted data12.