Formula Forensics No. 039 – Find the Cell Address for a value (2D & 3D Reverse Lookup)

Yesterday in Formula Forensics 038 we looked at an example of how to find a value or the Max/Min across a number of worksheets.
Then we looked at how to find out which worksheet the value was on.

At the end I posed the question – How do you find out which cell has the Maximum or Minimum value once we know which worksheet it is on?

Today we will answer that question:

As always at Formula Forensics we will use a sample file which you can use to follow along: Download Sample File

The sample file contains 3 worksheets of data Sheet1, Sheet2 and Sheet3 and a 2D Results and 3D Result worksheets.

2D Reverse Lookup

(How to find the cell which contains a value in a 2D Range)

Before we answer the above question, we will first look at a simpler example of finding a values address in a 2D range on the existing worksheet before expanding that logic into the third dimension.

Open the sample file and goto the 2D Result worksheet.

You will see a simple range of values A1:D4 where all cells contain the value 5
The Minimum value of 3 is in B2
The Maximum value of 10 is in C3

To find the Address of the cells containing the minimum and maximum values we will use the Excel Address() function

If we look at the formula in cell G9 we will see:
=ADDRESS(INDEX(ROW(A1:A4),SUMPRODUCT((A1:D4=G6)*(ROW(A1:D4)))), INDEX(COLUMN(A1:D1),SUMPRODUCT((A1:D4=G6)*(COLUMN(A1:D4)))),1,1)

Referring to the Address() syntax above:
Row_Number: INDEX(ROW(A1:A4),SUMPRODUCT((A1:D4=G6)*(ROW(A1:D4))))

Column_Number:  INDEX(COLUMN(A1:D1),SUMPRODUCT((A1:D4=G6)*(COLUMN(A1:D4))))

ABS_Number: 1

A1: 1

First thing to note is that the two formulas for the Row and Column Numbers are very similar
The Row_Number formula looks at the Row of the range A1:D4
The Column_Number formula looks at the Column of the range A1:D4
and so the logic of each is the same we will only examine the Row derivation.

You can check the logic of the column to ensure you understand what is going on there.

The second thing to note is that the Value for the Row is determined using an Index function.

The Excel Index() function has the syntax:

In our formula above we have

INDEX(ROW(A1:A4), SUMPRODUCT((A1:D4=G6)*(ROW(A1:D4))))
where
Array: ROW(A1:A4)
Row_Number: SUMPRODUCT((A1:D4=G6)*(ROW(A1:D4)))
[Column_Number]: Is Optional and not used in the example

If we Select Cell G9, Edit the cell with F2
Select the first Row(A1:A4) component and then press F9
Excel will display: {1;2;3;4}
this clearly is an array of the rows of the cells A1:A4

The Row_Number component SUMPRODUCT((A1:D4=G6)*(ROW(A1:D4)))
This is returning the Row Number for use in the Index function and in our case will return the value 2 for the Minimum value of 3
But How?

Look at the formula: SUMPRODUCT((A1:D4=G6)*(ROW(A1:D4))))
you will see it is a Sumproduct function containing: (A1:D4=G6)*(ROW(A1:D4)))

Select the entire internal part “(A1:D4=G6)*(ROW(A1:D4)))” of the sumproduct() function and press F9
Excel will display: {0,0,0,0;0,2,0,0;0,0,0,0;0,0,0,0}

The , in the above string denotes Columns and the ; denotes Rows
This would be better written as:
{0,0,0,0;
 0,2,0,0;
 0,0,0,0;
 0,0,0,0}

This is derived from the multiplication of the two internal components of the sumproduct

The first part A1:D4=G6 

This is saying return an array of values where A1:D4=G6

Select (A1:D4=G6), press F9
{False,False,False,False;
False,True ,False,False;
False,False,False,False;
False,False,False,False}

and multiply that by the Row number of each cell in A1:D4

Select ROW(A1:D4), Press F9
{1,1,1,1;
2,2,2,2;
3,3,3,3;
4,4,4,4}

We can see that A1:D4=G6 only occurs in the minimum cell B2 and in this case the Row number is 2

{False*1, False*1, False*1, False*1;
False*2, True*2, False*2, False*2;
False*3, False*3, False*3, False*3;
False*4, False*4, False*4, False*4}

Sumproduct multiplies the two arrays and adds all the values up and gets the value 2

You can read more about using Advanced Sumproduct and derivation of this type of logic at: FF007

The same logic is now applied to the Columns, which for the Minimum also returns 2

So our original formula:
=ADDRESS(INDEX(ROW(A1:A4),SUMPRODUCT((A1:D4=G6)*(ROW(A1:D4)))),INDEX(COLUMN(A1:D1),SUMPRODUCT((A1:D4=G6)*(COLUMN(A1:D4)))),1,1)

is simplified to: =ADDRESS(2,2,1,1)
For which excel displays as $B$2
The address of the minimum value.

3D Reverse Lookup

Now change to the 3D Results Worksheet

We can extend the Reverse Lookup to 3D by using a combination of multiple worksheet Ranges and the fact that we already know the Worksheet where the Minimum is located from our work in FF038

The basic formula for finding the address we saw above is:
=ADDRESS(INDEX(ROW(A1:A4),SUMPRODUCT((A1:D4=G6)*(ROW(A1:D4)))), INDEX(COLUMN(A1:D1),SUMPRODUCT((A1:D4=G6)*(COLUMN(A1:D4)))), 1, 1)

This needs to be extended to 3D by changing the references to the Ranges A1:D4
Referring to E11

This is done via the use of the Excel Indirect() function:
=ADDRESS(INDEX(ROW(INDIRECT(D3&”!A1:A4″)), SUMPRODUCT((INDIRECT(D3&”!A1:C4″)=C3) * (ROW(INDIRECT(D3&”!A1:C4″))))), INDEX(COLUMN(INDIRECT(D3&”!A1:C1″)), SUMPRODUCT((INDIRECT(D3&”!A1:C4″)=C3) * (COLUMN(INDIRECT(D3&”!A1:C4″))))), 1, 1)

Excel returns $B$2

In the above you can see that we have replaced all the references to ranges eg: A1:A4 etc with an Indirect() Function
INDIRECT(D3&”!A1:A4″)

Indirect takes the text, in this case the Worksheets name in cell D3 and appends the text “!A1:A4” to it to form the 3D range
Sheet2!A1:A4

We can now add the final optional component to the Address() function

As we already know the worksheet in Cell D3

=ADDRESS(INDEX(ROW(INDIRECT(D3&”!A1:A4″)), SUMPRODUCT((INDIRECT(D3&”!A1:C4″)=C3)*(ROW(INDIRECT(D3&”!A1:C4″))))), INDEX(COLUMN(INDIRECT(D3&”!A1:C1″)), SUMPRODUCT((INDIRECT(D3&”!A1:C4″)=C3) * (COLUMN(INDIRECT(D3&”!A1:C4″))))), 1, 1, D3)
Excel returns: Sheet1!$B$2

Download

You can download a copy of the above file and follow along, Download Sample File.

A Challenge

Can you solve the problem another way ?

Post your solutions in the comments below.

Other Posts in this Series

The Formula Forensics Series contains a wealth of useful solutions and information specifically about how Normal Formula and specifically Array Formula work.

You can learn more about how to pull Excel Formulas apart in the following posts: http://chandoo.org/wp/formula-forensics-homepage/

If you have a formula and you want to understand how it works contact Hui and it may be featured in future posts.

Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.