How do I select the correct record?

[HansM]

Following is an example of my data, dates as YYYYMMDD:

Row 1: CustomerCode / Number / Effective Date / Expiration Date
Row 2: AAAAAAA-4 / SL00001 / 20040301 / 20050301
Row 3: AAAAAAA-3 / SL00001 / 20030301 / 20040301
Row 4: AAAAAAA-2 / SL00001 / 20020301 / 20030301
Row 5: AAAAAAA-1 / SL00001 / 20010301 / 20020301

I have an event date, for example 20030613. I need to find the correct
Customer Code so I can select other data stored on the same row. I'm lost
as how I can do a lookup the customer code where effective date =< event
date < expiration date. I have wondered about some INDEX function or a
SUMPRODUCT, but I have not advanced any.

Thank you for any insight.
--
HansM

[Brenda Rueter]

Why not use Autofilter?

"HansM" <[email protected]> wrote in message
news:[email protected]...
> Following is an example of my data, dates as YYYYMMDD:
>
> Row 1: CustomerCode / Number / Effective Date / Expiration Date
> Row 2: AAAAAAA-4 / SL00001 / 20040301 / 20050301
> Row 3: AAAAAAA-3 / SL00001 / 20030301 / 20040301
> Row 4: AAAAAAA-2 / SL00001 / 20020301 / 20030301
> Row 5: AAAAAAA-1 / SL00001 / 20010301 / 20020301
>
> I have an event date, for example 20030613. I need to find the correct
> Customer Code so I can select other data stored on the same row. I'm lost
> as how I can do a lookup the customer code where effective date =< event
> date < expiration date. I have wondered about some INDEX function or a
> SUMPRODUCT, but I have not advanced any.
>
> Thank you for any insight.
> --
> HansM
>
>

[HansM]

Autofilter will let me manually find a certain row. I have thousands of
items like this where I have an event date that I need to fit to the correct
customer code. From that code I can then find other items that I need to do
a vlookup on.
--
HansM

"Brenda Rueter" <[email protected]> wrote in message
news:[email protected]...
> Why not use Autofilter?
>
> "HansM" <[email protected]> wrote in message
> news:[email protected]...
>> Following is an example of my data, dates as YYYYMMDD:
>>
>> Row 1: CustomerCode / Number / Effective Date / Expiration Date
>> Row 2: AAAAAAA-4 / SL00001 / 20040301 / 20050301
>> Row 3: AAAAAAA-3 / SL00001 / 20030301 / 20040301
>> Row 4: AAAAAAA-2 / SL00001 / 20020301 / 20030301
>> Row 5: AAAAAAA-1 / SL00001 / 20010301 / 20020301
>>
>> I have an event date, for example 20030613. I need to find the correct
>> Customer Code so I can select other data stored on the same row. I'm
>> lost
>> as how I can do a lookup the customer code where effective date =< event
>> date < expiration date. I have wondered about some INDEX function or a
>> SUMPRODUCT, but I have not advanced any.
>>
>> Thank you for any insight.
>> --
>> HansM
>>
>>
>
>

[Earl Kiosterud]

Hans,

I think, given that there are no holes in your date ranges, you could just
do a VLOOKUP on the Effective Date column. You'd have to sort your table so
the dates are ascending. And you'd need to get the Effective Date column
(or a column linked to it) to the left of the other columns from which
you'll want to retrieve data. This is because VLOOKUP only retrieves from
columns to the right of the lookup column.

If you can't sort the table, then MATCH to get the row number:

=MATCH(date, EffectiveDateColumn, -1)

Then use that to retrieve, e.g.: the CustomerCode, in an INDEX function:

=INDEX(CustomerCodeColumn, MATCH(EventDate, EffectiveDateColumn, -1))

I think this will work with your table the way it is. No rearranging
needed.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"HansM" <[email protected]> wrote in message
news:[email protected]...
> Following is an example of my data, dates as YYYYMMDD:
>
> Row 1: CustomerCode / Number / Effective Date / Expiration Date
> Row 2: AAAAAAA-4 / SL00001 / 20040301 / 20050301
> Row 3: AAAAAAA-3 / SL00001 / 20030301 / 20040301
> Row 4: AAAAAAA-2 / SL00001 / 20020301 / 20030301
> Row 5: AAAAAAA-1 / SL00001 / 20010301 / 20020301
>
> I have an event date, for example 20030613. I need to find the correct
> Customer Code so I can select other data stored on the same row. I'm lost
> as how I can do a lookup the customer code where effective date =< event
> date < expiration date. I have wondered about some INDEX function or a
> SUMPRODUCT, but I have not advanced any.
>
> Thank you for any insight.
> --
> HansM
>