INDIRECT(...) Help with cell reference

[alombia]

Hi,

I have the following array formula:

{=IFERROR(INDEX(MasterTable!$D$2:$GC$165,MATCH(1,IF($AA4=MasterTable!$C$2:$C$165,IF($AB4=MasterTable!$A$2:$A$165,1)),0),MATCH($X$3,MasterTable!$D$1:$GC$1,0)),"nothing found")}

I would like to refer to a value in say A1 instead of the above highlighted section. The value in A1 is GC165. I have tried the following:

{=IFERROR(INDEX(MasterTable!$D$2:INDIRECT(A1),MATCH(1,IF($AA4=MasterTable!$C$2:$C$165,IF($AB4=MasterTable!$A$2:$A$165,1)),0),MATCH($X$3,MasterTable!$D$1:$GC$1,0)),"nothing found")}

However, that does not work. Any ideas?

Thanks

 

[Aladin Akyurek]

What are you trying to make a variable - (a) column or (b) row?

If (a), it's already done by the second MATCH bit.

 

[admiral100]

Hi ,

I think it's just a syntax error , should be INDIRECT("A1")

 

[alombia]

That does not work either! Removing the INDIRECT and replacing with actual cell reference works. I.e. The following works: {=IFERROR(INDEX(MasterTable!$D$2:$GC$165,MATCH(1,IF($AA4=MasterTable!$C$2:$C$165,IF($AB4=MasterTable!$A$2:$A$165,1)),0),MATCH($X$3,MasterTable!$D$1:$GC$1,0)),"nothing found")} but not INDIRECT.

 

[Aladin Akyurek]

Try to answer the question of post #2 .

 

[alombia]

Im doing a 3 way match (AA4, AB4 and X3) to retrieve data from sheet called MasterTable. I am aware of the second Match. However, I am not clear on why your question is relevant to my INDIRECT dilemma. The issue is GC165 is likely to change and so I am trying to dynamically insert this rather than hard code it if that makes sense.
BTW, the 3 way match was provided by an amazing person on this forum

 

[Aladin Akyurek]

Why do you think that INDIRECT is relevant at all for a problem you fail to explicate? Once more, what is likely changing - row, column, or both?

 

[alombia]

Why do you think that INDIRECT is relevant at all for a problem you fail to explicate? Once more, what is likely changing - row, column, or both?

Column for now, though possible row as well.

 

[Aladin Akyurek]

Column for now, though possible row as well.

You don't need for column because: the red part in

{=IFERROR(INDEX(MasterTable!$D$2:$GC$165,MATCH(1,IF($AA4=MasterTable!$C$2:$C$165,IF($AB4=MasterTable!$A$2:$A$165,1)),0),MATCH($X$3,MasterTable!$D$1:$GC$1,0)),"nothing found")}

takes care of that.

 

[alombia]

You don't need for column because: the red part in

{=IFERROR(INDEX(MasterTable!$D$2:$GC$165,MATCH(1,IF($AA4=MasterTable!$C$2:$C$165,IF($AB4=MasterTable!$A$2:$A$165,1)),0),MATCH($X$3,MasterTable!$D$1:$GC$1,0)),"nothing found")}

takes care of that.

I am still not clear on your question. I apologise. The above formula works. How do i get it to work using INDIRECT instead for both GC165 and GC1? GC is likely to change.