# Excel: find the cell that another formula found its "answer" in

I made a formula that searches an array to return a value, and now I want excel to "show its work" and tell me in which cell it found that value. I've been fighting with `MATCH` and `ADDRESS` but so far have been only able to return the value's position within the array and not its absolute cell reference.

As a secondary goal, I want to take that cell reference and look one cell down and one cell to the left, then return whatever value it finds there. I don't know if that's a goofy way of doing it but that's my idea so far. Any better methods would be appreciated.

The actual file: https://dl.dropboxusercontent.com/u/78225/test_sheet.xlsx

EDIT: the formula I'm using, which is returning the right value but I want to find that value's cell reference: `{=MIN(IF(\$D\$1:\$DG\$1="food",IF(D2:DG2<>"",D2:DG2)))}`

• You should really post the formula you are using and which part you want to look up (expected results) as it would be easier to provide an answer. I've listed a generic answer below that should get you most of the way there. Jun 13 '14 at 18:38
• I tried `CELL` with the array-entered formula `{=MIN(IF(\$D\$1:\$DG\$1="food",IF(D2:DG2<>"",D2:DG2)))}`, but it throws an error and won't even let me enter it. Did you take a look at the actual file? Jun 13 '14 at 18:52

You should be able to do this with the CELL function as shown below.

``````=CELL("address",your formula here)
``````

For looking to the left or the right of the answer look up the offset function.

EDIT

I don't know of a way to do this directly with a min function I think you would need to look it back up in the table

``````   {=MIN(IF(\$D\$1:\$DG\$1="food",IF(D2:DG2<>"",D2:DG2)))}
``````=CELL("address",INDEX(\$D\$2:\$DG\$2,MATCH("food",\$D\$1:\$DG\$1,0)*MATCH(B2,\$D\$2:\$DG\$2,0)))
• `=CELL("address",INDEX(\$D\$2:\$DG\$2,MATCH("food",\$D\$1:\$DG\$1,0)*MATCH(B2,\$D\$2:\$DG\$2,0)))` used on `{=MIN(IF(\$D\$1:\$DG\$1="food",IF(D2:DG2<>"",D2:DG2)))}` (which reads "2005) returns "\$G\$2"; G2 contains "2006". Am I missing something here? Jun 14 '14 at 17:43