asked on

Excel 2007 question - using VLOOKUP with Between Date function to return value

I have attached a spreadsheet that shows start and stop dates for certain volume targets (column I) by customer number (column A). Some records are only one month total, others are many.

I need to take this information and condense it down to ~500 records starting in column M, that shows the customer volume for each month. so for the first record on row 2, I need a formula that says to vlookup M2, then if the records in A2 through I14,000 has a date between columns F & G, then take the volume amount from column I and display. Not sure if you can combine a VLOOKUP and a date between function, but that is what I'm trying to accomplish. Thanks in advance. Book2.xlsx

barry houdini

Hello carndar,

I put this formula in L2 copied down to L507 (hide if required or move somewhere else)

=COUNTIF(A:A,M2)

and then this formula in N2 copied across and down to AB507

=LOOKUP(N$1,INDEX($F$2:$F$15000,SUM($L$1:$L1)+1):INDEX($F$2:$F$15000,SUM($L$2:$L2)),INDEX($I$2:$I$15000,SUM($L$1:$L1)+1):INDEX($I$2:$I$15000,SUM($L$2:$L2)))

That could be shortened using OFFSET but the formula would probably be less efficient

Note: I formatted columns F and G as dates - that isn't strictly necessary but it won't affect the formula

see attached

regards, barry
27328856.xlsx

barry houdini

...actually....better with VLOOKUP rather than LOOKUP, then it can be shorter, i.e. this formula in N2 copied across and down

=VLOOKUP(N$1,INDEX($F$2:$F$15000,SUM($L$1:$L1)+1):INDEX($I$2:$I$15000,SUM($L$2:$L2)),4)

see revised attachment

I note that there are some #N/A errors further down (because the date ranges for some codes don't start until 2011). If you want the formula can be modified to show zero, e.g. with IFERROR

=IFERROR(VLOOKUP(N$1,INDEX($F$2:$F$15000,SUM($L$1:$L1)+1):INDEX($I$2:$I$15000,SUM($L$2:$L2)),4),0)

barry
27328856v2.xlsx

redmondb

... and please see attached my version -

=SUM($I$2:$I$15000*($A$2:$A$15000=$M2)*(N$1>=$F$2:$F$15000)*(N$1<=$G$2:$G$15000))

Few points...
(1) Barry and I have, I think, differed in our handling of multiple entries matching the criteria. In any event, I have pasted Barry's values (from his first file) starting in column M and highlighted the differences in red.
(2) You mentioned row 14,000 as being the last to check, but there are a number of rows beyond that. Like Barry, I assumed that this was a misprint and used 15,000 instead.
(3) My formulas must be entered as array formulas so use Ctrl+Shift+Enter rather than simply Enter.

Regards,
Brian.

Book2-V4.xlsx

ASKER CERTIFIED SOLUTION

barry houdini

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

redmondb

carndar,

The FormulaMeister's "SumIfs" is definitely the way to go - my "Sum" is an absolute dog (coming from an old dog, perhaps?).

Nice one Barry!

Regards,
Brian.

carndar

ASKER

You guys are incredible! Thanks for the quick response!

barry houdini

Thanks for the comments carndar - it might be that SUMIFS was the best solution for you but please can you consider re-allocating the points here - I wouldn't have arrived at that solution without Brian's work - that was just a more efficient version of the formula Brian had already proposed - so a points split here would be more equitable I think

regards, barry

redmondb

Barry,

I appreciate your kind offer more than any points. I'm happy for things to be left as they are.

Swings and roundabouts!

Regards,
Brian.