Excel lookup vector based on another formula

Question

My spreadsheet has 2 sheets.

Sheet 1 contains 30 days worth of values.

Each day's values are a maximum of 19 rows, consisting of names and correspoding values. The names that will appear for each day are not the same every day. In other words my name might appear on the 1st, 2nd and 14th of the month and not on any other days. (And these values may not be sorted.)

My second sheet will display only one persons values for the month, and I therefore use a lookup.

The formula for the 1st day is:

=LOOKUP("name";'Input Sheet'!$A$2:$A$20; 'Input Sheet'!B2:B20)

For the second day it would be:

=LOOKUP("name";'Input Sheet'!$A$22:$A$40; 'Input Sheet'!B22:B40)

The only thing that changes for each day is the lookup range/vector. I have a separate (hidden) column already that has calculated the correct starting row number, i.e. 22, 42, 62, but how would I use that number to change the lookup range on my formula?

Thanks a lot
Michael

Answer 1

The indirect() function will allow you to specify the range for your lookup in terms of another cell.

For example, if Sheet1 of the workbook has the numbers 1-10 going from A3:A12, where the starting row (3) is specified in cell B1 of the same worksheet.

    3

1   
2   
3   
4   
5   
6   
7   
8   
9   
10  

Then to lookup the value 5 and place the answer on any other sheet of the workbook:

=LOOKUP(5,INDIRECT("Sheet1!$A"&Sheet1!$B$1&":$A$12"))