Excel search vector based on a different formula

Question

My table has 2 sheets.

Sheet 1 contains values ​​for 30 days.

Each day has a value of no more than 19 lines consisting of names and matching values. The names that appear for each day will not be the same every day. In other words, my name may appear on the 1st, 2nd and 14th of the month, and not on other days. (And these values ​​cannot be sorted.)

In my second sheet, only one value for the month will be displayed, and therefore I use the search.

Formula for the first day:

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

On the second day it will be:

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

The only thing that changes every day is the range / vector of the search. I already have a separate (hidden) column that calculated the correct start line number, i.e. 22, 42, 62, but how would I use this number to change the search range in my formula?

thanks a lot
Michael

Answer 1

The indirect () function allows you to specify the range to search in terms of another cell.

For example, if in Sheet 1 of the book there are numbers 1-10, coming from A3: A12, where the initial line (3) is indicated in cell B1 of the same sheet.

  3 1 2 3 4 5 6 7 8 9 10 

Then, to find the value 5 and put the answer on any other sheet of the book:

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