Using Formulas to Look Up Data Excel Tutorial
Excel 2010 Training -
Working with Data
Free Online Microsoft Excel Tutorial
* Using VLOOKUP
Formulas like VLOOKUP, and INDEX-MATCH make it possible to look up and match data from one sheet to the next and removes the need to manually find information contained in your worksheets.
Test your Excel skills with the corresponding FREE Online Multiple Choice
Using VLOOKUP & INDEX-MATCH Test
* Using VLOOKUP
VLOOKUP is a great time saver. This powerful formula allows you to find information based on specific criteria and returns the desired value. For example, you may have a worksheet containing all the products and their product codes of items in storage. On a second sheet you want the product code to come up automatically when you type the product name. VLOOKUP solves this for you and saves you the time of having to go backwards and forwards between sheets or having to find the product codes manually.
The syntax (formula arguments) for VLOOKUP looks like this:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Don’t panic! The above formula may look intimidating, but in reality it is very simple.
Let’s look at it step by step:
Study the screenshot above. This is a simple worksheet containing a product name, product code and product price. We have an invoice template on a second worksheet and want to be able to type in the product name and have the product code and price be populated automatically.
To do this we use the VLOOKUP function:
1. Click in the cell in which you wish the lookup result to appear. In our example this is in the Invoice sheet in the cell where the corresponding product code needs to appear.
2. Insert the formula:
- =VLOOKUP( - this is the name of the function.
- lookup_value – click in the cell that will contain the value you want to look up. This is not the result value. In our example in the screenshot below, you would click in the cell where you will be inserting the name, or which already contains the name, of one of the products (for example gherkins).
- table_array – select the table that contains the value you are looking up as well as your reference value. In our example this will be the entire Product List pictured in the screenshot above.
- col_index_num – this is the number of the column in which the value you are looking for is located. In our example, we are looking up the product code which is located in column B in the screenshot above. We will therefore enter the number 2, for this part of the formula as it is located in column 2 (Code).
- range_lookup – this evaluates whether you are looking for an exact match of the lookup value or an approximate value. It is rare that you would use an approximate match and it is safe to usually just enter FALSE for this part of the formula.
3. The completed formula in the Invoice will look like this:
4. The lookup value Gherkins is contained in cell A15. The Product List reference contains the corresponding value we are looking for, the Product Code for Gherkins. This value is contained in column 2 of the Product List.
5. Make the reference to the table_array absolute. In our example, this is the Product List reference A2:C17 above. Remember how to do this? Select the cell reference(s) and push the F4 key on your keyboard. A dollar sign preceding the row and column reference means it is an absolute reference. This is to ensure that if you add values to the product list or move data around, Excel will still lookup the correct value.
6. Copy the formula down in the column. In our example, we would drag the fill handle of the formula and copy it down the entire Code column of the Invoice.
7. We can repeat the same VLOOKUP formula in the Price column of the Invoice by changing the column_index_num to the column number that contains the value we wish to lookup. If you look at the screenshot of the Product List above, you will note the Price value is contained in column C, which means the column_index_num is 3 as it is the third column from the left in our table_array.
There are multiple benefits to using VLOOKUP. It allows you to quickly find and match information across worksheets, to auto-populate cells without having to manually find the data and it ensures that data is consistent across multiple worksheets. For example, should we change the price values on the Product List, it will automatically update on the Invoice.
Our completed invoice using VLOOKUP, will look like this:
In the screenshot above, we have VLOOKUP formulas in the CODE and PRICE columns. This means if you type the product name in the PRODUCT NAME column, it will automatically populate the CODE and PRICE columns with the lookup values from the Product List worksheet. If you change any of the product names on the Invoice, the lookup values will automatically update. If you changed the price or product code of any of the products on the Product List, it will automatically update on the Invoice.
You are not limited to using VLOOKUP for invoices only. You can use VLOOKUP to analyze data, create different lists and match values in many different scenarios. It is worth experimenting with creating your own examples and using VLOOKUP to solve them.
NB: To use VLOOKUP the values you are looking up must be contained in a column. The reference value must be contained in the first (left) column of the table array you select. In our example above, the Name column was the first column of the Product List table array selected.
* Using INDEX-MATCH
Index-Match is considered the next generation of formulae for looking up values and in many ways is superior to the traditional VLOOKUP function described in the section above.
The INDEX function returns a value based on a specific range of cells, column or row number. To use INDEX:
1. The syntax (formula arguments) for INDEX is:
- =INDEX( - this is the name of the formula.
- array – this is the selected cells or table containing all the data that is relevant to your values.
- row_num – the row number (position) of the cell containing the required INDEX value.
- column_num – the column number (position) of the cell containing the required INDEX value.
2. For example, in the screenshot below, if we wanted to return the INDEX value for the highlighted cell: The formula: =INDEX(A1:C16, 5, 3) will return the value $45 (see screenshot below):
If we entered the formula as =INDEX(C1:C16, 5), it will return the same value.
The above is all good and well, but if you wondered: what’s the point of doing this? You are not alone! The problem is, we would not necessarily want to spend time entering the INDEX location for each value we want to look-up. Then we may as well do it manually or use VLOOKUP. So how do we get Excel to automatically find the position for the value, for example the value stored at 5 in the formula above? We need another formula to calculate this for us: For this we use the MATCH function.
1. The syntax for the MATCH function is:
- =MATCH( - this is the name of the function.
- lookup_value – this is the value of the cell, the position of which you are looking for.
- lookup_array – this is the group of cells within which the value falls.
- match_type – select whether the MATCH must be less than, greater than or exactly the same as the lookup value. The usual answer to this is 0, which means Exact Match.
2. We can use MATCH to return the position for the highlighted cell below by entering the formula:
=MATCH(A19,C1:C16,0) in cell B19 under the heading Result (position):
3. This formula returns the position of the lookup_value contained in cell A19 (45). The position of the lookup value is row 5.
On their own, the INDEX and MATCH functions may appear of little use. However, marrying the two up makes for a formula much more powerful than VLOOKUP.
1. The syntax for using INDEX-MATCH is:
- =INDEX(array – this is the column containing the value I want as a result (the price column in the example screenshot below)
- MATCH(lookup_value – this is my reference value that I want to lookup (coffee in the example screenshot below).
- lookup_array – this is the column in which my reference value is located (the name column in the example below).
- match_type – enter 0 for an exact match.
2. The screenshot below demonstrates this formula in action. If we wanted to return the Price value of a product, in our example coffee:
3. The Lookup Result returned by this formula is matched to the Lookup Value. This formula works in the same way as VLOOKUP in that you can use it to look up corresponding values across multiple worksheets.
There are many benefits to using INDEX-MATCH rather than VLOOKUP, the major one being that INDEX-MATCH can be used in looking values up that are positioned in columns from right to left, whereas VLOOKUP can only be used to lookup values that are positioned in columns from left to right. This means if you wanted to use a VLOOKUP in certain instances, you would have to rearrange your data in the correct order for the formula to work.
It is worth practicing the above a couple of times. The syntax for formulas can be difficult to remember, especially if you don’t use it all the time. Remember to take our quiz!
Now you have done the tutorial...* TRY THE NEXT TUTORIAL: Formulas to Manipulate & Clean Data
* TRY THE NEXT TEST: Formulas to Manipulate & Clean Data