- HOW TO USE VLOOKUP IN EXCEL 2016 STEP BY STEP WITH EXAMPLE CODE
- HOW TO USE VLOOKUP IN EXCEL 2016 STEP BY STEP WITH EXAMPLE SERIES
The range of cells that represents the database (in this case “’Product Database’!A2:D7”) is entered automatically for us into the second argument. Next we select the entire database, not including the header line: In our example, the database is located on a separate worksheet, so we first click on that worksheet tab: Now locate the database/list and select the entire list – not including the header line. Click on the selector icon next to the second argument: In other words, we need to tell VLOOKUP where to find the database/list. Now we need to enter a value for the Table_array argument. The value of “A11” is inserted into the first argument. Which piece of information from the database, associated with the unique identifier, do you wish to have retrieved for you?.What unique identifier are you looking up in the database?.You can think of this box as the function asking us the following questions: The Function Arguments box appears, prompting us for all the arguments (or parameters) needed in order to complete the VLOOKUP function.
The system would return us a list of all lookup-related functions in Excel. To find the one we’re looking for, we could type a search term like “lookup” (because the function we’re interested in is a lookup function). This is found by first clicking the Formulas tab, and then clicking Insert Function:Ī box appears that allows us to select any of the functions available in Excel. We need to locate the list of all available functions that Excel has to offer, so that we can choose VLOOKUP and get some assistance in completing the formula. So that’s where we write the VLOOKUP formula: in cell B11. Where do we want this description put when we get it? In cell B11, of course.
HOW TO USE VLOOKUP IN EXCEL 2016 STEP BY STEP WITH EXAMPLE 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 2016 STEP BY STEP WITH EXAMPLE 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.
0 kommentar(er)
