Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Why not to use HLOOKUP and VLOOKUP functions.
Download this lesson’s related exercise files.Don't Use HLOOKUP and VLOOKUP.xlsx
12.1 KB Don't Use HLOOKUP and VLOOKUP - Solution.xlsx
Don't Use HLOOKUP and VLOOKUP
Discover why to avoid VLOOKUP & HLOOKUP functions in a formula.
When to use
When looking up data in a table – two functions most modelers are very familiar with are VLOOKUP and HLOOKUP. When using these functions it’s very easy to make a mistake.
VLOOKUP has the following syntax:
- Lookup_value: What value do you want to look up?
- Table_array: Where is the lookup table?
- Col_index_num: Which column has the value you want returned?
- [Range_lookup]: Do you want an exact or an approximate match? This is optional and to begin with, I am going to ignore this argument exists.
HLOOKUP is similar, but works on a row rather than a column basis (horizontal rather than vertical).
- In this above example, the formula in cell C13 seeks the value 2 in the first column of the table D5:H9 and returns the corresponding value from the fifth column of the table (returning 3,840).
- Problems arise if columns are added or removed from the table range
- With a column inserted, the formula contains hard code (5) and therefore, the eighth column (H) is still referenced, giving rise to the wrong value.
- Deleting a column instead is even worse: Now there are only four columns so the formula returns #REF
- It is possible to make the column index number dynamic using the COLUMNS function:
- Duplicate values in the lookup column can also create issues. With one duplicate, the following happens: Here, the second value is returned, which might not be what is wanted. With two duplicates the same thing happens and the third value is returned: When there are three duplicates, the value returned is the fourth of five. The problem is, there’s no consistent logic and the formula and its result cannot be relied upon.
Lesson notes are only available for subscribers.