Explore 10 useful XLOOKUP equations in Excel. These can easily be adapted to fit your own needs.
=XLOOKUP(A1, A2:C6, 2, 0)This equation looks up the value in cell A1 in the first column of the range A2:C6, and returns the corresponding value in the second column of the same range.
=XLOOKUP(A1, A2:C6, C2:C6, 1, 0)This equation uses the exact match option and returns 0 if the value in cell A1 is not found in the first column of the range A2:C6. The equation returns the corresponding value in the third column of the same range.
=XLOOKUP(A1, A2:C6, B2:B6, 2, "N/A")This equation uses the optional argument “N/A” to specify what value to return if the lookup value is not found in the first column of the range A2:C6. The equation returns the corresponding value in the second column of the range B2:B6.
=XLOOKUP(A1, A2:C6, C2:C6, 0, "Not Available")This equation uses the optional argument “Not Available” to specify what value to return if the lookup value is not found in the first column of the range A2:C6. The equation returns the corresponding value in the third column of the same range.
=XLOOKUP(A1, A2:C6, B2:C6, 0)This equation looks up the value in cell A1 in the first column of the range A2:C6, and returns the corresponding values in the second and third columns of the same range. The result will be displayed as an array.
=XLOOKUP("Apple", A2:B6, 2, 0)This equation looks up the value “Apple” in the first column of the range A2:B6, and returns the corresponding value in the second column.
=XLOOKUP(A1, A2:B6, B2:B6, 0)This equation looks up the value in cell A1 in the first column of the range A2:B6, and returns the corresponding value in the second column.
=XLOOKUP("*" & A1 & "*", A2:B6, 2, 0)This equation uses the wildcard characters “*” to search for the value in cell A1 within the first column of the range A2:B6, and returns the corresponding value in the second column.
=XLOOKUP(A1, A2:B6, B2:B6, 1)This equation uses the exact match option to find the value in cell A1 in the first column of the range A2:B6, and returns the corresponding value in the second column.
=XLOOKUP(A1, A2:B6, C2:C6, 2, "Not Found")This equation uses the optional argument “Not Found” to specify what value to return if the lookup value is not found in the first column of the range A2:B6. The equation returns the corresponding value in the third column of the range C2:C6.
