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

#### Erick Storm

##### New Member
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:

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:

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:  Anyone have any ideas? Thank you in advance.

### 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
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
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. #### Fluff

##### MrExcel MVP, Moderator
The cell reference is at the end of the string. #### Fluff

##### MrExcel MVP, Moderator

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

• Erick Storm

#### Erick Storm

##### New Member
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
You're welcome & thanks for the feedback.

Replies
5
Views
64
Replies
5
Views
61
Replies
1
Views
123
Replies
1
Views
584
Replies
2
Views
456