Excel Formulas to Lookup Multiple Criteria with INDEX and MATCH!!
Tags: excelformula, excelformulas
Published: 2021-01-18 04:00:06

---
title: Excel Formulas to Lookup Multiple Criteria with INDEX and MATCH!!
published: true
date: 2021-01-11 04:29:05 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/excel-lookup-multiple-criteria-with-index-and-match/
---

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](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)** and **[MATCH](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/)** 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](https://www.microsoft.com/en-in/microsoft-365/excel)

[![Formula to lookup multiple criteria](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Formula-to-lookup-multiple-criteria.png)](https://geekexcel.com/?attachment_id=20845#main)<figcaption id="caption-attachment-20845">Formula to lookup multiple criteria</figcaption>

## General Formula:

- To lookup values with INDEX and MATCH, using multiple criteria, you can use the below array formula.

**=[INDEX](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)(range1,[MATCH](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/)(1,(A1=range2)\*(B1=range3)\*(C1=range4),0))**

## Syntax Explanations:

- **INDEX** – The **[INDEX function](https://geekexcel.com/index-function-in-microsoft-excel-365-how-to-use/)** 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](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/)**.
- **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](https://geekexcel.com/excel-office-365-columns/) B** to **Column E**.

![Input Ranges](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Input-Range-2.png)<figcaption id="caption-attachment-20841">Input Ranges</figcaption>

- Now, we are going to look up the **price** of a given **item** based on its **color** and **size**.

![Criteria](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Criteria-3.png)<figcaption id="caption-attachment-20842">Criteria</figcaption>

- We will **[enter the given formula](https://geekexcel.com/how-to-enter-formula-in-excel/)** in the **[formula bar](https://geekexcel.com/how-to-use-formula-bar-for-various-purpose-in-excel-365/)** section.

![Enter a formula](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Enter-a-formula-5.png)<figcaption id="caption-attachment-20843">Enter a formula</figcaption>

- After that, Press **CTRL** + **SHIFT** + **ENTER**  Keys.
- Finally, it will return the result in the selected **[cell](https://geekexcel.com/merge-cells-in-excel-office-365/)**.

![Result](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Result-14.png)<figcaption id="caption-attachment-20844">Result</figcaption>

## 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](https://geekexcel.com/) **and [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

### Read Ahead:

- **[Excel Formulas: Exact Match Lookup with INDEX and Match!!](https://geekexcel.com/excel-formulas-exact-match-lookup-index-match/)**
- [**Excel Formulas to Find the Exact INDEX Match in Office 365!!**](https://geekexcel.com/excel-formulas-find-exact-index-match/)