How do I select the correct record?
 

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

Why not use Autofilter?

"HansM" wrote in message
...
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

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" wrote in message
...
Why not use Autofilter?

"HansM" wrote in message
...
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

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" wrote in message
...
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