Lookup with dynamic range

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

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Ola Sigurdh" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
>
>
>
>

> 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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Thank´s for your reply Max

Something is wrong or I dont understand what you mean


"Max" <[email protected]> skrev i meddelandet
news:[email protected]...
> 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
>
> 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
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "Ola Sigurdh" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> >
> >
> >
> >
> >
> >
> >
>
>

"Ola Sigurdh" wrote:
> 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

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--