Excel Formulas to Get Value in Multiple Columns With INDEX and MATCH
Tags: excelformula, excelformulas
Published: 2021-01-18 03:59:33

---
title: Excel Formulas to Get Value in Multiple Columns With INDEX and MATCH
published: true
date: 2021-01-11 04:26:17 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/excel-formulas-get-value-in-multiple-columns-with-index-and-match/
---

In the previous post, we have seen the simple formula used to find the value based on descending order in Excel. In this tutorial, we will guide you to learn the simple steps to **find the value in multiple columns by using the INDEX and MATCH functions in Excel**. Let’s step 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)

![Get Value in Multiple Columns With INDEX and MATCH](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/excel_iIFQeFT3HE.png)<figcaption id="caption-attachment-20810">Get Value in Multiple Columns With INDEX and MATCH</figcaption>
## General Formula:

- To look up a value by matching across multiple columns, 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,[MMULT](https://geekexcel.com/use-mmult-function-in-microsoft-excel-365-simple-methods/)(–(range2=criteria),[TRANSPOSE](https://geekexcel.com/how-to-use-transpose-function-in-microsoft-excel-365/)([COLUMN](https://geekexcel.com/use-column-function-in-microsoft-excel-365-simple-methods/)(range2)^0)),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/)**.
- **MMULT** – This function helps to return the matrix product of two arrays. Read more on the **[MMULT Function](https://geekexcel.com/use-mmult-function-in-microsoft-excel-365-simple-methods/)**.
- **TRANSPOSE** – The Excel [**TRANSPOSE function**](https://geekexcel.com/how-to-use-transpose-function-in-microsoft-excel-365/) converts a vertical range to a horizontal range or a horizontal range to a vertical range.
- **COLUMN** – In Excel, the **[COLUMN function](https://geekexcel.com/use-column-function-in-microsoft-excel-365-simple-methods/)** will return the column number for a reference
- **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.
- **Minus Operator (-)** – This symbol will help to subtract any two values.

## Example:

Refer to the below steps to get the matching value in multiple columns.

- First, we will give the input values in **Column B** to **Column E**.

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

- Then, 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-2.png)<figcaption id="caption-attachment-20806">Enter a formula</figcaption>

- After that, Press **CTRL** + **SHIFT** + **ENTER**  Keys.
- It will display the result as per the below image.

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

## Conclusion:

So yeah guys this is how you can get the **matching value in multiple columns using the INDEX and MATCH function in Excel**. Mention your **queries** in the **comment** box below. Thanks for visiting **[Geek Excel!!](https://geekexcel.com/) and [Excel Formulas](https://geekexcel.com/excel-formula/)!!**

### Related Articles:

- [**How To Use Excel VALUE Function in Office 365?**](https://geekexcel.com/how-to-use-value-function-in-microsoft-excel-2013/)
- **[Excel Formulas to Find the Position of Nth Matching Value!!](https://geekexcel.com/excel-formulas-to-find-position-of-nth-matching-value/)**