HOW TO USE VLOOKUP HOW TO

Then, based on it, choose between two sets of calculations.

Compare the value returned by VLOOKUP.

Compare the value returned by VLOOKUP with a value present in another cell and return values as above.

Compare the value returned by VLOOKUP with a sample value and return “True/False,” “Yes/No,” or 1 out of 2 values we determined.

IF(VLOOKUP(…) = sample_value, TRUE, FALSE) Let us investigate the combinations of these 2. Since we are interested in an “exact match,” the fourth argument is “FALSE,” which denotes an approximate match. For example, suppose you want to know Vijay’s marks in Chemistry.įrom the VLOOKUP formula template above, you can see the lookup_value is “Vijay,” table array is “B2:E6” since we are interested in marks of “Chemistry,” column number is 3. Suppose cells B2:E6 is the data containing students’ marks in the 3 subjects shown. You want to look up a value in any of the columns corresponding to a value in the table’s left column. Similarly, for the VLOOKUP function, suppose you have data in a table. In the above formula, “logical_test” is the condition we are testing, the value if the condition is “True,” and then the value if the condition is “False.” ‘IF’ is used when you want a condition to decide which value will be populated in a cell. We will briefly explain the IF statement and ‘Vlookup’ Function, so that combination becomes easier to explain. VLOOKUP with IF statement: Returns “True/False” or “Yes/No.”’ While the two are quite important on their own, they provide more value.

HOW TO USE VLOOKUP HOW TO

We will also see how to deal with #NA errors, which we might sometimes receive while using a combination of IF statement and VLOOKUP. We will combine the twin functions of the IF function and VLOOKUP. Things to Remember About Excel Vlookup with IF Function.How to Use Vlookup with IF Statement in Excel?.Here we discuss How to Use VLOOKUP Names along with practical examples and a downloadable excel template. We can edit or change the Vlookup names using the methods shown in example-2.Do not consider the header while naming the selected range.Vlookup names fix the range so that the user does not get any error while executing the Vlookup function for any type of range.Because spaces are not allowed while naming the range. When naming the selected range, do not use spaces.It is recommended to use Vlookup Names only when we need to apply vlookup multiple times, or we need to select the huge set of lookup range.With the help of this, we can avoid any error while selecting the ranges.Vlookup names save a lot of time to in selecting the range.If we click on the edit button, we will be able to see the Named ranges upon selecting any of the listed named values. When we select the range, we will see it will get converted into the name saved name “Sales_Info”, as we can see below.Ĭlicking on the Name Manager Option from the Formula tab, we will get the box in which we would have a list of names that we have changed or given to any range. So, go to cell F2 and apply vlookup, considering the owner as John again. But this time, we will be choosing the saved table name as the lookup range. Let’s change the name to Sales_Info, as shown below.Īgain we will apply the Vlookup in the second table as we did earlier. And after that, we can name the table range in any name in the Name Box as highlighted below. Now to name the range for Vlookup, first select the data from cell A2 to C6. Now, once we enter, we will be able to see the value from the first table to the cell G2, as shown below. Similarly, we will look up the value for Product Class for the same owner John as shown below. We will then get the value of quantity sold by John as 4159 as per the value from the first table. Let’s lookup the value for owner name JOHN in cell F2 and get the quantity sold from the first table using Vlookup as shown below. And besides that, we have a section where we will be looking up the value from the first table. This table has Owner, Quantity Sold and Product Class columns. For this, we have sales data as shown below. In this example, we will see the way to name the table used in the Vlookup function. Lets us discuss the examples of Vlookup Names. Here we have chosen a Name.Īfter that, if we again place the cursor at cell B1, we will be able to see the given name. Now to name cell B1, let’s go-to name box and write the name which we want to give to cell B1.

How to use vlookup