How To Find Duplicate Values In Excel Using Vlookup

VLOOKUP is i of Excel'due south nigh useful functions, and it's also one of the least understood.  In this article, we demystify VLOOKUP by manner of a real-life case.  Nosotros'll create a usable Invoice Template for a fictitious company.

VLOOKUP is an Excel function.  This commodity volition assume that the reader already has a passing understanding of Excel functions, and can use basic functions such as SUM, AVERAGE, and TODAY.  In its near common usage, VLOOKUP is a database role, meaning that it works with database tables – or more merely, lists of things in an Excel worksheet.  What sort of things?   Well, any sort of matter.  You may have a worksheet that contains a listing of employees, or products, or customers, or CDs in your CD collection, or stars in the night heaven.  It doesn't really affair.

Here'south an instance of a list, or database.  In this instance it's a list of products that our fictitious visitor sells:

Usually lists like this have some sort of unique identifier for each item in the listing.  In this instance, the unique identifier is in the "Item Code" column.  Annotation:  For the VLOOKUP office to piece of work with a database/list, that list must accept a column containing the unique identifier (or "key", or "ID"), and that column must be the first column in the table.  Our sample database above satisfies this criterion.

The hardest part of using VLOOKUP is agreement exactly what it's for.  So let'southward see if we can go that clear outset:

VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.

In the example in a higher place, you would insert the VLOOKUP function into another spreadsheet with an detail code, and information technology would return to you either the corresponding item's description, its price, or its availability (its "In stock" quantity) equally described in your original list.  Which of these pieces of information volition it pass you back?  Well, yous get to decide this when yous're creating the formula.

If all yous need is one piece of information from the database, it would exist a lot of trouble to go to to construct a formula with a VLOOKUP function in information technology.  Typically y'all would use this sort of functionality in a reusable spreadsheet, such every bit a template.  Each time someone enters a valid item lawmaking, the system would retrieve all the necessary data nearly the corresponding detail.

Allow'due south create an case of this:  An Invoice Template that nosotros can reuse over and over in our fictitious company.

First we start Excel, and nosotros create ourselves a bare invoice:

This is how it'south going to work:  The person using the invoice template volition make full in a series of item codes in column "A", and the arrangement will retrieve each detail'south description and price from our product database. That information will be used to calculate the line total for each item (assuming we enter a valid quantity).

For the purposes of keeping this instance unproblematic, we will locate the product database on a divide sheet in the same workbook:

In reality, it'southward more likely that the product database would be located in a separate workbook.  It makes little difference to the VLOOKUP function, which doesn't really care if the database is located on the same sheet, a different sheet, or a completely dissimilar workbook.

So, we've created our product database, which looks like this:

In club to exam the VLOOKUP formula we're well-nigh to write, we first enter a valid particular lawmaking into cell A11 of our bare invoice:

Adjacent, we movement the agile cell to the cell in which we want data retrieved from the database past VLOOKUP to exist stored.  Interestingly, this is the step that most people get incorrect.  To explain further:  Nosotros are about to create a VLOOKUP formula that volition remember the clarification that corresponds to the particular code in prison cell A11.  Where do we want this clarification put when we get it?  In prison cell B11, of course.  So that's where we write the VLOOKUP formula: in cell B11. Select cell B11 now.

We need to locate the list of all available functions that Excel has to offer, so that we tin can cull VLOOKUP and get some assist in completing the formula.  This is found past first clicking the Formulas tab, then clicking Insert Function:

A box appears that allows the states to select whatever of the functions available in Excel.

To notice the i nosotros're looking for, we could type a search term like "lookup" (considering the function nosotros're interested in is a lookup part).  The system would return u.s. a list of all lookup-related functions in Excel. VLOOKUP is the 2nd one in the list.  Select information technology an click OK.

The Function Arguments box appears, prompting us for all the arguments (or parameters) needed in gild to complete the VLOOKUP function.  Yous can recall of this box equally the office asking u.s.a. the following questions:

1. What unique identifier are you looking upwardly in the database?
2. Where is the database?
3. Which slice of information from the database, associated with the unique identifier, practise y'all wish to have retrieved for yous?

The first three arguments are shown in bold, indicating that they are mandatory arguments (the VLOOKUP function is incomplete without them and will not return a valid value).  The fourth statement is not bold, significant that it'south optional:

We volition complete the arguments in order, top to bottom.

The get-go argument we need to consummate is the Lookup_value argument.  The role needs us to tell information technology where to find the unique identifier (the detail lawmaking in this case) that it should be returning the description of.  We must select the detail code nosotros entered before (in A11).

Click on the selector icon to the right of the first argument:

And then click once on the prison cell containing the item code (A11), and press Enter:

The value of "A11" is inserted into the first statement.

At present we need to enter a value for the Table_array argument.  In other words, we need to tell VLOOKUP where to discover the database/listing.  Click on the selector icon adjacent to the 2nd argument:

Now locate the database/list and select the entire list – not including the header line.  In our example, the database is located on a split up worksheet, so we starting time click on that worksheet tab:

Next nosotros select the unabridged database, non including the header line:

…and printing Enter.  The range of cells that represents the database (in this case "'Product Database'!A2:D7") is entered automatically for us into the second argument.

Now we demand to enter the tertiary argument, Col_index_num.  We employ this argument to specify to VLOOKUP which piece of information from the database, associate with our detail lawmaking in A11, we wish to accept returned to us.  In this particular example, we wish to have the item's description returned to u.s..  If you expect on the database worksheet, you lot'll observe that the "Description" column is the second column in the database.  This means that we must enter a value of "2" into the Col_index_num box:

It is important to note that that we are not inbound a "ii" here because the "Description" cavalcade is in the B column on that worksheet.  If the database happened to start in column Thou of the worksheet, we would withal enter a "two" in this field because the "Description" cavalcade is the 2nd column in fix of cells we selected when specifying the "Table_array".

Finally, we need to decide whether to enter a value into the final VLOOKUP argument, Range_lookup.  This argument requires either a true or fake value, or it should be left bare.  When using VLOOKUP with databases (equally is true 90% of the time), the fashion to make up one's mind what to put in this argument can be idea of as follows:

If the first cavalcade of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it'southward possible to enter a value of true into this argument, or get out it blank.

If the first cavalcade of the database is not sorted, or it'southward sorted in descending order, and then you must enter a value of imitation into this argument

As the first column of our database is non sorted, we enter false into this argument:

That'southward it!  We've entered all the information required for VLOOKUP to return the value we demand.  Click the OK button and notice that the description respective to item code "R99245" has been correctly entered into jail cell B11:

The formula that was created for us looks like this:

If we enter a different particular code into jail cell A11, we will begin to see the power of the VLOOKUP role:  The description prison cell changes to lucifer the new item code:

We can perform a similar set up of steps to get the particular'south price returned into cell E11.  Note that the new formula must exist created in cell E11.  The result volition wait like this:

…and the formula will await like this:

Note that the only difference between the two formulae is the third statement (Col_index_num) has changed from a "2" to a "three" (because we desire data retrieved from the 3rd column in the database).

If we decided to purchase 2 of these items, we would enter a "2" into cell D11.  We would then enter a elementary formula into jail cell F11 to go the line full:

=D11*E11

…which looks similar this…

Completing the Invoice Template

We've learned a lot about VLOOKUP then far.  In fact, we've learned all we're going to learn in this article.  It's important to annotation that VLOOKUP can exist used in other circumstances also databases.  This is less mutual, and may be covered in future How-To Geek articles.

Our invoice template is not yet complete.  In order to consummate it, we would exercise the following:

1. We would remove the sample item code from cell A11 and the "2" from prison cell D11.  This will cause our newly created VLOOKUP formulae to display error messages:
   
   We can remedy this past judicious use of Excel'southward IF() and ISBLANK() functions.  We change our formula from this… =VLOOKUP(A11,'Product Database'!A2:D7,2,FALSE)…to this…=IF(ISBLANK(A11),"",VLOOKUP(A11,'Production Database'!A2:D7,2,FALSE))
   
2. We would copy the formulas in cells B11, E11 and F11 downwardly to the remainder of the particular rows of the invoice.  Note that if we practice this, the resulting formulas will no longer correctly refer to the database table.  We could set up this by changing the cell references for the database to accented cell references.  Alternatively – and even meliorate – we could create a range name for the entire product database (such as "Products"), and use this range name instead of the cell references.  The formula would change from this… =IF(ISBLANK(A11),"",VLOOKUP(A11,'Product Database'!A2:D7,two,FALSE))…to this…      =IF(ISBLANK(A11),"",VLOOKUP(A11,Products,2,FALSE))…and so copy the formulas down to the rest of the invoice item rows.
3. Nosotros would probably "lock" the cells that contain our formulae (or rather unlock the other cells), then protect the worksheet, in order to ensure that our advisedly constructed formulae are non accidentally overwritten when someone comes to fill in the invoice.
4. We would salve the file as a template, so that it could be reused by everyone in our company

If nosotros were feeling really clever, we would create a database of all our customers in another worksheet, and then utilise the customer ID entered in cell F5 to automatically fill in the customer'south name and address in cells B6, B7 and B8.

If you would similar to practice with VLOOKUP, or simply see our resulting Invoice Template, it can exist downloaded from hither.

Source: https://www.howtogeek.com/howto/13780/

Posted by: clarkcasits.blogspot.com

Share this post