pivotronics

In the first part of this VLOOUP series, I have taken you through a simple example of VLOOKUP where while searching for the lookup value, we were doing an exact match. Now lets see an example scenario of approximate match.

Scenario: You have a table showing the percentage of discount to be given to the customer based on the number of units of the product purchased as in the below screenshot, indicating that no discount is given if less than 50 units are purchased, 2% discount is given if 50 or more but less than 500 units are purchased, 10% discount is given if 500 or more but less than 1000 units are purchased, 15% discount is given if 1000 or more but less than 2000 units are purchased, and 20% discount is given if 2000 or more units are purchased.

Challenge: Now the requirement is that you will be given the units purchased and asked to arrive at the discount to be given to the customer. for example, if the number of units purchased by a customer is 700 units then the discount percent value to be returned is 10%. However, VLOOKUP is not intelligent enough at natural language processing that it can understand that >=500 and <1000 indicates all values between 500 and 1000 including 500 and excluding 1000. so when it searches for 700 in units purchased column, it will not find the value.

Solution: The option of approximate match for the last argument of VLOOKUP function comes to your rescue in such scenarios. Just that you need to adjust the units purchased column in a way that excel can understand it. To do this, you will insert a new column in the mid and write values as follows, as in the below screenshot:

• write 0 in first row and 50 in 2nd row implying units less than 50 units for first row.
• write 500 in 3rd row and 1000 in 4th row indicating units greater than or equal to 50 and less than 500 for 2nd row and also indicating units greater than or equal to 500 and less than 1000 units for 3rd row.
• write 2000 in 5th row indicating units greater than or equal to 1000 units and less than 2000 units for 4th row and also indicating units greater than or equal to 2000 units for 5th row .

Now lets write the VLOOKUP formula against Discount % in cell G24 so that it may lookup the value against Units Purchased in the cell G23 and return the percentage discount to be given:

=VLOOKUP(G23,C24:D28,2,TRUE)

Explanation:

first argument to the function –  WHAT to search/Lookup: cell G24 has the value to be looked up.

2nd argument to the function – WHERE to search/lookup: first column of the lookup table C24:D28 is where the lookup value is to be searched.

3rd argument to the function – WHICH column in the lookup table enumerating from left to write (corresponding to the row where the lookup value is found) has the value to be returned. Here discount percent to be returned is in the 2nd position as shown in the below screenshot.

4th argument to the function – How to search. Here, you want to first search for an exact value. If an exact value is not found, then search for the nearest available value that is less than the lookup value. example, if you are searching for 700 which is not exactly not found, it will look for nearest value less than 700. i.e., 500 will be the match in this case. so, we will specify 1 or TRUE here.

Testing: Now enter a value in cell G23, say 990. For 990, since it lies between 500 and 1000, it should return 10% discount. It correctly returns the value in cell G24. you can further test by entering other values in cell G23.

Caveats: In case of all approximate search types of VLOOKUP, ensure that the first column in the lookup table (i.e., the column where the lookup value is to be searched) is in ascending ORDER. Otherwise, incorrect values will be returned. This constraint is owing to the search technique (Binary search) used by Excel internally while searching for the lookup value in the first column of lookup table.

This is one use case for approximate match type VLOOKUP. Hope you enjoyed reading this post..!! Follow this blog by entering your email and hitting on FOLLOW button at the top right, to learn VLOOKUP in more variations in the next post.

Do leave your comments, if any…