Although VLOOKUP in Excel is a very useful function, it suffers a drawback. If the lookup value to be searched is not in the first column of lookup table, VLOOKUP wouldn’t work. In the last part of this learning series, titled “VLOOKUP: Right to Left Lookup” I have taken you through a trick to address this. Here is another one.
Hoping that you understand the functioning of MATCH and INDEX functions, let’s understand how the combination of INDEX and MATCH as an efficient alternative to VLOOKUP function in terms of addressing the issue if the column in the lookup table where the lookup value is be searched is not in the first position.
Example Scenario: Below are the customer names and their respective phone numbers. you are given a customer name and asked to lookup the customer name from this table and return the phone number of the respective customer.
Challenge: However, the customer name column in the lookup table from which the lookup value will be searched is not in the first position. see the screenshot below:
Solution: Here is the formula of combination of INDEX and MATCH functions to address this issue (see the below screenshot: =INDEX(B50:B60,MATCH(F49,C50:C60,0))
Explanation: Any nested formula is understood by understanding it from the inside to outside. i.e., the function that is nested most inside need to be understood/evaluated first and then proceed to evaluating the function nested at next higher level. So, in our example, we will understand and evaluate the MATCH function first.
Inner Function: MATCH(F49,C50:C60,0) – Here MATCH searches for the value passed in the first argument to it (i.e., value in cell F49), in the array C50:C60 and then returns number corresponding to its position in the array by counting from top to bottom in the array. so, here, it searches for “rathod” in the array C50:C60 and returns 4, since the value “rathod” appears in 4th row starting from the top of the array.
Outer Function: INDEX(B50:B60,MATCH(F49,C50:C60,0)) – Now our outer function, INDEX goes to the row number represented by its 2nd argument in the array represented by its first argument and then returns the value present in that row of the array. In our example, after the MATCH function is evaluated, the formula becomes – INDEX(B50:B60,4). Now the INDEX function goes to the 4th row in the array B50:B60 and returns the value present in that row. i.e., 7174683825 is returned.
Testing: You can further test by changing the value in cell F49 to another customer name.
Hope you enjoyed reading this post..!!
Wondering if there are more such tricks in Excel..? No doubt, inumerable..!! Follow my blog to learn more tips, trips and techniques in MS Excel, by entering your email and then hitting on FOLLOW button at the top right. Happy learning.. 🙂
Do leave your comments or questions, if any..