0

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

  • 1
    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. – gtwebb 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? – user3161965 Jun 13 '14 at 18:52
0

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

Your formula in: B2

   {=MIN(IF($D$1:$DG$1="food",IF(D2:DG2<>"",D2:DG2)))}

Lookup formula

=CELL("address",INDEX($D$2:$DG$2,MATCH("food",$D$1:$DG$1,0)*MATCH(B2,$D$2:$DG$2,0)))
  • This doesn't work with the formula as you described. Can you elaborate? – CharlieRB Jun 13 '14 at 19:27
  • This would work if you were using something like a lookup, or index function that returns the value of an individual cell it won't work for the formula you are using – gtwebb Jun 13 '14 at 20:40
  • =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? – user3161965 Jun 14 '14 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.