|
Lookup problem Excel capabilities
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. |
Lookup problem Excel capabilities
Try Vlookup. Here's some info from Excel-help:
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_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 -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Tarquin Krikery" wrote: 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. |
Lookup problem Excel capabilities
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" wrote: 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! -- Biff Microsoft Excel MVP |
Lookup problem Excel capabilities
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? -- Biff Microsoft Excel MVP "Tarquin Krikery" wrote in message ... 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" wrote: 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! -- Biff Microsoft Excel MVP |
| All times are GMT +1. The time now is 03:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com