#How to do a if then formula in excel plus

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 'Not found', VLOOKUP(E1, A2:B10, 2, FALSE))Ħ.2 IF With SUM, AVERAGE, MIN, and MAX FunctionsĮxcel has the SUMIF and SUMIFS functions that you can use, to sum up values in cells using certain criteria. =IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), '', VLOOKUP(E1, A2:B10, 2, FALSE))Įxample 3: If the lookup value is not available, the formula returns a specific text as shown below =IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))Įxample 2: If the lookup value is not found, the formula returns blank as shown below If the lookup value in a cell (E1) is not found, the formula returns 0 as shown below IF(ISNA(VLOOKUP(…)), value_if_na, VLOOKUP(…))Įxample 1. Here, you will need the following generic formula: Therefore, you can return zero, blank, or text if #N/A to make your table neat. The VLOOKUP or any other lookup function returns a N/A error if it cannot find something. Other Excel functions that can give the IF formula for multiple conditions include 6.1 IF#N/A Error in VLOOKUP Function The trick can also work as a regular formula in Excel 365 and Excel 2021 since it supports dynamic arrays.Įxample: We can get “Pass” if cells B2 and C2 are greater than 50 by applying the formulas IF(condition1) + (condition2) + …, value_if_true, value_if_false)įinally, you can complete the Array formula by pressing Ctrl + Shift + Enter buttons together.

#How to do a if then formula in excel plus

While to test conditions using OR logic, you will apply the plus sign: IF(condition1) * (condition2) * …, value_if_true, value_if_false) For example, to evaluate the AND logical condition, you can use the asterisk: =IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), 'Pass', 'Fail')Īn Array formula can also help to get an Excel IF to test multiple conditions. In the end, the final IF formula with multiple AND & OR statements will turn into:

You can now use the OR function to test the logic of IF and supply the desired value_if_true and value_if_false values. That is because meeting all the conditions is not necessary, but either statement will serve the purpose. In this case, the exam is deemed passed if either of the conditions is passed.Īlthough the formula may seem tricky, you only need to express each condition as an AND statement and nest them in the OR function. For example, if you are checking exam results using these criteria: You can only apply AND & OR functions at a time if your task requires you to evaluate several sets of conditions. IF Formula with Multiple AND & OR Conditions You can use ISNUMBER and ISTEXT formula to find whether the cells have text value, number, or blank. The only difference is that, if one of the conditions is TRUE then the result will be TRUE.įollow the above process to apply the OR Function. The IF with OR Function works the same way as the AND function we have seen above. Press enter and use the AutoFill feature to copy the formula to the rest of the cells. To know whether the student passed or failed the exam. If the condition is true return cheap, otherwise return expensive! In the example below, let us test whether the price is less than or equal to 2000. IF (logical test, ‘value_if_true’, ‘value_if_false’) Basic IF formula that tests a single conditionĪ single IF statement tests whether a condition is met and returns a value if TRUE and another value if FALSE. 6.2 IF With SUM, AVERAGE, MIN, and MAX Functionsġ.IF formula with multiple conditions (Nested IF Functions. Basic IF formula that tests a single condition