How To Use Vlookup In Excel 2013

The tutorial shows how to combine V LOOKUP and IF function together to 5-lookup with if status in Excel. You will as well learn how to use IF ISNA VLOOKUP formulas to supervene upon #N/A errors with your own text, zero or blank prison cell.

Whilst the VLOOKUP and IF functions are useful on their ain, together they deliver even more than valuable experiences. This tutorial implies that you remember the syntax of the 2 functions well, otherwise yous may desire to brush up on your knowledge past following the above links.

Vlookup with If statement: render True/False, Yes/No, etc.

Ane of the about common scenarios when yous combine If and Vlookup together is to compare the value returned by Vlookup with a sample value and return Yep / No or True / Fake every bit the result.

In most cases, the post-obit generic formula would piece of work nicely:

IF(VLOOKUP(…) = value, True, Simulated)

Translated in plain English language, the formula instructs Excel to render Truthful if Vlookup is truthful (i.due east. equal to the specified value). If Vlookup is false (non equal to the specified value), the formula returns False.

Below you will a notice a few real-life uses of this IF Vlookup formula.

Example i. Look upwards a specific value

Allow'south say, you lot have a list of items in column A and quantity in column B. You lot are creating a dashboard for your users and need a formula that would check the quantity for an item in E1 and inform the user whether the particular is in stock or sold out.

You pull the quantity with a regular Vlookup with verbal friction match formula like this:

=VLOOKUP(E1,$A$two:$B$10,2,FALSE)

Then, write an IF statement that compares Vlookup's outcome with zero, and returns "No" if it is equal to 0, "Yep" otherwise:

=IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0,"No","Yes")

If Vlookup formula to return Yes or No based on vlookup result

Instead of Yep/No, y'all can return TRUE/FALSE or In Stock/Sold out or any other two choices. For example:

=IF(VLOOKUP(E1,$A$2:$B$x,ii)=0,"Sold out","In stock")

Y'all can also compare the value returned by Vlookup with sample text. In this case, be sure to enclose a text string in quotation marks, like this:

=IF(VLOOKUP(E1,$A$2:$B$10,two)="sample text",Truthful,FALSE)

Case ii. Compare Vlookup event with another jail cell

Some other typical instance of Vlookup with If status in Excel is comparison the Vlookup output with a value in some other cell. For example, we tin can check if it's greater than or equal to a number in cell G2:

=IF(VLOOKUP(E1,$A$2:$B$ten,2)>=G2,"Yes!","No")

And here is our If formula with Vlookup in action:
If formula with Vlookup to compare vlookup's result with another cell

In a similar fashion, you tin utilize any other logical operator together with a cell reference in your Excel If Vlookup formula.

Example 3. Vlookup values in a shorter list

To compare each cell in the target column with some other listing and return Truthful or Aye if a match is found, Imitation or No otherwise, use this generic IF ISNA VLOOKUP formula:

IF(ISNA( VLOOKUP(…)),"No","Yes")

If Vlookup results in the #N/A mistake, the formula returns "No", meaning the lookup value is non found in the lookup list. If the friction match is institute, "Yes" is returned. For example:

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

Vlookup values in a shorter list and return Yes or No.

If your business logic requires the contrary results, simply swap "Aye" and "No" to opposite the formula'due south logic:

=IF(ISNA(VLOOKUP(A2,$D$2:$D$four,1,False)),"Yes","No")

IF ISNA VLOOKUP formula to look up values in a shorter list and return Yes or No.

Excel If Vlookup formula to perform dissimilar calculations

Besides displaying your own text messages, If part with Vlookup tin can perform different calculations based on the criteria yous specify.

Taking our instance further, let'south calculate the commission of a specific seller (F1) depending on their effectiveness: twenty% commission for those who made $200 and more than, 10% for everyone else.

For this, yous cheque if the value returned by Vlookup is greater than or equal to 200, and if information technology is, multiply it by 20%, otherwise by 10%:

=IF(VLOOKUP(F1,$A$two:$C$ten,3,FALSE )>=200, VLOOKUP(F1,$A$2:$C$x,3,Imitation)*xx%, VLOOKUP(F1,$A$two:$C$10,3,FALSE)*10%)

Where A2:A10 are seller names and C2:C10 are sales.
Excel If Vlookup formula to perform different calculations

If the VLOOKUP function cannot find a specified value, it throws an #N/A mistake. To catch that fault and replace it with your ain text, embed a Vlookup formula in the logical test of the IF function, similar this:

IF(ISNA(VLOOKUP(…)), "Not plant", VLOOKUP(…))

Naturally, you lot can type whatsoever text yous like instead of "Not found".

Supposing, you accept a list of seller names in i cavalcade and sales amounts in another column. Your task is to pull a number corresponding to the proper name the user enters in F1. If the name is non found, display a message indicating then.

With the names in A2:A10 and amounts C2:C10, the task can be fulfilled with the following If Vlookup formula:

=IF(ISNA(VLOOKUP(F1,$A$ii:$C$10,iii,Faux)), "Not establish", VLOOKUP(F1,$A$2:$C$10,3,FALSE))

If the name is found, a corresponding sales amount is returned:
IF ISNA VLOOKUP formula pulls a matching value

If the lookup value is not establish, the Not found message appears instead of the #Due north/A error:
If the lookup value is not found, IF ISNA VLOOKUP returns custom text instead of the N/A error.

How this formula works

The formula's logic is very simple: you lot employ the ISNA function to cheque Vlookup for #N/A errors. If an error occurs, ISNA returns TRUE, otherwise FALSE. The in a higher place values get to the logical examination of the IF function, which does 1 of the post-obit:

If the logical exam is True (#N/A error), your message is displayed.
If the logical exam is FALSE (lookup value is plant), Vlookup returns a lucifer ordinarily.

IFNA VLOOKUP in newer Excel versions

Commencement with Excel 2013, you can use the IFNA function instead of IF ISNA to catch and handle #N/A errors:

IFNA(VLOOKUP(…), "Not found")

In our example, the formula would accept the following shape:

=IFNA(VLOOKUP(F1,$A$2:$C$10,3, Faux), "Non found")

Tip. If you'd similar to trap all sorts of errors, not only #North/A, use VLOOKUP in combination with the IFERROR role. More details can be institute hither: IFERROR VLOOKUP in Excel.

Excel Vlookup: if not institute return 0

When working with numerical values, you may desire to return a zero when the lookup value is non found. To take it done, employ the IF ISNA VLOOKUP formula discussed above with a footling modification: instead of a text message, supply 0 in the value_if_true argument of the IF function:

IF(ISNA(VLOOKUP(…)), 0, VLOOKUP(…))

In our sample table, the formula would become as follows:

=IF(ISNA(VLOOKUP(F2,$A$2:$C$x,3,FALSE)), 0, VLOOKUP(F2,$A$ii:$C$10,3,False))

If Vlookup formula: if not found return 0

In the recent versions of Excel 2016 and 2013, you lot can use the IFNA Vlookup combination once more:

=IFNA(VLOOKUP(I2,$A$2:$C$10,3, Faux), 0)

Excel Vlookup: if not found return blank prison cell

This is one more variation of the "Vlookup if then" statement: return nothing when the lookup value is not found. To practice this, instruct your formula to render an empty string ("") instead of the #N/A error:

IF(ISNA(VLOOKUP(…)), "", VLOOKUP(…))

Below are a couple of consummate formula examples:

For all Excel versions:

=IF(ISNA(VLOOKUP(F2,$A$2:$C$ten,iii,FALSE)), "", VLOOKUP(F2,$A$2:$C$10,3,Simulated))

For Excel 2016 and Excel 2013:

=IFNA(VLOOKUP(F2,$A$2:$C$10,3, Faux), "")

If with Index Match - left vlookup with If condition

Experienced Excel users know that the VLOOKUP part is non the simply mode to do vertical lookup in Excel. The Index MATCH combination can too be used for this purpose and it's even more powerful and versatile. The good news is that Index Friction match tin work together with IF in exactly the same mode as Vlookup.

For example, you have order numbers in cavalcade A and seller names in column B. You are looking for a formula to pull the order number for a specific seller.

Vlookup cannot be used in this case considering it cannot search from right to left. Index Match will piece of work without a hitch as long as the lookup value is found in the lookup column. If not, a #N/A fault will show upward. To replace the standard fault notation with your own text, nest Index Friction match inside IF ISNA:

=IF(ISNA(INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0))), "Not found", INDEX(A2:A10, Friction match(F1, $B$2:$B$ten, 0)))

In Excel 2016 and 2016, y'all can use IFNA instead of IF ISNA to make the formula more compact:

=IFNA(Alphabetize(A2:A10, Match(F1, $B$2:$B$10, 0)), "Not found")

Using If with Index Match to do left lookup without N/A errors

In a similar manner, y'all can use Index Match in other If formulas.

This is how yous use Vlookup and IF statement together in Excel. To have a closer expect at the formulas discussed in this tutorial, you are welcome to download our sample workbook below. I thank you lot for reading and hope to come across you lot on our web log next calendar week!

Practise workbook for download

Excel IF Vlookup - formula examples (.xlsx file)

Yous may also be interested in