XLOOKUP with IF
This tutorial will demonstrate how to combine the XLOOKUP and IF functions in Excel. If your version of Excel does not support XLOOKUP, read how to use VLOOKUP instead.
XLOOKUP Multiple Lookup Criteria
There are a lot of ways to use the IF Function alongside the XLOOKUP Function, but first, let’s look at an example using the core element of the IF Function, the logical criteria.
One common example is performing a lookup with multiple criteria, and the most common solution to this is by concatenating the lookup criteria (e.g., F3&G3) and their corresponding column in the lookup data (e.g., B3:B7&C3:C7).
=XLOOKUP(F3&G3,B3:B7&C3:C7,D3:D7)
The above method works fine most of the time, but it can lead to incorrect results for conditions that involve numbers.
A more foolproof method is by creating an array of Boolean values from logical criteria.
=XLOOKUP(1,(B3:B7=F3)*(C3:C7=G3),D3:D7)
Let’s walk through this formula:
Logical Criteria
First, let’s apply the appropriate condition to their corresponding columns by using the logical operators (e.g., =,<,>).
Let’s start with the first criterion (e.g., Student ID).
=B3=$C$2
Repeat the step for the other criteria (e.g., Subject).
=D3=$E$2
Array AND
Next, we perform the array equivalent of the AND Function by multiplying the Boolean arrays where TRUE is 1 and FALSE is 0.
=C3*E3
Note: The AND Function is an aggregate function (many inputs to one output). Therefore, it won’t work in our array scenario.
XLOOKUP Function
Next, we use the result of the Array AND as the new lookup array where we will lookup for 1 instead of the original lookup value.
=XLOOKUP(1,F3:F7,G3:G7)
Combining all formulas above results to our original formula:
=XLOOKUP(1,(B3:B7=F3)*(C3:C7=G3),D3:D7)
XLOOKUP Error-Handling with IF
Sometimes we need to check if the result of an XLOOKUP Function results in an error. A great way of doing this is by using the IF Function, which is also the best way of notifying us about the cause of the error.
XLOOKUP IF with ISNA
Let’s first check if the XLOOKUP failed to find a match using the IF with ISNA Formula.
=IF(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",F3/XLOOKUP(E3,B3:B7,C3:C7))
Let’s walk through the above formula:
ISNA Function
First, let’s check for the #N/A Error, which basically means no match was found, using the ISNA Function.
=ISNA(XLOOKUP(E3,B3:B7,C3:C7))
IF Function
Next, let’s use the IF Function to check the result of the ISNA Function and return a message (e.g., “Product not found!”) if the result is TRUE. Otherwise, if the result is false, we’ll proceed with the calculation.
=IF(G3,"Product not found!",F3/XLOOKUP(E3,B3:B7,C3:C7))
Combining all formulas results to our original formula:
=IF(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",F3/XLOOKUP(E3,B3:B7,C3:C7))
XLOOKUP IF with ISBLANK
Another thing to check is if the result of XLOOKUP is blank. There are cases where blank means there’s no input yet, and therefore, we need to distinguish it from zero.
We’ll just replace the ISNA with ISBLANK to check for blank.
=IF(ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",F3/XLOOKUP(E3,B3:B7,C3:C7))
Let’s walk through the above formula:
ISBLANK Function
First, let’s check for blank using the ISBLANK Function.
=ISBLANK(XLOOKUP(E3,B3:B7,C3:C7))
IF Function
Just like with the previous scenario, we then input the result of the ISBLANK Function to the IF Function and return a message (e.g., “No Data!”) if TRUE or proceed with the calculation if FALSE.
=IF(G3,"No data!",F3/XLOOKUP(E3,B3:B7,C3:C7))
Combining all formulas results to our original formula:
=IF(ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",F3/XLOOKUP(E3,B3:B7,C3:C7))
XLOOKUP IF with ISTEXT
Another thing to avoid in calculations is accidental text input. In this case, we’ll use the IF with ISTEXT Formula to check for a text value.
=IF(ISTEXT(XLOOKUP(E3,B3:B7,C3:C7)),"Invalid input!",F3/XLOOKUP(E3,B3:B7,C3:C7))
ISTEXT Function
First, we check if the output of the XLOOKUP Function is a text.
=ISTEXT(XLOOKUP(E3,B3:B7,C3:C7))
IF Function
Next, we check the result using the IF Function and return the corresponding message (e.g., “Invalid input!”) if TRUE or proceed to the calculation if FALSE.
=IF(G3,"Invalid input!",F3/XLOOKUP(E3,B3:B7,C3:C7))
XLOOKUP with IFS
The final error-handling formula would be the combination of the previous IF Formulas, and we can do this by nesting them.
=IF(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",
IF(ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",
IF(ISTEXT(XLOOKUP(E3,B3:B7,C3:C7)),"Invalid input!",F3/XLOOKUP(E3,B3:B7,C3:C7))
)
)
As we notice above, the Nested IF Formula becomes more complicated as we add more conditions. A better way to approach this is by using the IFS Function.
=IFS(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",
ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",
ISTEXT(XLOOKUP(E3,B3:B7,C3:C7)),"Invalid input!",
TRUE,F3/XLOOKUP(E3,B3:B7,C3:C7)
)
Note: The IFS Function can evaluate multiple sets of logical criteria. It starts from the first condition moving on to the next until it finds the first TRUE condition and returns the corresponding return value to it.
Let’s walk through the formula above:
ISNA
We start with our first condition, which is the ISNA Function. If ISNA is TRUE, we return its corresponding value (e.g., “Product not found!”). Otherwise, we proceed to check the next condition.
=ISNA(XLOOKUP(E3,B3:B7,C3:C7))
ISBLANK and ISTEXT
Since the first condition is FALSE in this scenario, we check the succeeding conditions until we find the first TRUE and return the corresponding value for the TRUE condition.
=ISBLANK(XLOOKUP(E3,B3:B7,C3:C7))
=ISTEXT(XLOOKUP(E3,B3:B7,C3:C7))
Default Value
We can set a default value by setting the last condition as TRUE in case all the conditions are FALSE, which in our error-handling scenario, means that we can now proceed to the calculation without errors.
Combining all formulas above results to our original formula:
=IFS(ISNA(XLOOKUP(E3,B3:B7,C3:C7)),"Product not found!",
ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"No data!",
ISTEXT(XLOOKUP(E3,B3:B7,C3:C7)),"Invalid input!",
TRUE,F3/XLOOKUP(E3,B3:B7,C3:C7)
)
