How to Look up Values in a Range by Combining MATCH & INDEX functions

In our previous article, we have introduced the function of VLOOKUP and explain the usage of the formula. And today we will introduce the function combination of MATCH and INDEX to look up values.

Excel functions have very amazing effects, which can help us do many complex tasks. The independent function can achieve certain functionalities. In addition, you can also try to combine different functions to deal with your problems. Thus today, we will combine the MATCH and INDEX functions to lookup values in different worksheets.

The Function Combination of MATCH and INDEX

This is a worksheet of sales volume. And now you need to calculate the bonus of employees with the job number of 2014.Sales Volume of Employees

However, you need to put the total number into a new worksheet.To Calculate Bonus

Thus, you can use this functions combination to input the total sales volume into the column. And the following are steps of the usage.

  1. Select the cell C2.
  2. Then input the formula into the cell:

=INDEX(‘sales volume’!$A$2:$G$19,MATCH(B2,’sales volume’!$B$2:$B$19,0),7)

Although it is a little bit long, it is very easy to understand. The MATCH function will return the position of the target number, and here it is the position of job number. After knowing the position, the INDEX function can look up the corresponding number in the worksheet “Sales volume” in the 7th column. Besides, here you can use the absolute reference into the formula. What’s more, in your actual worksheet, you need to change the area and the column number in the formula.

With this formula, the number will appear in the cell C2.The Number Appears

  1. Put your mouse to the bottom right corner of the cell C2.
  2. When you see the shape of “+”, double click your mouse.The Shape of Mouse

And then all the cells in the column will be filled with certain formulas.The Result

And now, you can use this result to calculate the bonus.

Don’t Ignore Small Virus or Malware

With the development of computer technology, the security of computer is guaranteed. Sometimes, the protection software in your computer can easily handle such problems. However, there are still chances that your computer may get affected by virus or malware. And probably a very small virus can quick attack your system and corrupt your Excel files. In this case, your information is likely to be lost. But there still ways that you can handle those data disasters. And third party tool is one of the most effective ways. It can recover Excel with effortless ease and you can retrieve your data in a short time.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including word recovery and outlook repair software products. For more information visit

Comments are closed.