Interestingly, this is the step that most people get wrong. Next, we move the active cell to the cell in which we want information retrieved from the database by VLOOKUP to be stored.
#How to perform a vlookup in excel 2016 code#
In order to test the VLOOKUP formula we’re about to write, we first enter a valid item code into cell A11 of our blank invoice: So, we’ve created our product database, which looks like this: 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 different workbook. In reality, it’s more likely that the product database would be located in a separate workbook. That information will be used to calculate the line total for each item (assuming we enter a valid quantity).įor the purposes of keeping this example simple, we will locate the product database on a separate sheet in the same workbook: This is how it’s going to work: The person using the invoice template will fill in a series of item codes in column “A”, and the system will retrieve each item’s description and price from our product database. Let’s create an example of this: An Invoice Template that we can reuse over and over in our fictitious company.įirst we start Excel, and we create ourselves a blank invoice: Each time someone enters a valid item code, the system would retrieve all the necessary information about the corresponding item. Typically you would use this sort of functionality in a reusable spreadsheet, such as a template. If all you need is one piece of information from the database, it would be a lot of trouble to go to to construct a formula with a VLOOKUP function in it. Which of these pieces of information will it pass you back? Well, you get to decide this when you’re creating the formula. In the example above, you would insert the VLOOKUP function into another spreadsheet with an item code, and it would return to you either the corresponding item’s description, its price, or its availability (its “In stock” quantity) as described in your original list.