## So, you want to look something up in a table better

Grab a copy of this example workbook

Last time, we explored the three main ways to look up values in tables to return corresponding data: `lookup`

, `vlookup`

and `hlookup`

. As I said, **“inevitably, =Index(Match) is always the best option.”** If you’re anything like me, once I knew

`vlookup`

like the back of my hand, I figured *why do I need this?*

**Versatility**

Consider the same “table” (Range), both oriented vertically and horizontally.

### Index ↗

**index(array, row_num, [column_num])**

**array**: The “table” range to reference**row_num**: The row number of the element you want*[column_num]*: The column of the element you want. Optional.

The first thing you notice is that, no, this can’t *find* a value. All it does is return a value in an array. Using the vertical array above:

```
=Index($A$1:$C$7,1) = #REF
=Index($A$1:$C$7,1,3) = "Price"
=Index($A$1:$C$7,3,1) = "Bananas"
```

### Match↗

**match(lookup_value, lookup_array, [match_type])**

**lookup_value**: What you want to look up.**lookup_array**: Where you want to look.*[match_type]*: How you want to match the lookup_value. Optional.

**match_type** can be 1 (default), 0 or -1. Just consider **1** to be the same as `TRUE`

in vlookup as it will behave the same way (Price Is Right rules: the closest without going over). **0** will only return an exact match. **-1** is basically a vlookup with `TRUE`

that searches from the bottom of the list rather than the top. The list must be in ascending order for **`** and descending order for **-1**.

Ah, a *lookup_value,* that looks familiar! The difference is that `match`

returns a number – the location of your lookup_value. Consider the vertical array above:

```
=MATCH("lemons",$A$2:$C$7,0) = #N/A
=MATCH("lemons",$A$2:$A$7,0) = 5
=MATCH(70,$B$2:$B$7,0) = 2
=MATCH(1.25,$C$2:$C$7,0) = #N/A
=MATCH(1.25,$C$2:$C$7,1) = 1
```

You see that `Match`

only works with a 1 dimensional array, in this case a column, and it returns the *row_num* of the match. This is the *position* of the match within the list.

Now watch what happens With the horizontal array above:

```
=MATCH("cherries",$G$1:$M$1,0) = 4
```

It’s returning the *col_num* as the *position* in the list. You see, both arrays can be thought of as *lists*. Regardless of whether it’s a vertical or horizontal list, `match`

returns your lookup_value’s number on the list. “Cherries” is the 4th member of the horizontal list above, but its actual column position is 10. This is an important rule to remember, **your result will give you answers relative to your array, not your worksheet**.

### Index(Match)

We have two arguments we can use in `index`

– *row_num* and *column_num*. And we can return the position of a value in a list vertically or horizontally with `match`

. You know where this is going, don’t you? You can index your results relative to your data array and return the value from a position *relative* to your array. So if you want to look up the inventory for “lemons” in the vertical array-

```
=INDEX($B$2:$B$7,MATCH("lemons",$A$2:$A$7,0)) = 90
```

The `match`

replaced the `row_num`

argument in `index`

. Wait, what?

```
=MATCH("lemons",$A$2:$A$7,0) = 5
```

So we want the 5th item in the inventory list

```
=Index($B$2:$B$7, 5) = 90
```

### Conclusion

It’s *that* simple. Use `match`

to get the position of what you want in the column you’ve indexed. *But how’s that different than vlookup?* I said it before – **versatility**. Suppose you want to know what fruit is the most expensive fruit under $3.00. How would vlookup return a column *to the left* of Price? Let me tell you – not very easily. `Index(Match)`

to the rescue!

```
=INDEX($A$2:$A$7,MATCH(2.99,$C$2:$C$7,1)) = Coconuts
```

If you grab a copy of the example workbook, you’ll see how to use `Index(Match)`

on the horizontal table, but why not give it a try before looking at the answer? I’m confident you’ll get it with the information above!