Indirect function syntax

[Matt]

I am trying to use the following formula:

=MATCH(G1,INDIRECT("'Sheet3'!"&"D"&MATCH(B4,Sheet3!$A$2:$A
$21647,0)):INDIRECT("D"&MATCH(B4,Sheet3!$A$2:$A$21647,1)),-1)

but keep getting the #VALUE!. When I do the formula auditing, it
works until here:

=MATCH(125,Sheet3!$D$21323:$D$21645,-1)
then:
=MATCH(125,#VALUE,-1)

Am I doing something wrong?

Thanks for any help!

 

[Sheeloo]

I have not tested this but try this;
replace : with & ":" & in your formula

 

[Thomas [PBD]]

Matt,

There were a couple minor errors in the coding, here is a tested (on my
sheet), working function:

=MATCH(G1,INDIRECT("Sheet3!D"&MATCH(B4,Sheet3!$A$2:$A$21647,0)&""&MATCH(B4,Sheet3!$A$2:$A$21647,1)),-1)

Took the 's away from Sheet3 in the INDIRECT. Added &""& instead of the
second INDIRECT function as it was unneeded. Rearranged the parenthesis.
Note, this will return the first value of the array set by the indirect
function (hope that is what you were going for instead of the row number
itself).

 

[Matt]

That seems to help.

but now instead of evaluating this:
=MATCH(125,Sheet3!$D$21323:$D$21645,-1)

with D2132321645 as the match range, its evaluating the value in
those cells first so that the match range is

=MATCH(125,128.75:129.30,-1)

which still gives #VALUE!

 

[Matt]

Works perfectly now. Thanks