I have the following formula and it works great:
=INDEX(sheet1!A3:C13,MATCH("bob",sheet1!C3:C13,0), 0)

Is is possible to change the static reference "A3:C13" to be dynamic? The
row reference of this formula needs to change dynamically based on the
results of another cell. I.e. it can change to A23:C47 or A99:C122 etc. I.e.
=INDEX(sheet1!A23:C47,MATCH("bob",sheet1!C23:C47,0 ),0)

The 23 and 47 values are found in sheet2, cells A17 and A18 respectively.

I've tried to name the A17 (and A18) cells on sheet2 "NameOne" (and
"NameTwo") and use it in the formula. I.e.
=INDEX(sheet1!ANameOne:CNameTwo,MATCH("bob",sheet1 !CNameOne:CNameTwo,0),0)

No such luck no matter what syntax I try. Can this be done?

Watch word wrap on this single formula:

=INDEX(INDIRECT("A"&Sheet2!A17&":A"&Sheet2!A18),MA TCH("bob",INDIRECT("A"&Sheet2!A17&":A"&Sheet2!A18) ,0),0)

Report back,,



"steve" wrote:

I have the following formula and it works great:
=INDEX(sheet1!A3:C13,MATCH("bob",sheet1!C3:C13,0), 0)

Is is possible to change the static reference "A3:C13" to be dynamic? The
row reference of this formula needs to change dynamically based on the
results of another cell. I.e. it can change to A23:C47 or A99:C122 etc. I.e.
=INDEX(sheet1!A23:C47,MATCH("bob",sheet1!C23:C47,0 ),0)

The 23 and 47 values are found in sheet2, cells A17 and A18 respectively.

I've tried to name the A17 (and A18) cells on sheet2 "NameOne" (and
"NameTwo") and use it in the formula. I.e.
=INDEX(sheet1!ANameOne:CNameTwo,MATCH("bob",sheet1 !CNameOne:CNameTwo,0),0)

No such luck no matter what syntax I try. Can this be done?

Thanks for the response JMay, and I apologize for the delayed response. I
ended up naming the ranges instead of the cells and got it working. I'll keep
your solution around in case I run into a similar situation. Thanks again.

"JMay" wrote:

Watch word wrap on this single formula:

=INDEX(INDIRECT("A"&Sheet2!A17&":A"&Sheet2!A18),MA TCH("bob",INDIRECT("A"&Sheet2!A17&":A"&Sheet2!A18) ,0),0)

Report back,,



"steve" wrote:

I have the following formula and it works great:
=INDEX(sheet1!A3:C13,MATCH("bob",sheet1!C3:C13,0), 0)

Is is possible to change the static reference "A3:C13" to be dynamic? The
row reference of this formula needs to change dynamically based on the
results of another cell. I.e. it can change to A23:C47 or A99:C122 etc. I.e.
=INDEX(sheet1!A23:C47,MATCH("bob",sheet1!C23:C47,0 ),0)

The 23 and 47 values are found in sheet2, cells A17 and A18 respectively.

I've tried to name the A17 (and A18) cells on sheet2 "NameOne" (and
"NameTwo") and use it in the formula. I.e.
=INDEX(sheet1!ANameOne:CNameTwo,MATCH("bob",sheet1 !CNameOne:CNameTwo,0),0)

No such luck no matter what syntax I try. Can this be done?