Index:Match where the lookup matches a range perfectly, but the result is greater than or equal to...

[RockandGrohl]

Sorry if that's confusing. There are two sheets, both have a unique reference on them. One is enquiry data, the other is booking data.

I need to match enquiry dates to booking dates to work out lead times, find out how long it takes our customers to book after requesting a brochure (enquiry)


A problem ensues when someone enquires on 5th May 2016, then books on 10th September 2016, then books another holiday again on the 4th July 17.

The formula I have that pulls through from the reference is showing the very latest booking, when in reality it needs to show the next booking after the enquiry date. Going back to this example, the lead time is being shown as 425 days when really it should be 128 days as their booking after the enquiry was approximately 4 months.

ENQUIRY DATA
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]H[/TD]
[TD]J[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]P[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Enquiry Date[/TD]
[TD]Book Date[/TD]
[TD]First Enquiry[/TD]
[TD]Previous Booking[/TD]
[TD]Lead Time[/TD]
[TD]Ref[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]05/05/2016[/TD]
[TD]Ignore[/TD]
[TD]05/05/2016[/TD]
[TD]04/07/2017[/TD]
[TD][/TD]
[TD]AAAA001[/TD]
[TD]Incorrect[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]05/05/2016[/TD]
[TD]Ignore[/TD]
[TD]05/05/2016[/TD]
[TD]10/09/2016[/TD]
[TD][/TD]
[TD]AAAA001[/TD]
[TD]Correct[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












What I need to see is their first booking AFTER the enquiry date.

On the example above, column Q is where the formula sits. Currently, it's this: =IFERROR(INDEX(BookData!H:H,MATCH(EnqData!P:P,BookData!N:N,0)),"")


BOOKING DATA
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]J[/TD]
[TD]_[/TD]
[TD]_[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Book Date[/TD]
[TD]Tour[/TD]
[TD][/TD]
[TD][/TD]
[TD]Ref[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10/09/2016[/TD]
[TD]France[/TD]
[TD][/TD]
[TD][/TD]
[TD]AAAA001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/03/2018[/TD]
[TD]Yorkshire[/TD]
[TD][/TD]
[TD][/TD]
[TD]AAAA001[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]19/05/2018[/TD]
[TD]Weymouth[/TD]
[TD][/TD]
[TD][/TD]
[TD]AAAA001[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]12/10/2018[/TD]
[TD]Germany[/TD]
[TD][/TD]
[TD][/TD]
[TD]BBLR056[/TD]
[/TR]
</tbody>[/TABLE]












Looking at the above, for customer AAAA001, the date I want to pull through in Column R is the next booking date greater than or equal to the enquiry date of 27/04/2018.

The correct answer would be 19/05/2018 which would have a lead time of approximately 20 or so days.

However, my current index-match will just pull through whatever's first on the list, which more often than not is incorrect.


So in summary, what I need is a formula to place in Q. This will index column H on 'bookdata', and match cell P2 on 'EnqData' against the range N:N on BookData... The tricky bit is only matching where the criteria that H in BookData is >= C2 in EnqData, but only the first largest one up, not the absolute largest in the range.


Do you think this is possible to do without an array formula? We have a list of 195,000 enquiries here

 

[steve the fish]

Impossible without an array formula of some sort as its searching an array of values for your criteria. Can we assume that the data is in date order? Essentially that the next enquiry is placed at the bottom of the data? If so something like:

=IFERROR(INDEX(BookData!$H$2:$H$1000,MATCH(1,INDEX((BookData!$H$2:$H$1000>=C2)*(BookData!$N$2:$N$1000=P2),0),0)),"")

You will have to change the ranges to suit.

 

[RockandGrohl]

Impossible without an array formula of some sort as its searching an array of values for your criteria. Can we assume that the data is in date order? Essentially that the next enquiry is placed at the bottom of the data? If so something like:

=IFERROR(INDEX(BookData!$H$2:$H$1000,MATCH(1,INDEX((BookData!$H$2:$H$1000>=C2)*(BookData!$N$2:$N$1000=P2),0),0)),"")

You will have to change the ranges to suit.

Thanks for this, I'll have to add a small change to my macro to re-sort the data when new data is applied, so it's always in date order.

Do the formulas have to be absolute? Currently they're set as H:H and N:N etc... is it possible to leave them as this or will it increase calculation time significantly?

Thanks.

 

[RockandGrohl]

Also another thought, there are 190,000 rows with enquiry data, but the array formula probably only needs to go to around 10,000 rows.. Is there a way to prevent the array formula calculating a row it doesn't need to? Currently I have it set so that if the enquiry reference cannot be found in the booking data, then return blank, otherwise return the formula to give a booking date.

Is this correct or is there a faster way? Thanks.

 

[steve the fish]

There's an option that I could write where the data didn't need sorting but it would be slow. If you could sort where most recent data was at the top and you knew all of your results would be captured by 1000 rows then it would be much faster but would need to be another formula. It's your data so you need to decide what is appropriate. You could even carve your data up so data not required could be archived away. Up to you really.