Excel Index Match Returning Value instead of Formula

Question

I am using Index Match in my sheet. The problem is the Index() is returning the value of the cell not the formula within the cell. Is there a way to return the actual formula an not the value?

My IndexMatch formula looks like this:

=Index(ProductTypeRef!$A$2:$B$11, Match(L2, ProductTypeRef!$A$2:$A$11, 0), 2)

The cells that are being referenced look like this:

="Folder for 5in by 7in paper. With "&INDIRECT("SanrioShop!M"&Match(A3,SanrioShop!$L$1:$L$71, 0))&" as the theme"

I want to return the actual formula, not the string that is created by the formula.

If the formula referncee returns M2 then M2 is returned in the IndexMatch Table. But I want the IndexMatch Table to return the current row, not M2.

you can't do that with a formula. Formula return values and not the underlying thing. — sous2817
Okay I figured values were the onty thing that could be returned, thanks — Mwspencer

basic basic · Accepted Answer · 2020-08-04T22:06:00

You can use FORMULATEXT function - it returns underlying formula for given reference.

In your case:

=FORMULATEXT(Index(ProductTypeRef!$A$2:$B$11,Match(L2,ProductTypeRef!$A$2:$A$11,0),2))

Excel Index Match Returning Value instead of Formula

1 Answers