Lookup, VLookup & HLookup

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.

enter image description here


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.

enter image description here

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?

enter image description here

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.

enter image description here

=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.

enter image description here

=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.

Leave a Reply

Your email address will not be published. Required fields are marked *