I can't seem to find an answer to what I assume is a simple question. I need to find what cell an index match is referring to.
Example: I have an example below of what it looks like. The "Index Matched" column is returning the number of black "as color tee" of size L that is currently there. I need this to be updated with the QTY, which is easy enough to do. But I need to find where the 8 is coming from. I know its cell F3 on the sheet, but how to I extract that information.
The formula I have there is:
=INDEX($C$3:$N$57,MATCH(X8&Y8,$A$3:$A$57&$B$3:$B$57,0),MATCH(Z8,$C$2:$N$2,0))
It searches a table fine. When it finds it, can I get what cell that data is from?
CodePudding user response:
I modify your formula as
="(" & MATCH(X8&Y8,$A$3:$A$57&$B$3:$B$57,0) & "," & MATCH(Z8,$C$2:$N$2,0) & ")"
see if works. It is in (row, column) form. If you want other form, there is way to further transform it.
CodePudding user response:
Ok, I have solved my issue.
It was simple as I thought.
The solution is to add Cell("Address",
to the front of the formula.
So that =INDEX($C$3:$N$57,MATCH(X8&Y8,$A$3:$A$57&$B$3:$B$57,0),MATCH(Z8,$C$2:$N$2,0))
would, instead of producing the result of say "8" it would produce "$F$3" with the line:
=CELL("Address", INDEX($C$3:$N$57,MATCH(X8&Y8,$A$3:$A$57&$B$3:$B$57,0),MATCH(Z8,$C$2:$N$2,0)))