Lookup with dynamic range

[Ola Sigurdh]

Hello

I have a table that I want to lookup a date and then return the last value
in column e for that date. For example if I lookup day 1 I want it to return
6. I know the code to make a dynamic range
OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1 (thank´s Debra), but it does
not stop counting for a blank cell. I have tried to combine an
INDEX(E1:E31,MATCH(F1,A1:A31,0)+G1) with the formula for a dynamic range
but for the above reason it did not work. Hope you understands my question.

A B C D E
1 Date Value
2 1 1
3 2
4 3
5 6
6
7 2 9
8 10
9 19
10
11 5 18
12 19
13 20
14 21
15 22
16 100

Tia

Ola

 

[Max]

One play which might suffice ..

Assuming the sample table as posted

Put in C2: =IF(B2="",ROW(),"")
Copy down to C17 (till one row just below the last row of data in col B)
(Leave C1 empty)

Put in D2: =SMALL(A:A,ROWS($A$1:A1))

Put in E2:
=INDEX(B:B,MATCH(SMALL(C:C,ROWS($A$1:A1)),C:C,0)-1)

Select D2:E2, fill down until #NUM appears (till E5 in this case)

Now use the data returned in cols D & E
as the reference table in say, a VLOOKUP

We could enter the date to lookup in F1, e.g.: 1
and then put in G1: =VLOOKUP(F1,D:E,2,0)
to retrieve the last value from col B for that date

 

[Max]

Assuming the sample table as posted

Sorry, I had wrongly taken "Value" to be in col B (instead of col E) in the
suggested set-up. Please adapt the formulas accordingly to suit ..

 

[Ola Sigurdh]

Thank´s for your reply Max

Something is wrong or I dont understand what you mean

One play which might suffice ..

Assuming the sample table as posted

Put in C2: =IF(B2="",ROW(),"")
Copy down to C17 (till one row just below the last row of data in col B)
(Leave C1 empty)

This one I understand it puts a row nr in column C if there is something in
column B

Put in D2: =SMALL(A:A,ROWS($A$1:A1))

This one gets me confused because it only gives me 1 in every Row so the
next formula is not working. The answer is 0 and not 6

 

[Max]

Thank´s for your reply Max
Something is wrong or I don't understand what you mean

Apologies, I messed it up in the earlier response ..

Here's a sample file with a working construct,
revised with the "Value" col correctly placed in col E:
http://www.savefile.com/files/4923051
File: OlaSigurdh_gen.xls

The revised construct:

With "Date" in col A, "Value" in col E as per orig. post

Put in F2: =IF(E2="",ROW(),"")
Copy down to F17 (till one row just below the last row of data in col E)
(Leave F1 empty)

Put in G2: =SMALL(A:A,ROWS($A$1:A1))

Put in H2:
=INDEX(E:E,MATCH(SMALL(F:F,ROWS($A$1:A1)),F:F,0)-1)

Select G2:H2, fill down until #NUM appears (till H4 in this case)

Now use the data returned in cols G & H
as the reference table in say, a VLOOKUP

We could enter the date to lookup in I1, e.g.: 1
and then put in J1: =VLOOKUP(I1,G:H,2,0)
to retrieve the last value from col B for that date