How to use a variable for a range

[Jeff Lowenstein]

What I am trying to do is lookup data in the 2 column of a different
workbook. This data is organized as follows:
John Smith
Day spot trial ...
Fri 7/1
Sat 7/2
Sun 7/3

John Doe
Day spot trial ...
Fri 7/1
Sat 7/2
Sun 7/3

What I have to do first is find the name ( ex: John Smith), and then find
the correct day in his section of the data, and return the value. I've found
and got the row information for both the name, and the date cells. ex: A7
and A33. What I am having a hard time doing is using these values as the
range values in either a index, or vlookup statement.
i.e. cell A34 = 7 - row where the name was found
Cell A54 = 33 - row where the date was found.

What I want to do is something along the order of this:
INDEX(SPREADSHEET2!A34:F54,MATCH(NAMECELL,SPREADSHEET2!A34-A54),2)

Where the 34'S and 54'S are variables. Is this even possible?

 

[JMB]

You could try the INDIRECT function,


INDIRECT("SPREADSHEET2!A"&A1&":F"&A2)

where cell A1 and A2 contain the beginning and ending row numbers. Or, if
these are the result of other functions you are using to find the row
numbers, you could replace A1 and A2 with your functions and just use one
large formula.