Looking up cells
One thing I seem to do a lot is look up data, so it's good to know a few different methods.
The most common way of finding data in a table is to use VLOOKUP. Suppose we want to look up a tax rate from the table below. Suppose we've given the rangename TaxTable to the cells in blue text.
| Income | Tax Rate |
| 0 | 0% |
| 10,000 | 10% |
| 30,000 | 20% |
| 50,000 | 25% |
| 100,000 | 30% |
| 9,999,999 |
If we use this formula =VLOOKUP( 25,000, TaxTable, 2, TRUE)
we'll
get the answer 10%, because it looks for the figure in the first column which is the same as, or just less than, the number we asked it to find. 25,000 is above 10,000 but less than 30,000, so it uses the 10,000 line. We've asked it for the second column, so it returns 10%. If instead we look for 30,000, it will return 20%.
Note that for this reason, we have to provide a tax rate for the first row with income of 0, because this row will be used all the way up to 10,000, and the last row of 9,999,999 will never be used because no-one except Bill Gates is paid that much, so there is no need for a percentage in the second column.
Sometimes we want an exact match, eg if we are looking up details for a specific customer number. In that case, we use a similar formula to that above, but use TRUE as the final parameter.
HLOOKUP is similar to VLOOKUP, except that it searches across columns instead of down rows.
Index and Match
If there's one thing some Excel purists get excited about, it's using Index and Match to do lookups.
This formula will find us the tax rate in the first example above
=INDEX(TaxRates,MATCH(25,000,IncomeLevels,1)) where TaxRates is the rangename for the column of tax rates in the table, and IncomeLevels is the rangename for the first column showing income levels
he MATCH function looks up 25,000 in the column of income levels, and because it's been told to find the exact figure or the next lowest, it will pick the row with 10,000, and returns the figure 2 (ie 2nd row of the table). The INDEX function then takes the 2nd row of the IncomeLevels column, ie 10%.
OK, so it can do the same as VLOOKUP, but it's more complicated. So why use it?
- It's more powerful, because it can not only do exact lookups and the nearest lower figure, just like VLOOKUP, but it can also do the nearest higher figure, ie find the number which is equal to or greater than the figure we're looking for.
- In addition, VLOOKUP always searches in the first column of the range you give it, whereas you can use MATCH in any column at all, and INDEX in any other column, so you have more freedom.
- INDEX is not a "volatile" function, whereas VLOOKUP is, and so it will recalculate more often than it needs to
- You can do more complicated lookups
Note: Where possible, I've attributed ideas to the people who thought of them. If I have missed someone or made a mistake, please let me know.