# Excel - Referring to a cell by using another cell for the row number

Asked By David Tannenbaum on 02-Aug-12 07:12 PM
```On one tab I have data that looks like this:

8/1/2012
1	29381
2	2423

8/2/2012
5	23231
8	7542

On the second tab I would like to automatically pull this data depending on what today's date is.

So, for example, I would like to do a VLOOKUP for "1" on 8/1/2012.

So on the second tab, in A1, I have created a formula to tell me what row to start my VLOOKUP on:
=MATCH(D3,'DT''s raw data'!A:A,0)+1

(Here "D3" is :"8/1/12"). This returns "2'.  I have also created a formula, in A2, that tells me where to end my VLOOKUP for 8/1/12:

=MATCH(E3,'DT''s raw data'!A:A,0)-1

This returns "3".

Now I would like to write the VLOOKUP so it pulls from A2:B3 on the second tab. Something like this:

=vlookup(1,FirstTab!A[A1]:B[A2],2,false)

Obviously that "A[A1]" and "B[A2]" is not working. Any way I can make it work?```

David Tannenbaum replied to David Tannenbaum on 02-Aug-12 08:14 PM
```Here is a simpler way to ask this.

On TabOne I have the value "35" in A1.

On TabTwo I have the value "A1" written into B1.

On TabTwo I now want to call up the value in TabOne!A1 by referring to TabTwo!B1. So it would be something like this:

TabOne!(TabTwo!B1)

But that does not work, of course.```
zvkmpw replied to David Tannenbaum on 02-Aug-12 08:48 PM
```Maybe this would help get started:
=VLOOKUP(1,OFFSET(FirstTab!\$A\$1,\$A\$1-1,0,\$A\$2-\$A\$1+1,2),1,FALSE)
to pull column A, and
=VLOOKUP(1,OFFSET(FirstTab!\$A\$1,\$A\$1-1,0,\$A\$2-\$A\$1+1,2),2,FALSE)
to pull column B.```
David Tannenbaum replied to zvkmpw on 02-Aug-12 09:08 PM
```zvkmpw, that is a helpful suggestion, but unfortunately it does not solve my =
problem, because every day I would like to pull values from a different set=
of rows. I cannot predict in advance which rows those will be, but I do hav=
e a formula to calculate the rows. So I need a way to reference that formul=
a.```
zvkmpw replied to David Tannenbaum on 03-Aug-12 06:19 PM
```OK, try this in a new tab, Sheet2

In Sheet2!A1, put a formula that returns the _FIRST_ row number of the range in FirstTab to be searched. The formula can take into account a date and/or other values.

In Sheet2!A2, put a formula that returns the _LAST_ row number of the range in FirstTab to be searched. The formula can take into account a date and/or other values.

In Sheet2!B1 put the value to be looked up in columnn A of FirstTab.

In Sheet2!B2, put
=VLOOKUP(B1,OFFSET(FirstTab!\$A\$1,\$A\$1-1,0,\$A\$2-\$A\$1+1,2),2,FALSE)
Hopefully, this does the needed lookup.

Explanation: The OFFSET(...) here returns a two-column sub-range inside FirstTab!A:B, limited by the row numbers computed in Sheet2!A1 and Sheet2!A2.

Modify or expand as needed.```
zvkmpw replied to zvkmpw on 05-Aug-12 03:54 PM
`I should add: By making the formulas in Sheet2 depend on TODAY(), the sub-range will depend on what today's date is.`
David Tannenbaum replied to zvkmpw on 11-Aug-12 06:17 PM
`Amazing, thank you!!!`