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$21 647,1)),-1)

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

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

Am I doing something wrong?

Thanks for any help!

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

"Matt" wrote:

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$21 647,1)),-1)

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

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

Am I doing something wrong?

Thanks for any help!

That seems to help.

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

with D21323:D21645 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!

Works perfectly now. Thanks

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)&":D"&MATCH(B4,Sheet3!$A$2:$A$21647,1) ),-1)

Took the 's away from Sheet3 in the INDIRECT. Added &":D"& 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).

--
--Thomas [PBD]
Working hard to make working easy.
Answered your question? Click ''''Yes'''' below.


"Matt" wrote:

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$21 647,1)),-1)

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

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

Am I doing something wrong?

Thanks for any help!