Tools: functions VLOOKUP, HLOOKUP
Level: intermediate
Problem
The property tax rates established by Buttercreek City Council are 0% for all real estate in zip code 88880, 0.1% for all properties in zip code 88881, 0.15% for all properties in zip code 88882 and 0.2% for all properties in zip code 88883. How does one determine the appropriate tax rate for a given property location?
Solution
Typically, an Excel user will think of a straightforward decision tree built as a nested IF statement looking like the following. Cell A1 contains a property location variable.
=IF(A1=88880,0,(IF(A1=88881,0.001,(IF(A1=88882,0.0015,0.002)))))
This formula is not the best solution, as it has several problems
- It is complicated to write, enter or edit, especially easy to miss the brackets.
- If a condition is changed, or a new condition is added, all instances of the formula must be changed. Manually. You cannot use a range with tax figures and thresholds.
- You can only nest a formula a limited number of levels (for Excel 2003 the limit is 7, for Excel 2007 and 2010 it is 64).
- It is UGLY.
As a matter of fact, the decision tree problem quickly boils down to choosing from a list.
Excel contains a special CHOOSE function for choosing a value from a list. Using it will result in something like this. Cell A1 contains the number of position in a list.
=CHOOSE(A1,0,0.001,0.0015,0.002)
It is not much better, though:
- Every argument must be entered separately and manually.
- CHOOSE second and subsequent arguments cannot refer to a range.
- The value is chosen by its position in the list, no criteria are involved.
- In Excel 2003, CHOOSE can take only 30 arguments.
Is there a way to just type a list of zip codes and tax rates and pull out a rate?
Use VLOOKUP function with a list. VLOOKUP stand for "Vertical Lookup"
- Create a new service sheet and enter a list
- (OPTIONAL) Name range A2:B6 e.g. property_tax.
- (OPTIONAL) Name range A1:A1 (the cell where the zip variable is located) e.g. zip.
- Enter a formula: =VLOOKUP(zip,property_tax,2,0).
| A | B | |
| =VLOOKUP(A1,property_tax,2,0) | ||
| ZIP | TAX | |
| 0.00% | ||
| 0.10% | ||
| 0.15% | ||
| 0.20% |
What the VLOOKUP formula does: it compares the value in zip (argument "zip") with values in first column of property_tax (argument "property_tax") and then returns the value in the same row and nth column of the range in our case, it is a second column (argument "2"). The last "0" argument is optional and forces VLOOKUP to find an exact match in Column 1.
If for some reason your data is a horizontal, rather than a vertical range, HLOOKUP formula must be used instead. The formula syntax is exactly the same, except that, as name suggests, HLOOKUP is "horizontal" and so finds a match in first row and then brings the value from the nth row below.
|
|
|
| A | B | C | D | E | |
| =HLOOKUP(A1,property_tax,2,0) | |||||
| ZIP | |||||
| TAX | 0.00% | 0.10% | 0.15% | 0.20% |
Things to keep in mind:
If your list contains non-unique criteria values, VLOOKUP and HLOOKUP find the FIRST match and stop. When you set up a decision tree, make sure you criteria do not overlap and use the optional range_lookup switch wisely.
VLOOKUP searches only to the right and HLOOKUP down. Their criteria range is always the first column and the first row, respectively. If you need to use for your criteria a row or column in the middle, use another solution. We will provide some later.
In my experience (and experience of most Excel masters), vertical ranges are much easier to use and handle, and keep in mind you have much more room in your spreadsheet down than right, too. Excel 2010 gives you only 65,536 rows by 256 columns, and earlier versions have even less.
Formula syntax
| Code | Variable | Comment |
| =VLOOKUP( | ||
| zip | Lookup_value | Value that you are looking for in the table_array |
| property_tax | Table_array | Table where the Lookup_value and the values that correspond to it are located |
| Col_index_num | Number of the column in the Table_array where the value that you want the formula to return is located | |
| Range_lookup | Optional: 1 (default) is an approximate match, 0 is an exact match | |
| ) | Formula complete |
Similar tasks
Task 1. You have a list of monthly average rainfall levels for Sydney.
| A | B | C | |
| Month | mm | inches | |
| Jan | 103.4 | 4.1 | |
| Feb | 111.0 | 4.4 | |
| Mar | 131.3 | 5.2 | |
| Apr | 129.7 | 5.1 | |
| May | 123.0 | 4.8 | |
| Jun | 129.2 | 5.1 | |
| Jul | 102.8 | 4.0 | |
| Aug | 80.3 | 3.2 | |
| Sep | 69.3 | 2.7 | |
| Oct | 82.6 | 3.3 | |
| Nov | 81.4 | 3.2 | |
| Dec | 78.2 | 3.1 | |
| Year | 1222.7 | 48.1 |
Source: GHCN, based on 1790 months between 1840 and 1989
If you know the month, how do you get the average rainfall in inches from this table?
Solution
=VLOOKUP(Aug,A1:C14,3)
Task 2. You have a list of public debt of European Union member states (in billions of euro).
| A | B | |
| EU member country | ||
| Germany | 2,088,472 | |
| Italy | 1,897,179 | |
| France | 1,717,256 | |
| United Kingdom | 1,547,506 | |
| Spain | 734,961 | |
| Netherlands | 392,506 | |
| Belgium | 361,735 | |
| Greece | 355,617 | |
| Austria | 217,399 | |
| Poland | 192,672 | |
| Portugal | 184,291 | |
| Ireland | 169,264 | |
| Sweden | 150,484 | |
| Denmark | 111,766 | |
| Norway | 101,533 | |
| Finland | 93,030 | |
| Hungary | 72,134 | |
| Czech Republic | 60,798 | |
| Romania | 44,607 | |
| Slovakia | 29,911 | |
| Slovenia | 16,954 | |
| Cyprus | 12,720 | |
| Lithuania | 11,827 | |
| Iceland | 10,140 | |
| Latvia | 8,618 | |
| Luxembourg | 7,786 | |
| Bulgaria | 6,286 | |
| Malta | 4,600 | |
| Estonia |
|
|
|
Source: Eurostat, 2012
How do you retrieve from this table the debt of Italy?
Solution
=VLOOKUP(Italy,A1:B30,2)
