Matching data from 2 sources, based on two conditions using multiple VLOOKUP and arrays

[agarman]

Hi all,

Huge thanks in advance for anyone who spends any time on this. I have been a spectator of this forum for quite sometime and have been helped out immensely by people's queries and solutions... This is my first time actually posting so I hope my query (and potential solution) is helpful to someone else! Here goes...

I am trying to match data from two spreadsheet sources based on two common criteria. To put the problem into context, I am trying to allocate our company cars fuel bills to the appropriate division/job number. The sorted incoming data consists of:

Spreadsheet 1 (S1) from Fuel Provider: Column A - Car Registration # ; Column B - Date of Fuel Purchase ; Column C - Cost of Fuel.

Spreadsheet 2 (S2) from our company resource allocation schedule: Column A - Car Registration # ; Column B - Allocation Start Date ; Column C - Allocation End Date ; Column D - Job/Division Number.

So, if the car registrations in the two Column As match, and the date of the purchase (S1:Column B) falls between the dates in S2:Columns B and C, I need to return the Job/Division number to S1 so that the cost in S1:Column C can be charged to the right account.

The logic seemed simple enough to me at first, but having tried numerous combinations of VLOOKUP and IFs in arrays and a couple of INDEX, MATCH combos (although I'd be the first to admit I'm a complete novice with the latter functions), I always come back to the same problems... 1) VLOOKUP will only return a single match for the Car Registration when in fact there instances of multiple results (since one car may work on 2 or more jobs simultaneously); 2) I can't seem to get the IF function to work properly in an array, in order to scan the Job allocation periods to match the right purchase date.

I've been in awe of some of the neat tricks posted on here in the past, so I've every confidence someone out there has a solution!

With thanks for your valuable time,

Andrew

 

[T. Valko]

Using named ranges:

Reg refers to your "S2" column A
StartDate refers to your "S2" column B
EndDate refers to your "S2" column C
Div refers to your "S2" column D

On "S1" :

A2 = some reg number
B2 = some date

Enter this array formula in "S1" cell D2:

=INDEX(Div,MATCH(1,(Reg=A2)*(StartDate<=B2)*(EndDate>=B2),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

 

[agarman]

Fantastic! Works perfectly.

I was clearly barking up the wrong tree with VLOOKUPs and IFs... It seems the INDEX, MATCH combo is far more flexible than the above functions... Will have to do some serious swatting up on these.

Many, many thanks for your help.

 

[T. Valko]

You're welcome!