How Excel XLOOKUP Parameters Work

The XLOOKUP function in Excel is a powerful tool that allows you to search for a value in a range and return a corresponding value in the same row. To make the most out of this function, it's essential to understand how the parameters work.

Lookup Value

The first parameter of XLOOKUP is the lookup value, which is the value you want to search for in the lookup array. It can be a specific value, a cell reference, or a formula that returns a value.

Lookup Array

The lookup array is the range of cells where Excel will search for the lookup value. It can be a single row or column or a multi-column or multi-row range.

Return Array

The return array is the range from which XLOOKUP will return a value. It must have the same shape and size as the lookup array.

Match Mode

The match mode determines how Excel searches for the lookup value. It can be set to exact match or approximate match. In an exact match, Excel looks for an exact match of the lookup value. In an approximate match, Excel looks for the closest match smaller or larger than the lookup value.

If Not Found

The if not found parameter allows you to specify the value or action to be taken if the lookup value is not found in the lookup array. For example, you can choose to return an error message, display a specific value, or perform further calculations.

By understanding these parameters, you can effectively use the XLOOKUP function in Excel to perform advanced searches and retrieve data dynamically.