Excel: How to Use XLOOKUP with IF Statement

You can use the XLOOKUP function in Excel to look up some value in a column and return the corresponding value in a different column.

You can also use the following methods to apply IF logic to an XLOOKUP function and only return values if they meet certain criteria:

Method 1: Return First Value that Meets One Criteria in XLOOKUP

=XLOOKUP(TRUE,A2:A16="Spurs",C2:C16)

This particular formula finds the first value in the range A2:A16 that is equal to “Spurs” and returns the corresponding value in the range C2:C16.

Method 2: Return First Value that Meets Multiple Criteria in XLOOKUP

=XLOOKUP(1,(A2:A16="Rockets")*(B2:B16="Forward"),C2:C16)

This particular formula finds the first row where the value in the range A2:A16 is equal to “Rockets” and the value in the range B2:B16 is equal to “Forward” and returns the corresponding value in the range C2:C16.

The following examples show how to use each method with the following dataset in Excel that contains information about various basketball players:

Example 1: Return First Value that Meets One Criteria in XLOOKUP

We can type the following formula into cell E2 to return the value in the points column for the first player that has a value of “Spurs” in the team column:

=XLOOKUP(TRUE,A2:A16="Spurs",C2:C16)

The following screenshot shows how to use this formula in practice:

Excel XLOOKUP with IF logic

The formula returns a value of 6, which represents the value in the points column for the first row that is equal to “Spurs” in the team column.

Note that the formula A2:A16=”Spurs” returns a value of TRUE for each row that is equal to “Spurs” in the team column or FALSE otherwise.

Thus, when we use the XLOOKUP function we are simply looking up the first row that has a value of TRUE in the range A2:A16 and then returning the corresponding value in the range C2:C16.

Example 2: Return First Value that Meets Multiple Criteria in XLOOKUP

We can type the following formula into cell E2 to return the value in the points column for the first player that has a value of “Rockets” in the team column and a value of “Forward” in the position column:

=XLOOKUP(1,(A2:A16="Rockets")*(B2:B16="Forward"),C2:C16)

The following screenshot shows how to use this formula in practice:

The formula returns a value of 22, which represents the value in the points column for the first row that is equal to “Rockets” in the team column and “Forward” in the position column.

Note that the formula (A2:A16=”Rockets”)*(B2:B16=”Forward”) returns a value of 1 for each row that is equal to “Rockets” in the team column and “Forward” in the position column or 0 otherwise.

Thus, when we use the XLOOKUP function we are simply looking up the first row that has a value of 1 and then returning the corresponding value in the range C2:C16.