VLOOKUP: Passing a Value to the Function

[News Reader]

I have a vlookup function as follows:
=VLOOKUP($A8,issues!$B$13:$H$681,3,FALSE)

The row numbers (13 and 681) will change over time. Is there any way
to get the function to read the row numbers from two cells in the
worksheet for placement into the function? For example, there would
be 13 in cell B2, how do I get this into the equation?

Thanks for any help.

 

[Frank Kabel]

Hi
try:
==VLOOKUP($A8,INDIRECT("'issues!$B$" & B2 & ":$H$681"),3,FALSE)

 

[Ragdyer]

Since you originally asked for the two row references to be variable, and,
Frank has a small typo, you can try this:

=VLOOKUP($A8,INDIRECT("issues!$B$"&$B$8&":$H$"&$C$8),3,0)

With the column B row number in B8,
And the column H row number in C8.

 

[Frank Kabel]

Hi RD
thanks for the catch

 

[Aladin Akyurek]

=VLOOKUP($A8,INDEX(issues!$B:$B,B2):INDEX(issues!$H:$H,C2),3,0)

where C2 >= B2 with values like 13 in B2 and 681 in C2.

 

[Ragdyer]

NICE !

 

[Harlan Grove]

=VLOOKUP($A8,INDEX(issues!$B:$B,B2):INDEX(issues!$H:$H,C2),3,0)

where C2 >= B2 with values like 13 in B2 and 681 in C2.

It works, but what are the advantages of two INDEX calls joined by the :
operator vs

OFFSET(issues!$B:$H,B2-1,0,C2-B2+1)

or

INDIRECT("issues!B"&B2&":H"&C2)

?

 

[Ragdyer]

Isn't there an advantage Harlan, of just the fact that Indirect() might be
the choice of last resort, considering the limitation of it's inability to
operate on closed files?

 

[Harlan Grove]

Isn't there an advantage Harlan, of just the fact that Indirect() might be
the choice of last resort, considering the limitation of it's inability to
operate on closed files?

....

There might be an advantage if the OP were referring to a different
workbook, but that didn't appear to be the case.

It does appear that INDEX can be used in expressions like

INDEX('C:\foo\[bar.xls]xyz'!$B:$B,5):INDEX('C:\foo\[bar.xls]xyz'!$H:$H,24)

and resolve to 'C:\foo\[bar.xls]xyz'!B5:H24 even when C:\foo\bar.xls is
closed.