VLOOKUP and MATCH combo function in excel
We already know about the VLOOKUP function in excel and its functionality. VLOOKUP basically search value based on the given criteria. There are few limitations in VLOOKUP like it picks only the first value available in the reference table, its columns are static. In this article, we will be discussing how to make a dynamic column range in VLOOKUP in excel.
The problem with static column number in VLOOKUP
Let us understand this by the below example.
In the above image, we can see that we are trying to get the Quantity value of Product ID 1108. The Quantity of product id is in the Quantity column which is the 4th column from Product ID. So we according put value 4 in column index number. Now let’s suppose we wanted to add one more column of initial status after the product name.
Now if we see the value in CELL K5 then it got changed from 16 to 95.8 which is the value from the column Unit Price. We also can see the value 4 now comes to the Unit Price column and the Quantity column came to 5th place from Product ID. So our VOLOOKUP values become wrong. What is the solution for this?
Creating a dynamic range for VLOOKUP
To avoid such type of error we will use the MATCH function in excel. The use of VLOOKUP and MATCH functions in excel make the dynamic range.
Difference between VLOOKUP and MATCH function
- VLOOKUP : VLOOKUP provides value as an output based on the lookup value given.
- MATCH : MATCH provides postion of any value based on the lookup value given.
How to use MATCH within VLOOKUP function
We will use our VLOOKUP inputs as used in the earlier example, just one change is required that is instead of using the fixed value in column index number we will use the Excel MATCH function. Let us understand this by the below example illustrated in the image.
In the above example, we used the MATCH function to find the position of the Quantity column in an array (B3:F3). Now to check if this works we will insert a column as we did earlier.
After inserting a column we can see that value remains the same. There is no change in the Quantity for Product ID 1108. Though we can see that there is a change in the array value of the MATCH function. Now it got changed from B3:F3 to B3:G3.
That is all for this article VLOOKUP and MATCH combo function in excel. Will meet again with some new topic.
You can also read about this combo function from the below link.
- https://www.excelcampus.com/functions/vlookup-match-dynamic-duo/
- https://www.excel-exercise.com/dynamic-vlookup-research/
You can watch the below video also for more clarification on this.
Pingback: How to return a value if the VLOOKUP value is in a range || Excel VLOOKUP ||DataWitzz - DataWitzz