In my earlier Excel tutorial post, we have learned how to use vlookup function. In this post, I will show you simple tweak through which you can make VLOOKUP function more effective. This will be useful for you when you want to map huge data.
For example you have base table with more than 20 or 30 columns. From that, you need to fetch selected 5 or 6 columns in different part of the sheet or in different sheet itself. For doing this we need to use MATCH worksheet function along with VLOOKUP function.
Using Excel VLOOKUP with Match Formula:
To make this simple I am taking a small table which has 4 columns for better understanding. From there we need to fetch only second column data and the 4 th column data.
So i am using the VLOOKUP formula as mentioned below.
=VLOOKUP($A9,$A$1:$D$6,MATCH(B$8,$B$1:$D$1,1)+1,FALSE) in column B9 and copy the same in C9, B10 and C10 also. In cell B9 it returns 31% which is the % of Commision of Banana from the table and in C9 it returns 120 which is quantity. Similarly, it returns 15% and 50 in B10 and C10 respectively.
Explanation on the Above VLOOKUP formula with MATCH function:
The lookup value column is made constant and the data range both rows and columns made constant. So where ever we paste the data, the lookup range should be the same and lookup value will be on the same column but it may be in different rows as we are not made row index constant.
The third argument of vlookup, ‘index_number’ which refers to the column from where vlookup should fetch the data, we have used MATCH function. The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. In our example, match function returns relative position of the required column header in the headers of the our main database headers. The MATCH function position number starts from 0 and VLOOKUP starts with 1. So we are adding 1 to the relative position returned by MATCH function.
As I said earlier, this is the one of simple yet very useful combination of formulas in a real time excel profession. I have faced many situation in my professional life, where it takes a huge time if we use classic vlookup function to fetch few data from a huge database. This saves our precious time also accuracy as there is very less chances for the error. Do let me know, if this Excel tip helpful for you and also if you face any problem using this tip.
