Idl where several conditions in vlookup

VLookup with IF statement

Vlookup is a reference function, while when it is a conditional statement in Excel, these two functions are combined to find a certain value that meets the criteria and also matches the reference value based on the results of the Vlookup function. When statements show the result, in other words, we nest Vlookup in the If function.

VLookup with IF statement in Excel

Here we will combine the twin functions of 'IF Function' & 'VLOOKUP'. We will also see how we can deal with #NA errors that we sometimes get when we use a combination of the 'IF statement' and 'VLOOKUP'. While the two are very important on their own, together they offer more value.

'Vlookup' with 'If' statement: Returns 'True / False' or 'Yes / No'.

I will briefly explain the function 'IF statement' and 'Vlookup' to make this combination easier to explain. 'If' is used when you want to specify a condition as to which value should be inserted into a cell.

In the above formula, "logical_test" is the condition we are testing under, then the value if the condition is true, and then value if the condition is false.

Below is an example:

For example, suppose you have data in a table for the Vlookup function and you want to look up a value in one of the columns that corresponds to a value in the left column of the table.

Below is an example:

For example, suppose cells 'B2: E6' are the dates containing the grades of students in the three subjects shown. For example, let's say you want to learn about Vijay's grades in chemistry.

In the formula template "vlookup" above, you can see that "lookup_value" is "Vijay", the table array is "B2: E6" since we are interested in grades of "Chemistry", the column number is 3 and we are the fourth argument is "FALSE", which means an approximate match.

Now that we've revised these 2, let's examine the combinations of these 2.

The generic formula is:

IF (VLOOKUP (…) = sample_value, TRUE, FALSE)

Typical use cases for this are:

  • Compare the value returned by Vlookup to a sample value and return True / False, Yes / No, or 1 of 2 values ​​we set.
  • Compare the value returned by Vlookup with a value in another cell and return the values ​​given above
  • Compare the value returned by Vlookup and choose between two sets of calculations based on that.

How to use Vlookup with IF statement in Excel?

Now that I've explained the formula template and some use cases to you, let's try to explain them better using examples and then explain them in a descriptive way.

Vlookup with IF function example # 1

The data table remains the same as explained in the 'vlookup' function.

Now suppose we have decided on one condition: if the points scored are greater than 92, it will be shown as “Great”, otherwise it will be shown as “Good”. Now when I see the grades I don't care what the actual grade is, but I just want to see if they're great or good.

Now let's apply the formula here.

Now look at cell F3. We carry out a 'lookup' as above, which gives the result "92". Add this "if" condition. Now it is checked whether this grade is greater than 92, it is "great", otherwise it is "good". Since here we are getting the marks from Vijay that correspond to chemistry, i.e. 92, the result shown is “good”.

Vlookup with IF function example # 2

Now let's move on to another example and say where you want to make this cutoff dynamic. You want to change the limit values ​​and immediately see whether the value is "Great / Good" (in this case).

Please see the screenshot below:

Here you can see the formula in cell F5. It's like what I showed in the previous example. The only difference is that the value you are now comparing the results with is a dynamic value that is stored in cell E8.

As the cut-off was reduced to 90, Vijay's performance in the same subject chemistry was rated “great” compared to “good” as shown in the previous example.

Vlookup with IF function example # 3

Now we come to the third example, in which a calculation is carried out based on the 'vlookup' result.

Let's use different dates this time. Let's say we want to use a discounting strategy based on the retail price of things.

You can find the data in the following screenshot:

Cells B3: C8 show the price of fruits. I used data validation in Excel so that in cell E5 you could select one of the fruits mentioned in column B.

Now let's move on to our pricing strategy. If the cost is more than 180, we give a 20% discount, otherwise only 10% discount.

We implement this using the formula as shown in the following screenshot:

See the formula in cell F5. First, the result of the search function is checked. If it is greater than 180 we will multiply the value by 80% (i.e. 20% discount), otherwise we will multiply the result by 90% (i.e. 10% discount).

Now we come to the last example.

Vlookup with IF statement example # 4

Let's just use the data above. Suppose you want to see if there is a fruit in the list or not. This gives us an example in which we can use the combination of IF statement, VLOOKUP and ISNA function in Excel.

For example, suppose you did a "search" at the price of "WaterMelon". Since it is not in the list, you will get the '#NA' error.

See cell B11. The formula is displayed in the system tray. To handle such cases, we will now show you the formula. Our idea now is that if the “fruit” we are looking for is not available, the result “Not available” is displayed. Otherwise, the price of the fruit should be returned.

Apply the formula "If-Statement", "ISNA" and "Vlookup".

Just put the name of the fruit in cell E2 and you will get the result you want in cell C11. If the fruit is absent, cell C11 will output "Absent" as you can see for "WaterMelon". Otherwise the price quoted for "Apple" is given.

Hope these examples give you all clarification. Please practice for a better and advanced understanding.

Important information about Excel Vlookup with IF function

  • For "Vlookup" to work, the "Lookup" value should always be in the "far left" column of the data table that you enter in the "Vlookup" formula.
  • The combination of "If statement" and "Vlookup" can be used for error handling. This is a very important part of creating dashboards, monthly planners, etc. So take the time to understand and practice the formulas.
  • When you run Vlookup, you usually choose Exact Match as the fourth argument of Vlookup if you are interested in exactly matching the search value.

You can download this Vlookup with IF-Instruction-Excel-Template - Vlookup with IF-Excel-Template