Two ways LOOKUP formula to extract values from left & right (INDEX & MATCH FUNCTIONS)

I am sure if you’re using Excel at work, then you must have used lookup function (LOOKUP, VLOOKUP & HLOOKUP). There is no doubt that these are most useful & beautiful functions provided in the excel package. But if you haven’t used these function yet, let me take the honor of telling you that a lookup function return a value from a given table by looking up another value in the same table.

Because of our habit of entering data in very common style, VLOOKUP is among the most used function of Excel. But there is a limitation that it cannot go left and with all the LOOKUP functions is that they only look in the left column and return a from a cell to the right. So what I mean by this is LOOKUP function in Excel are useful for one-way lookups, there is function (inbuilt) for two-way lookup.

Today after reading this post you will be able to conquer this limitation, I am sure of that! First I would like you to look at the data given below and visualize it. It a table representing sales figures for ten of my employees in USA. I have named this table as “SalesData”.

Now if you want to know whose region is Minnesota, no lookup function will be useful. This is because, LOOKUP functions only return values to the right and it doesn’t return values from the left side. One transitory solution can be cut the data from column C and insert it in column and use VLOOKUP, but such quick fix are not always possible. Especially when you are not supposed to manipulate the arrangement of data provided.

In this case we will take help of two functions available in Excel namely INDEX & MATCH.

INDEX(reference,row_num,column_num,area_num): This function returns the value of an element in a table or an array, selected by the row and column number indexes. It means this function will provide you result from your data when you supply the row number and column number of the desired result.  The answer will be intersection of supplied row & column number.

MATCH(lookup_value,lookup_array,match_type): This function returns the relative position of an item in an array that matches a specified value in a specified order. It means this function will provide you the number where your lookup value falls in the series.

Now let’s come back to our problem where we have to know the name of the sales person responsible Minnesota region.

The solution will go like this F13:  =INDEX(SalesData[Name],MATCH(D3,SalesData[Region],0)) this will provide the name as “JAMES”

Similarly if you want to know who over achieved his target by highest margin? Solution will be F14: =INDEX(SalesData[Name],MATCH(MAX(SalesData[Difference]),SalesData[Difference],0)) and the name will be “EMMA”.

Now before wrapping the post here I have some homework for you. Please try to find the following using function mentioned above.

• Who has the maximum & minimum target in my team and what were their respective regions? For minimum use MIN function.
• What is the name of person managing Kansas region and how long is he/she with the organization?
• Who has the lowest target among the team?
• Who is the newest member of the team?

Let me know you solutions, you can post them in the comment section!

I hope you like this post and will share this with your friends and colleagues to help us grow the community of excel learners, there are various options you can use to share with people at the end of the post.

Now you also follow me on Twitter, just click on the vertically aligned Twitter radio button to the left of your computer screen to follow!

You can post your feedback as a comment below and I will take a note of it for future. “Happy Learning”

Please download the file from here!