CODEWITHSUNDEEP
excelexcel-formulavlookup
Mwspencer
Mwspencer

Reputation: 1183

Excel Index Match Returning Value instead of Formula

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.

Upvotes: 0

Views: 626

Answers (1)

basic
basic

Reputation: 11968

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

enter image description here

Upvotes: 1

Related Questions