Trace formula result

**Ztv** · 03-25-2014, 08:01 AM

Hi everyone

I have this excelsheet with a lot of INDEX-MATCH functions. Can anyone tell me how to "trace" the result?

For example:
Lets say the resultat from an index-match formula (or any formula) is 10.

Can I, somehow, "trace" the resultat, i.e. where the 10 comes from? For example C10 or Sheet1, cellc10...

**ragulduy** · 03-25-2014, 08:15 AM

The result of the match part of the function will be the row in the index function that the 10 comes from.

So if you have:
=INDEX(A5:A10,MATCH(A1,A5:A10,0))

Then the result of the match function will be the position in the range A5:A10 that the result comes from.

**Sam Capricci** · 03-25-2014, 08:29 AM

are you referring to the trace dependents function under the formulas tab?

**Ztv** · 03-25-2014, 08:40 AM

@Ragulduy: I know, but thanks. I just wondered if we could take it further than juts a position in a range.
@Sambo kid: No. I was just wondering if I could trace the result from a formula.

For example a INDEX-MATCH formula like the one Ragulduy has written. If it finds the match in cell A8, I would like a formula that told me that the result from the index-match formula is found in A8.

Does it make sense?

**ChemistB** · 03-25-2014, 08:49 AM

There is no formula you can just wrap around the INDEX formula and it'll spit out the cell address. You could construct a separate formula that gives you the address but that would require knowing your formula exactly. You can use "Evaluate Formula" in the Formula Tab to watch the formula evaluate step by step which may help also.

**MrShorty** · 03-25-2014, 10:19 AM

I would probably use ragulduy's suggestion. If you un-nest the match from within the index function, you can use that result, combined with the known address of the starting cell of your index range, to figure out the address of the final result. See attached.