Returning a Cell Address of a Result found via INDEX(MATCH

Erick Storm

New Member
Joined
Oct 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have an INDEX(MATCH formula that goes to another tab of data, finds the result of the intersection between the row and column criteria and returns the number.

For audit purposes, I have been trying to create a formula next to this cell that provides the cell reference of where the number was located, as kind of audit trail for the external auditors to point them to exactly where the number came from.

Here is the INDEX(MATCH formula in Cell C6 that returns the result:
=INDEX('Sch B-3'!A8:W183,MATCH("Some Business Segment Total",'Sch B-3'!A8:A183,0),MATCH("Total Adjusted Revenue",'Sch B-3'!A8:W8,0))

This formula returns the correct value and now I would like to write a formula that returns the cell address of the number it found. Based on what I found in the knowledge bases, I used a CELL("address",INDEX(MATCH, but it is returning a #REF! error:
=CELL("address",INDEX('Sch B-3'!F9:F195,1,MATCH('CHO Sch B-1'!C10,'Sch B-3'!F9:F195,0)))

The data will always be within this one column, Column F, so I only indexed and matched to that column. 'CHO Sch B-1'!C10 is where the INDEX(MATCH formula is written and returns the value. I want to use the retuned value to find the cell address on the other tab.

I was thinking that since the actual lookup cell is a formula that returns a number (instead of being anumber) that maybe I needed to use the VALUE function but this did not work either:
=CELL("address",INDEX('Sch B-3'!F9:F195,1,MATCH(VALUE('CHO Sch B-1'!C6),'Sch B-3'!F9:F195,0)))

1603382611452.png
1603382611452.png


Anyone have any ideas? Thank you in advance.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You can just use your original formulae
Excel Formula:
=CELL("address",INDEX('Sch B-3'!A8:W183,MATCH("Some Business Segment Total",'Sch B-3'!A8:A183,0),MATCH("Total Adjusted Revenue",'Sch B-3'!A8:W8,0)))
 

Erick Storm

New Member
Joined
Oct 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
You can just use your original formulae
Excel Formula:
=CELL("address",INDEX('Sch B-3'!A8:W183,MATCH("Some Business Segment Total",'Sch B-3'!A8:A183,0),MATCH("Total Adjusted Revenue",'Sch B-3'!A8:W8,0)))
Thank you Fluff. I should have mentioned that was the first thing I tried and it returned the file name instead of the cell reference.
1603386791716.png

=CELL("address",INDEX('Sch B-3'!A8:W183,MATCH("Some Business Segment Total",'Sch B-3'!A8:A183,0),MATCH("Total Adjusted Revenue",'Sch B-3'!A8:W8,0)))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
The cell reference is at the end of the string. ;)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you only want the cell address, you can use
Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(CELL("address",INDEX('Sch B-3'!A1:W183,MATCH("Some Business Segment Total",'Sch B-3'!A1:A183,0),MATCH("Total Adjusted Revenue",'Sch B-3'!A8:W8,0))),"$",REPT(" ",100),1),100))
 
Solution

Erick Storm

New Member
Joined
Oct 22, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
If you only want the cell address, you can use
Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(CELL("address",INDEX('Sch B-3'!A1:W183,MATCH("Some Business Segment Total",'Sch B-3'!A1:A183,0),MATCH("Total Adjusted Revenue",'Sch B-3'!A8:W8,0))),"$",REPT(" ",100),1),100))
Fluff, I can't thank you enough. That did the trick!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,125
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,010
Messages
5,545,479
Members
410,685
Latest member
chandraganji
Top