Learn How to Get the Location of Value in a 2D Array in Microsoft Excel
The main purpose of this formula is to locate the position of a value in a 2D array. Here we will learn how to get the location of the value in a 2D array in the given data range in the workbook in Microsoft Excel.
That implies, with the help of a formula based on the SUMPRODUCT function, you can able to locate the position of a value in a 2D array.
So, with the help of this formula, you can able to get the location of the value in a 2D array in the given data range in the workbook in Microsoft Excel.
General Formula to Get Location of Value in 2D Array
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
The Explanation to Get Location of Value in 2D Array
So we know that with the help of the given formula above you can able to locate the position of a value in a 2D array. Here we will learn how to get the location of the value in a 2D array in the given data range in the workbook in Microsoft Excel.
As we know that for this example, we arbitrarily find the location of the maximum value in the data, but you can replace data=MAX(data) with any other logical test that will isolate a given value.
Also, note these formulas will fail if there are duplicate values in the array. So now you have learned how to get the location of the value in a 2D array in the given data range in the workbook in Microsoft Excel.
