Edit Table 2 in Vlookup B2 Table 2 3 False

VLOOKUP in Excel is a function to make vertical lookups. IF is the Excel function to run logical comparisons (TRUE or FALSE). Separately, they can do a great job. However, you can nest Excel VLOOKUP and IF, and enjoy their synergy. Read on to discover their uses.

How to use IF and VLOOKUP in Excel together

In most cases, you can use the Excel VLOOKUP IF statements to make a comparison between a lookup result and a specified value. At the same time, IF VLOOKUP Excel can also cope with the following tasks:

• Do a vertical lookup on two values
• Conceal #N/A errors
• Perform calculations

Check out how to use VLOOKUP in Excel

IF VLOOKUP Excel formula syntax

          =IF(VLOOKUP("lookup_value",lookup_range, column_number, [match])logical_condition, value_if_true, value_if_false)        

• "lookup_value" – the value to look up vertically.
• lookup_range – the range of where to search for the lookup_value and the matching value.
• column_number – the number of the column containing the matching value to return.
• [match] is an optional parameter to choose either closest match (TRUE) or exact match (FALSE). TRUE is set by default.
• logical_condition – a logical expression that can be evaluated as TRUE or FALSE. Logical expressions are built with the help of logical operators: =, >, =<, etc.
• value_if_true – the value to return if the logical_condition is TRUE.
• value_if_false –  the value to return if the logical_condition is FALSE.

VLOOKUP with IF condition in Excel example

To demonstrate how the Excel IF function with VLOOKUP works, we imported a data set from Airtable to Excel using Coupler.io, a tool for integrating different sources such as Airtable, Google Sheets, or BigQuery with Excel.

• 

Check out the available Excel integrations and try out Coupler.io for your project.

Our goal consists of two steps, implemented in one IF VLOOKUP formula in Excel:

1. Find a quantity that matches the lookup value "Sausage sandwich"
2. Get a notification "Bon Appetit!" if the return value is greater than 10. If not, get a notification "Not bad".

Here is what the formula looks like:

=IF(VLOOKUP(B4,B3:E12,3,FALSE)>10, "Bon Appetit!", "Not bad")

• 

Now let's check out other ways of using Excel IF and VLOOKUP together.

Excel IF statement with VLOOKUP to do a vertical lookup on two values

The idea here is slightly different from the example above. You have a logical condition based on which you can perform two vertical lookups:

• Do the first lookup if the condition is true.
• Do the second lookup if the condition is false.

For example, if we have "Erastus Startin" in cell G2, then the formula will perform VLOOKUP on the type of sandwich that matches this value; otherwise, it will perform VLOOKUP on the Tine Kimmel's type of sandwich. Here is what the IF VLOOKUP Excel formula looks like:

=IF(G2="Erastus Startin", VLOOKUP("Erastus Startin",A1:E10,2,FALSE), VLOOKUP("Tine Kimmel",A1:E10,2,FALSE))

• 

This example may look illogical, but it explains the mechanism of using IF and VLOOKUP for two values.

Check out our blog post about Excel VLOOKUP between two values.

Now let's see how you can use the combination of these functions to hide #N/A errors.

Excel formulas VLOOKUP IF to conceal #N/A errors

We have a lookup list and we need to learn which of the values from the dataset are on this list.

• 

We can use a regular combination of IF and VLOOKUP like this:

=IF(B2=VLOOKUP(B2,$H$2:$H$4,1,FALSE),"Yes","No")

• 

However, if we drag the formula down, we'll get #N/A errors instead of the expected "No".

• 

This can be fixed if we slightly update our formula by adding the ISNA function as follows:

=IF(ISNA(VLOOKUP(B2,$H$2:$H$4,1,FALSE)),"No","Yes")

• 

IFNA instead of Excel IF ISNA VLOOKUP to conceal #N/A errors

The Excel IFNA function is another option to use for hiding #N/A errors in the IF VLOOKUP formulas. However, the drawback of this function is that you can only specify the value to return instead of #N/A errors. Here is how it looks in our example:

=IFNA(VLOOKUP(B2,$H$2:$H$4,1,FALSE),"No")

• 

Excel IF and VLOOKUP for calculations

You can expand your IF VLOOKUP formula with arithmetic operators to perform calculations based on a specified condition. In our example, we want to vlookup the orders with 10+ items and provide them with a 15% discount. Here is the formula to make this happen:

=IF(VLOOKUP(B2,$B$2:$E$10,3,FALSE)>=10,E2*0.85,E2)

• VLOOKUP(B2,$B$2:$E$10,3,FALSE)>=10 – condition for a vlookup result
• E2*0.85 – the total amount including the 15% discount if the condition is true

• 

IF VLOOKUP Excel is powerful pairing

These two Excel functions nested together can solve many problems. At the same time, separately they can also do many things, for example, compare two columns in Excel using VLOOKUP or run different logical checks using IF. Additionally, you can use these functions nested with others, such as SUM, SUMIF, COUNTIF, and many more. Good luck with your data!

Back to Blog

Edit Table 2 in Vlookup B2 Table 2 3 False

Source: https://blog.coupler.io/if-vlookup-excel/