Is there a way to use a cell value as a parm in a canned function?

[Guest]

Using: =INDEX(A5:A748,MATCH(J2,E5:E748,0),1)

The first parm of INDEX is an array spec. Is there a way I can use the
value of cell K1 (which would contain A5) and the value of cell L1 (which
would contain A748) in the function?

 

[Rick Rothstein \(MVP - VB\)]

You can use the INDIRECT function to build a reference from text. Give this
a try...

=INDEX(INDIRECT(K1&":"&L1),MATCH(J2,E5:E748,0),1)

Rick

 

[Peo Sjoblom]

=INDEX(INDIRECT(K1&":"&L1),MATCH(J2,E5:E748,0),1)

However I would probably use

=INDEX(INDEX(A:A,K1):INDEX(A:A,L1),MATCH(J2,E5:E748,0))

and put 5 in K1 and 748 in L1 that way the formula wouldn't be volatile


--


Regards,


Peo Sjoblom

 

[Guest]

Thanks Rick, worked great.

Peo's worked with this change...
=INDEX(INDIRECT("A"&K3&":A"&L3),MATCH(J2,E5:E748,0))

Hope I got that right.

Regads.

 

[Rick Rothstein \(MVP - VB\)]

Peo's worked fine for me... use exactly what he posted AND make sure you
only have a number in K1 and L1, NOT the cell reference. That is, don't put
A5 in K1, just put 5 in there (the formula gets the A from the A:A
reference).

Rick