- HOW TO USE VLOOKUP IN EXCEL FOR MULTIPLE VALUES CODE
- HOW TO USE VLOOKUP IN EXCEL FOR MULTIPLE VALUES SERIES
HOW TO USE VLOOKUP IN EXCEL FOR MULTIPLE VALUES CODE
To explain further: We are about to create a VLOOKUP formula that will retrieve the description that corresponds to the item code in cell A11. 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. 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:
HOW TO USE VLOOKUP IN EXCEL FOR MULTIPLE VALUES SERIES
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.