Excel Formulas to Get Location of Value in 2D Array!!
Tags: excelformula, excelformulas
Published: 2021-01-05 15:32:07

---
title: Excel Formulas to Get Location of Value in 2D Array!!
published: true
date: 2021-01-03 16:28:27 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/excel-formulas-get-location-of-value-in-2d-array/
---

In this tutorial, we are going to see how to **get the location of the given value in a 2D array in Excel**. Let’s get into this article!! 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 location in 2D array](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Get-location-in-2D-array.png)](https://geekexcel.com/excel-formulas-get-location-of-value-in-2d-array/get-location-in-2d-array/#main)<figcaption id="caption-attachment-20616">Get location in 2D array</figcaption>
## General Formula:

- Use the below formula to locate the position value in a [2D array](https://geekexcel.com/simple-shortcut-to-enter-array-formula-in-microsoft-excel-365/).

**Find Row:**

**=[SUMPRODUCT](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/)((data=[MAX](https://geekexcel.com/how-to-use-excel-max-function-in-office-365-with-examples/)(data))\*[ROW](https://geekexcel.com/row-function-in-microsoft-excel-365-how-to-use/)(data))-ROW(data)+1**

**Find Column:**

**=[SUMPRODUCT](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/)((data=[MAX](https://geekexcel.com/how-to-use-excel-max-function-in-office-365-with-examples/)(data))\*[COLUMN](https://geekexcel.com/use-column-function-in-microsoft-excel-365-simple-methods/)(data))-COLUMN(data)+1**

## Syntax Explanations:

- **SUMPRODUCT** – In Excel, the **[SUMPRODUCT function](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/)** helps to multiply the corresponding arrays or ranges and returns the sum of the products.
- **MAX** – The [**MAX Function**](https://geekexcel.com/how-to-use-excel-max-function-in-office-365-with-examples/) returns the largest numeric value from the range of input values.
- **ROW** – It will return the row number for reference. Read more on the **[ROW function](https://geekexcel.com/row-function-in-microsoft-excel-365-how-to-use/)**.
- **COLUMN** – The [**COLUMN Function**](https://geekexcel.com/use-column-function-in-microsoft-excel-365-simple-methods/) in Excel returns the column number for 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.
- **Multiplication** **(\*)** – In this symbol will multiply any two values or numbers.
- **Minus Operator (-)** – This symbol will help to subtract any two values.
- **Plus operator (+)** – This symbol is used to add the values.

## Practical Example:

- Now we are going to see how to find the location of the given value.
- First, we will give the input ranges in **Column C** to **Column F**.
- Then, enter the given formula in the formula bar section.
- Finally, we will get the result in **Cell I3**.

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

## Closure:

From this tutorial, we have described the simple steps to **find the location of the given value in a 2D array**. Hope you like this article. Click here to know more on **[Geek Excel](https://geekexcel.com/)** and [**Excel Formulas**](https://geekexcel.com/excel-formula/) **!!**

### Related Articles:

- [**Excel Formulas to Get the content of Given Row and Column!!**](https://geekexcel.com/excel-formulas-get-content-given-row-column/)
- **[Highlight Active Cell Row and Column in Excel Office 365!!](https://geekexcel.com/highlight-active-cell-row-and-column-in-excel-office-365/)**

### Video Tutorial:

<!--[if lt IE 9]><script>document.createElement('video');</script><![endif]--><video id="video-20612-1" width="770" height="227" loop="1" autoplay="1" preload="metadata" controls="controls"><source type="video/webm" src="https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/97Bdaeciah-1.webm?_=1"></source><a href="https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/97Bdaeciah-1.webm">https://geekexcel.com/wp-content/uploads/2021/01/97Bdaeciah-1.webm</a></video>