As its Halloween this weekend we thought we would dedicate this week’s blog post to something that people find particularly scary! Something that sends chills down your spine! OK, I am being a little dramatic but it is fair to say that when it comes to Excel, Formulas are the thing that tend to strike fear into the heart of most.
Fortunately, formulas have never been easier or more human in Excel which means you don’t have to be a mathematical genius to understand them. Today we are going to take a look at ‘Using VLOOKUP to extract data’, a really common and useful formula which will help you cross-reference spreadsheets and save a lot of time.
Using VLOOKUP to extract data
VLOOKUP is a great time saving formula.
Let’s set the scene…
In this spreadsheet, I have a table (Table 1) that lists Items in column C and their associated unique Item Number or ID in column A. When a customer orders an item, I log the Item Number and the Customer Name in a separate spreadsheet (Table 2). However, the Item Name is not listed. I am going to use VLOOKUP to quickly and easily reference the Item ID and pull back the Item Name.
STEP ONE: Name your lookup range
This step is optional. You do not have to name your lookup range but I find that this is the best way of ensuring that your data pulls through correctly and it means that you don’t have to mess around making cell references absolute (fixed).
- Highlight the lookup table (the table that contains the data you are pulling through, in this case Table 1)
- Click in the Name box and type a name for your table i.e. PRODUCTS.
- Press Enter
NOTE: It is worth noting that table name cannot have spaces. If I wanted to name it ‘PRODUCT KEY’ I would need to use the format ‘PRODUCTKEY’ or ‘PRODUCT_KEY’.
STEP TWO – Create the VLOOKUP Formula (the scary part)
- Click in the cell where you want the value to appear (C12)
- Click on the Formulas ribbon
- Click Insert Function
- Enter the criteria into the fields
Lookup_value – The lookup value is the information that you want to reference. It is normally a piece of information that is in both tables. Excel needs a reference point and in this example we are using the Item ID.
- Click the cell that contains the first Item ID (A12)
Table_array – The table array is the table Excel is referencing. Excel needs to know which table to find the Item ID. This is where naming your table in Step 1 comes in useful.
- Press F3
Any named tables will appear in a list. Select the table ‘PRODUCT’. If you did not name your table, you would need to highlight the cells instead.
Col_index_num – This is the column in the lookup table that contains the data you want to pull through. In our example, we need the ITEM NAME. This information is contained in Column 3.
- Enter ‘3’
Range_lookup – This field can be left blank or will contain the value ‘TRUE’ or ‘FALSE’. Which one you use depends on how your table is sorted and the data you want to pull through. If you have your tables sorted in Ascending order, you can leave this field blank or use TRUE. TRUE also searches for an exact match. In our example, my data is not sorted and so I need to enter FALSE. In my experience, most data that you are dealing with is not sorted nice and neatly and so FALSE is the more common value to go in here.
- Enter FALSE
- Click OK
If the formula has been added correctly, you should now see the item name in cell C12. Excel has essentially looked in the lookup table for the Item ID and then pulled through the corresponding Item Name.
Once you have used the helpful dialogue box a few times and you are comfortable with the format, you can just type the formula into the cell. E.g. =VLOOKUP(C12,PRODUCTS,3,FALSE)
You could repeat the above steps to complete the rest of the table but that can be quite tedious especially if you are dealing with a lot of data. To quickly copy the formula down, you can use the AutoFill handle in the bottom right-hand corner.
- Drag the handle down OR double-click on the handle
TIP: If you get an unexpected result when using the auto-fill handle, such as the wrong data being pulled through or the same Item Name repeated, it is worth checking that you do not have your Auto-fill settings set to manual.
- Click the Formulas ribbon
- Click Calculation Options
- Set to Automatic
So as you can see, Excel formulas do not have to be a scary prospect.