Excel Lookup with multiple criteria and date range match

[denniswtlx]

I am trying to figure out how to code a formula that can search multiple fields in a 2nd table including matching a specific date to a date range.

Table 1 columns:
A-City Name
B-Number
C-Date
D-$ amount


Table 2:
A-Number
B-City Name
C-Beginning Date
D-Ending Date

Goal is to match Table1 B to Table 2 A + match Table 1 C to the range of Table 2 C & D, returning Table 2 B into Table 1 A. Any help?

Table 1
Cost Center NUMBER Date AMOUNT
CHGO 101 1/16/2013 $22.25
CINTI 101 2/28/2013 $44.61
BOSTON 101 4/26/2013 $33.00
BOSTON 101 5/12/2013 $67.23
CLEVE 102 2/22/2013 $10.94
DETROIT 102 4/30/2013 $19.81
NYC 102 5/20/2013 $20.58
NYC 104 1/25/2013 $35.13
CLEVE 104 3/1/2013 $12.77
CLEVE 104 3/20/2013 $22.20
CHGO 105 2/28/2013 $10.94
CHGO 106 11/23/2012 $111.16
CLEVE 106 2/25/2013 $333.48
CLEVE 106 4/27/2013 $111.16
CINTI 106 6/3/2013 $576.53
CINTI 106 6/4/2013 $86.70
CLEVE 107 2/12/2013 $29.50
CLEVE 107 3/16/2013 $38.68
BOSTON 107 3/17/2013 $15.08

Table 2
Number Cost Center Created Date End Date
101 CHGO 12/1/2012 2/23/2013
101 CINTI 2/24/2013 3/10/2013
101 BOSTON 3/11/2013 1/1/2500
102 CHGO 1/1/2013 1/24/2013
102 CLEVE 1/25/2013 3/16/2013
102 DETROIT 3/17/2013 5/12/2013
102 NYC 5/13/2013 1/1/2500
104 NYC 1/10/2012 2/12/2013
104 CLEVE 2/13/2013 1/1/2500
105 CHGO 2/13/2013 1/1/2500
106 CHGO 10/15/2012 1/21/2013
106 DETROIT 1/22/2013 2/15/2013
106 CLEVE 2/16/2013 4/17/2013
106 BOSTON 4/28/2013 5/12/2013
106 CINTI 5/13/2013 1/1/2500
107 CLEVE 1/4/2013 3/16/2013
107 BOSTON 3/17/2013 1/1/2500

[oeldere]

your tekst confuses me; but I bet you get better help if you add an excel file, without confidentional information.

Please also add the desired (expected) result in your file.

[Aladin Akyurek]

Sheet1, A2, control+shift+enter, not just enter:

=INDEX(Sheet2!$B$2:$B$100,MATCH(1,IF(Sheet2!$A$2:$A$100=$B2,IF($C2>=Sheet2!$C$2:$C$100,IF($C2<=Sheet2!$D$2:$D$100,1))),0))