Today we want to focus on having a non-exact match or rather an approximate value. Imagine we have a table like the one below listing our regional sales:
Sales per Region
| A | B | C | D | |
| 1 | Region | Office | Sales | Commission Bonus |
| 2 | East | New York | 2,385,673 | |
| 3 | South-East | Miami | 1,499,390 | |
| 4 | South | Dallas | 1,285,037 | |
| 5 | Central | St. Louis | 1,834,854 | |
| 6 | West | Los Angeles | 2,501,468 | |
| 7 | Nord-West | Seattle | 1,158,383 |
The second table is our "standard" commission bonus table, where we list boni that will be paid for ranges of sales: Commissions
| A | B | |
| 1 | Sales | Additional Bonus |
| 2 | 500,000 | 3.00% |
| 3 | 1,000,000 | 3.50% |
| 4 | 1,500,000 | 4.50% |
| 5 | 2,000,000 | 5.00% |
| 6 | 2,500,000 | 6.00% |
Display Excel tables online >> Excel Jeanie HTML 4
As we can see this second table has absolute values in column A where we will have no exact match with the sales column C of the previous sales per region table. But wait, as you may imagine, there is a solution :-) We enter the following formula into cell D2:
=VLOOKUP(C2;Commissions!$A$1:$B$6;2;TRUE)
By the way, if you don't want to enter the "$"-sign manually, simply click into each of the addresses in the argument field (A1 and B6 in this example) and press the F4 key on your keyboard after entering the cells which will "fix" the address of the cells for future copying of the formula.
This formula does not seem to be very different to the one we entered in the last post, but there is a major difference, the last argument we entered. Let's go through the arguments one by one:
The function name (VLOOKUP)
As mentioned before, this is telling MSXL that we have vertical oriented data.
The Lookup_value (C2)
Here we name our identifier, in this case the sales amount of the region.
The Table_array (Commissions!$A$1:$B$6)
Where will we search for matches? This is our matrix from which we receive the corresponding values.
The Col_index_num (2)
What column of our matrix we're searching in should be returned? In this example it's the second one holding the commission percentage.
The Range_lookup (TRUE)
And there we have it, our major difference. With this small change from FALSE to TRUE we tell MSXL that we are not looking for exact but approximate values. What is interesting in this context is the fact, that Excel does not take the closest value but the one, which is already "met". Take a look at cell C3, the sales for the soutch-east region in Miami. The value 1,499,390 is much closer to 1,500,000 than it is to 1,000,000. But MSXL still applies the value of the condition we already met and took the 3,50% value back from our Commissions table which is absolutely correct.
The finished table should look like this:
Sales per Region
| A | B | C | D | |
| 1 | Region | Office | Sales | Commission Bonus |
| 2 | East | New York | 2,385,673 | 5.00% |
| 3 | South-East | Miami | 1,499,390 | 3.50% |
| 4 | South | Dallas | 1,285,037 | 3.50% |
| 5 | Central | St. Louis | 1,834,854 | 4.50% |
| 6 | West | Los Angeles | 2,501,468 | 6.00% |
| 7 | Nord-West | Seattle | 1,158,383 | 3.50% |
| Formulas in the table |
||||||||||||||
|
Display Excel tables online >> Excel Jeanie HTML 4
I think that this function of MSXL is quite useful, as it supports us very well in all kinds of references not only absoulte ones like the one we talked about previously (with the "FALSE" Range lookup) but also when we want to see what reference values are located in what target ranges. We will dive deeper in using the LOOKUP functions of Excel in future posts. If you have suggestions on a specific topic I should cover, drop me a note or leave a comment. For what kind of purpose will you use this function? Leave a comment or send me a message and we can have a visitors showcase around here (of course with your name, website etc. mentioned, if you want to).
Discuss this blog post in the forums (click the blue button)
