MATCH function...AGAIN

[F. Lawrence Kulchar]

In cell D2, I have the number 1.

The following concatenation

="A"&(D2+1)&":A21"

works out to be...A2:A21

WHY CAN I NOT USE THIS AS MY REFERENCE LOOK-UP IN THE MATCH FUNCTION??

IN OTHER WORDS, =MATCH($C$2,"A"&(D2+1)&":A21",0)
does NOT work?

Please help?

Thanks,

FLKulchar

 

[F. Lawrence Kulchar]

In other words, I wish to use the A2:A21 as a range reference??

FLKulchar

 

[Gary''s Student]

You are very close!!

=MATCH($C$2,INDIRECT("A"&(D2+1)&":A21"),0)

whenever you are using a formula that displays a string like Z2:Z34
you need to use INDIRECT() because MATCH() wants a true range and not just a
string

 

[FLKulchar]

Thank you...

Now..It is PERFECT!!

thanks for the help...my MATCH "pulldown" now works thanks to your help.

FLKulchar

 

[Gary''s Student]

You are very welcome! Have a great weekend!