You may have a list of categories that is maintained by a different department. But many times in real life the value that you need to find is in a different file. In the example above, we had the list of categories in the same file, just in a different tab.
#HOW TO USE VLOOKUP IN EXCEL 2016 HOW TO#
How to do a vlookup between two spreadsheets You used vlookup to fill up the category column with the correct category information. While selecting the cell C2, double click on the little square on the bottom right to copy down the formula all the way to the bottomĪnd there it is. It will find “Apples” in the cell A2 and will go in the second column and retrieve the value “Fruits” and bring it back to the first table. The formula will go look for “Apples” in the “Category” table and bring the information from the second column once it matches exactly the word “apple”. =vlookup(B2,Sheet5!A:B,2,FALSE) How does vlookup actually work Once you type the last part of the formula, close the bracket and hit Enter. Most of the time this value will be FALSE. This is the field that tells the formula to look for an exact match (FALSE) or an approximate match (TRUE). This is obvious with a small dataset but if you have a large dataset with many columns you have to count the columns starting from 1 and type in the number of the column where the lookup information is. You’ll find the category information in column with the index 2 (second column). You know your product information is in column A (index 1) and the category is in the next column (index 2). Once you type comma, the formula moves to the next fieldĬol_index_num – this is the column index number where you would find the information you lookup. So you can see that the table_array is now setup as range A:B in Sheet5. You can now see the partial formula in the formula bar and the guide for what the formula should be.
While still editing the formula, click on the worksheet that holds the category information above and select columns A and B (first column that contains the products and ending with the last column that contains the category). In other words, where is the information that the apples belong to the category fruits. Table_array -this is where you tell the formula where to find the category for this lookup_value. In this case, you would look for “apples” that can be found in B2. Lookup_value – this is the value that you look for. So we have to tell it what to look for, where to look, where in the table will it find it and if it should match it exactly. Vookup is looking for a lookup_value into a table. Let’s now break down the 4 elements of the vlookup formula. Once you type that, excel will help you filling in the rest of the formula. Click in the cell C2 and enter the following: =vlookup(
See the example for the vlookup functionīut, with this in mind, let’s backtrack and start writing the formula in the formula bar.
#HOW TO USE VLOOKUP IN EXCEL 2016 WINDOWS#
This will display a windows with each of the functions elements and the result of the function. A good guide on syntax can be found in Excel if you click on the fx button in the formula bar. Let’s learn the syntax of this function to know how to use better. Once you have that, remove all records in the Category column in the main data-set What is the vlookup syntax To have vlookup help with this task, create a category worksheet looking like this Whoever is typing in this data has to make sure they type the correct category, or the summary will not be accurate. To keep it simple, we only have 4 products: apples, oranges, cucumbers and broccoliĪs you notice, these 4 products are actually grouped in 2 categories: fruits and vegetables. This dataset contains the sales by day of every type of product they have in store. We’ll continue with our example of the Grocery Store that keeps track of the sales of fruits and vegetables for every day of the year. If you know vlookup and pivot tables you will find the most value out of Excel.
0 kommentar(er)
