So, you want to look something up in a table
Grab a copy of this example workbook
Excluding VBA, there are three main ways to look up values in tables to return corresponding data: lookup, vlookup and hlookup. So, let’s explore these and discuss why, inevitably, =Index(Match) is always the best option:
Consider this “table” (Range), both oriented vertically and horizontally.
Lookup ↗
=Lookup has been deprecated for quite a while as it requires your table to be sorted in ascending numerical or alphabetical order:
IMPORTANT: The values in lookup_vector must be placed in ascending order: …, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.
Ugh. Let’s review its syntax and usage anyway. It has two forms:
Lookup(lookup_value, array) [array form]
- lookup_value: what you want to look up.
- array: where you want to look.
This form will look vertically or horizontally, depending on the array you specify. If it’s taller than or equal to its width, it will perform a vertical lookup.
Here we use the array form to look up the prices:
=LOOKUP(C12,$A$2:$C$7) [vertical table]
=LOOKUP(C13,$H$1:$M$3) [horizontal table]
As shown, Lookup always returns the last value of your table – not much control.
Lookup(lookup_value, lookup_vector, [result_vector]) [vector form]
- lookup_value: what you want to look up.
- lookup_vector: where you want to look (only one column or row).
- result_vector : what you want to return (only one column or row). Optional
Again, this can be used vertically or horizontally and has more control over what to return. Above it is used to lookup the inventory:
=LOOKUP(C12,$A$2:$A$7,$B$2:$B$7)
=LOOKUP(C13,$H$1:$M$1,$H$2:$M$2)
But, what happens if we sort the table by inventory rather than fruit?
Disaster strikes! Don’t use lookup.
Vlookup ↗
Vertical Lookup
Vlookup(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: what you want to look up
- table array: the array you’d like to search with the leftmost column being where the lookup_value can be found
- col_index_num: which column of the array you’d like to return.
- range_lookup: Boolean Approximate or exact match. Optional.
One common mistake is that the col_index_num is the column in reference to your array, not the entire worksheet.
For range_lookup you use TRUE for the closest match in the table and FALSE for only an exact match. I don’t consider it optional – it defaults to TRUE which requires the table to be sorted ascending. You’ll see I use 1 for TRUE and 0 for FALSE – it’s just a habit I picked up.
=VLOOKUP(C14,$A$2:$C$7,2,0)
=VLOOKUP(C14,$A$2:$C$7,3,0)
=VLOOKUP(C15,$A$2:$C$7,2,0)
=VLOOKUP(C15,$A$2:$C$7,3,0)
It’s actually not a very complicated function and it’s extremely powerful for users; learn it and don’t be intimidated.
Hlookup ↗
Horizontal Lookup. You can use this to search through the ever-elusive horizontal table. I guess if you’re an accountant you might need this.
Hlookup(lookup_value, table_array, row_index_num, [range_lookup]
- lookup_value: what you want to look up
- table array: the array you’d like to search with the topmost row being where the lookup_value can be found
- row_index_num: which row of the array you’d like to return.
- range_lookup: Boolean Approximate or exact match. Optional.
Again, this has the same pitfalls and advice as Vlookup.
=HLOOKUP(C16,$G$1:$M$3,2,0)
=HLOOKUP(C16,$G$1:$M$3,3,0)
=HLOOKUP(C17,$G$1:$M$3,2,0)
=HLOOKUP(C17,$G$1:$M$3,3,0)
If you learn VLookup, you’ll know HLookup. But my advice is just don’t make horizontal tables.
So, what’s this Index(Match) thing I mentioned? Take a look.