This tip is provided by the Centre for Enhancing Learning through Technology (CELT). The images in this post will enlarge when clicked – use the Back button to return to the post.
It seems as though the Excel tips of recent weeks are being well received so in-keeping with the theme, today’s tip is about the Lookup functions in Excel and Absolute references.
Using the lookup functions we can get Excel to search for values within a range and return a result based on a fixed set of criteria. This can be useful when have a large sheet of data and we want to, for example, append the data with extra information.
There are two Lookup functions in Excel; Vertical (up and down columns) and Horizontal (along the rows). The example that follows uses the VLookup (vertical) function to interrogate a list of fictional exam results and return the grade (as defined in a seperate table) in the empty column.
| These are the data we’ll be working with. I need the Grade column (column E) to be populated but I don’t want to manually type all of those grades in! So I have created a separate table (in columns G and H) with the potential grades and some matching marks. The name of the game here is to use the VLookup formula in column E and tell it to use the smaller table (the Array) to fill column E in with something depending on what it finds in column D (the result) |  |
| When using VLookup I generally use the wizard – to open this click on Formulas>Insert Function>VLOOKUP before clicking OK |  |
| With my cursor in cell E:2 I have opened the wizard and this is my view. This can be a bit daunting to begin with – here’s my understanding of the different elements:
Lookup_value In our example this is the result in column D. Essentially, what we want Excel to look for in our little Array Table_array This is the little table I created with the potential grades and marks in columns G and H Col_index_num This is the confusing one! Excel usually likes to use letters for column labels but here it wants to use numbers. This field relates to the column in the Array where the, in this example, the grades are stored. Our Array is a two column table and the grades are in the second column so we need to put a “2” in this field Range_lookup enables us to add some flexibility. If we leave this blank Excel will return a grade based on the Score that is closest to the Result. This is fine as our student results won’t fit neatly into our seven options within the Array. However, be careful with this as you may need Excel to find exact values when looking up data in a column – if this is the case type the word “FALSE” in this field |
 |
| Here’s how the wizard looks when completed as described |  |
| Having clicked Ok we now have a grade in Christine Alderman’s Grade column. To recap, cell E:2 is looking at D:2 and then seeing if there’s anything close within the Array (column G) and if so returning whatever sits in column H accordingly. Therefore Christine has a result of 87, this is closest to 85 so Excel returns an A* – well done Christine |  |
| The formula looks like this |  |
| Now we have a formula in place, to make life easier we can drag this down column E and get Excel to do the heavy work for our entire list – however, something doesn’t look right |  |
| This is the formula for row 10 (patricia Rowe) – notice that although Excel is looking at patricia’s Result (column D) the cells in which it thinks our Array lives have changed. Our Array is in range G1 to H8 but patricia’s row thinks our Array is from G9 to H16 – no wonder it can’t find anything |  |
| There’s an easy way to fix this; before dragging the formula, we need to make the cell references for our Array ‘Absolute’ or fixed. To do this we use the dollar sign “$” to fix both the columns and rows for our Array. We can amend the first formal that was created by the wizard by typing in the Formula Bar – I have added four dollar signs to the formula |  |
| This time, when I drag the formula down the Table Array will stay fixed and a full set of grades will be populated; bad luck Heather, patricia and Ashley!
Absolute references can be used in all parts of Excel when we’re dragging formulae around – it makes life so much easier when we need to fix a part of a formula |
 |