What is it
If you have a set of data that you want to reference elsewhere then VLOOKUP is a brilliant function. Some uses for this are when you:
- Have a list of clients and want to be able to pull a clients details (address, phone number, annual sales, etc) into another location
- Want to pull standard values into a formula
- Need repeatedly do the same conversion, for instance 1=January, 2=February etc
VLOOKUP works for vertical lists whereas HLOOKUP works for horizontal tables.
How does it work
VLOOKUP makes excel run down a vertical list looking in the left hand column until it finds the required reference and then counts across a specific number of columns and then returns the item it finds.
HLOOKUP works in the same way except the data is arrange horizontally and it looks in the first ROW for the matching data and counts DOWN the number of defined rows. In this blog we will use VLOOKUP however all the techniques are the same for HLOOKUP.
=VLOOKUP(what to find, where to find it, how many columns to count across, true or false)
The true or false defines how specific VLOOKUP is about what to find:
- False = find only the exact match.
- True = find the closest (smallest) numerical match.
Looking at the example below VLOOKUP is looking in the left hand column (A) of the dataset A1:B12 for the value in E1 (“3”). As soon as it finds this it counts 2 columns over (the first column is number 1) and returns the answer “March”.
Avoid having duplicate values
A key thing to remember is that VLOOKUP runs down the list and stops when it finds a match. So if you have multiple references the same there is no way to force it to find the 2nd, 3rd or last matching item. This also means that if you have the true/false section set to True then it is vital you have the data sorted by the 1st column or you may not get the results you expect.
If you have duplicate values in the index column it can be useful to make a unique reference by combining different columns into a unique key.
Using True to set thresholds
The TRUE version of the formula is useful when dealing with thresholds or less exact data sets. A classic is grading scores:
In this case the formula in E2 =VLOOKUP(D2,$A$2:$B$7,2,TRUE) and then copied down to cover all the scores in the list in column D.
Looking at the scores to see how “TRUE” works:
- 60: VLOOKUP finds and exact match and so returns “A”.
- 59: no exact match is found so VLOOKUP returns the value for the next smallest number (40) and so returns “B”. This is why TRUE only works with sorted numerical lists.
This behaviour of looking for a lower number in the Index column explains why you need to have the lowest number defined but not the upper value. If the 0 is omitted a score of 10 will generate an error (as there’s no match for 10 and no lower number to use. However a score of 80 is fine as there is a lower number to find in the list.
What do the errors mean?
#N/A
No match found. If using the false this just means that an exact match can’t be found. If you look at the list and think that there should be a match check:
- There are no spaces before or after the ‘matching’ value in the data set or for the item you’re trying to look up
- If the data and lookup item are numbers check that excel also sees them as numbers. Use the =ISNUMBER(cell reference) in a couple of blank rows to check that they’re both numbers. Sometimes a formula can return a number but show it as text (for instance a LEFT command to strip out a number from something else). In this case simply adding *1 to the calculation will force it to be reported as a number.
- Again if they’re both numbers check that there isn’t a rounding issue, maybe 4.99999 is showing as 5?
Something to note is that this error can be very useful. If for instance you want to check that a name is not in a list then you can use the ISERROR(VLOOKUP(……..)) to return a TRUE value for use in an IF statement.
#NAME?
If you are using a named range rather than $A$2:$B$7 and this range doesn’t exist (misspelt for instance) then this error will be returned
#REF!
This error tends to happen if you’ve asked for more columns than exist in the data set. So for instance if I use the formula =VLOOKUP(D2,$A$2:$B$7,3,False) this error will result.