Lookup problem Excel capabilities

[Tarquin Krikery]

Hi I have a bit of a problem I'm hoping someone can help with. Not sure if
what I want to do is possible within Excel so any help would be gratefully
received!

Ok I have two tables. One is a list of leads that reads something like this:

Post Sect Lead Date
NR1 8 20091218
NR1 8 20091220
CB1 7 20090921
CB1 7 20091211

It's a list of all the leads we have generated from our leafleting.

there is also another table below with a list of all the dates and postal
sectors our company has delivered a leaflet to in the past year.

Post Sect Drop Date Drop No
NR1 8 20091018 1
NR1 8 20091201 2
CB1 7 20090917 1
CB1 7 20091201 2
NE13 9 20091208 1

The date format is from our system so I know it's a little unusual.

Anyway, what I want to do is perform a vlookup (or something else whatever
is needed) to look at the postal sector of a lead and then work out which of
the drops the lead responds to. Obviously it will be the drop occasion either
equal to or previous to the input date (i.e it will be the same day or prior
to the input date, not after it.

Anyone have a clue how I can do this? It's making my head spin. I've tried
using a combo of Vlookups and If's but can't get it to work.

 

[ryguy7272]

Try Vlookup. Here's some info from Excel-help:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Notice: Range_lookup is a logical value that specifies whether you want
VLOOKUP to find an exact match or an approximate match. If TRUE or omitted,
an approximate match is returned. In other words, if an exact match is not
found, the next largest value that is less than lookup_value is returned. If
FALSE, VLOOKUP will find an exact match. If one is not found, the error value
#N/A is returned.

This is a good resource too:
http://www.contextures.com/xlFunctions02.html

 

[T. Valko]

Hmmm...

So, you want to lookup

NR1 8...20091218
From:

NR1 8...20091018...1
NR1 8...20091201...2

And the date to match will be <=20091218

So, which drop number would you expect for the result? Both dates in the
"from" table are <= the lookup date.

I predict great difficulty in doing this!

 

[Tarquin Krikery]

RYAN - thanks I am fairly confident with vlookup and use it all the time,
though it does not seem to cover what I am in need of on this occassion. I
may need to use it in conjunction with an IF statement or something else as
the vlookup will have more than one result available to it on the lookup
table.

BIFF - Yeah, that's the problem I am having!

It needs to be the drop date closest to the input date - but PRIOR to the
input date. This could be really hard as the drop date closest could be the
middle of 5 seperate entries for one postal sector!

From the example given (i.e the one you've shown below) I would expect the
result for the drop No to be "2".

I had a feeling this would be hard, I have been racking my brain for days as
to how I would do this, but have only given myself migraines!

Any help or ideas would be greatly received!

 

[T. Valko]

Ok...

You have 2 lookup values:

Post Sect Lead Date
NR1 8 20091218
NR1 8 20091220

And the lookup table:

Post Sect Drop Date Drop No
NR1 8 20091018 1
NR1 8 20091201 2

So, based on your explanation the expected result for both lookup values
would then be 2. Is that correct?

Can you post several more examples and tell me what the expected results
should be?