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

[Erick Storm]

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)))

Anyone have any ideas? Thank you in advance.

 

[Fluff]

Hi & welcome to MrExcel.
You can just use your original formulae

=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]

Hi & welcome to MrExcel.
You can just use your original formulae

=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.

=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]

The cell reference is at the end of the string.

 

[Fluff]

If you only want the cell address, you can use

=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))

 

[Erick Storm]

If you only want the cell address, you can use

=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]

You're welcome & thanks for the feedback.