Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
In Excel 2000, How do you select the whole of a worksheet (Select. | Excel Discussion (Misc queries) | |||
Dynamic Formulas with Dynamic Ranges | Excel Worksheet Functions | |||
How do I record a macro which should work on multiple files ? | Excel Discussion (Misc queries) | |||
How do I save a record from an excel template to a database in a . | Excel Discussion (Misc queries) |