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]

Try to explain your objective without any reference to the INDIRECT function...

 

[alombia]

Try to explain your objective without any reference to the INDIRECT function...

MasterTable!D2:GC165 and the second reference refer to a range that is likely to change. The first range refers to both rows and columns. Currently my data finishes in row 165 and column GC. However, this is likely to expand and contract. Also D2 may change to B2 or M2 depending on the changes made to the data sheet etc. Hence why I am trying to add dynamic references instead of static ones. I figured i can store the start and end ranges in cells and use indirect in the original formula. Thus, if the range does change, i simply need to change the value of these cells, instead of changing every formula (there are many variations of the formula in many cells).

The whole reason for the indirect method is to avoid changing the formula again and again as i work on the data sheet. I hope this makes sense.

 

[Aladin Akyurek]

Care to post the values that you have in AA4, AB4, and X3?

 

[alombia]

AA4 = Names. For example, John Smith
AB4 = Age. eg 20
X3 = column header name (row1) eg. Height

 

[Aladin Akyurek]

AA4 = Names. For example, John Smith
AB4 = Age. eg 20
X3 = column header name (row1) eg. Height

Define Lrow in the Name Manager as referring to:

=MATCH(9.99999999999999E+307,MasterTable!$A:$A)

Define Age in the Name Manager as referring to:

=MasterTable!$A$2:INDEX(MasterTable!$A:$A,Lrow)

Define Name in the Name Manager as referring to:

=MasterTable!$C$2:INDEX(MasterTable!$C:$C,Lrow)

Define MTdata in the Name Manager as referring to:

=MasterTable!$A$2:INDEX(MasterTable!$GC:$GC,Lrow)

Now we can rewrite the formula you have in terms of the foregoing dynamic named ranges...

{=IFERROR(INDEX(MTdata,MATCH(1,IF($AA4=Name,IF($AB4=Age,1)),0),MATCH($X$3,OFFSET(MTdata,-1,0,1),0)),"nothing found")}