There are several lookup functions available in Excel, but not all of them will allow you to use multiple criteria. Here is how you can use the INDEX and MATCH functions to lookup values in Excel with multiple criteria. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
General Formula:
- To lookup values with INDEX and MATCH, using multiple criteria, you can use the below array formula.
=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0))
Syntax Explanations:
- INDEX – The INDEX function returns the value at a given position in a range or array.
- MATCH – In Excel, this function will locate the position of a lookup value in a row, column, or table. Read more on the MATCH function.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Range – It represents the input ranges in your worksheet.
- Multiplication (*) – In this symbol will multiply any two values or numbers.
Practical Example:
Let’s follow the below steps to look up the value based on multiple criteria.
- First, you need to enter the input values. Refer to the below image here, we will give the input ranges in Column B to Column E.
- Now, we are going to look up the price of a given item based on its color and size.
- We will enter the given formula in the formula bar section.
- After that, Press CTRL + SHIFT + ENTER Keys.
- Finally, it will return the result in the selected cell.
Closure:
In this way, you can get to know the simple steps used to lookup multiple criteria in Excel using the INDEX and MATCH functions. Hope you like it. If you have any doubt ** just leave it in the below **comment box. Click here to know more on Geek Excel *and Excel Formulas *!!
Top comments (0)