## 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 ↗

**V**ertical **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 ↗

**H**orizontal **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.