pull data from one table to another if multiple conditions are met

[Icyhot]

I have two tables. One has a column for State and a column for Month/Year.
The other table has a column for State, a column for beginning period and a
column for ending period. There are other columns in each table. When the
Month/Year in the first table falls between the beginning and ending period
in the second table and the State is the same, I want to pull a value from
the second table to the first table. Can Excel do it? How?

Table 1 (thousands of records with different State and Month/Year)
NY 03/2004
NY 05/2005
....
CA 08/2003
CA 10/2004
....

Table 2
NY 01/2001 12/2003 rate1
NY 01/2004 12/2006 rate2
....
CA 01/2001 12/2003 rate3
CA 01/2004 12/2006 rate4
....

The target result should be

NY 03/2004 rate2
NY 05/2005 rate2
....
CA 08/2003 rate3
CA 10/2004 rate4
....

 

[Stefi]

Use a helper column, say E, in Table 2, with formula
=VALUE(RIGHT(B2,4)&LEFT(B2,2))
filled down as required (results shall be numbers: 200101, 200401, ...).

Enter formul
=INDEX(OFFSET(INDIRECT(ADDRESS(MATCH($A2,Table2!$A:$A,0),4,4,1,"Table2")),0,0,COUNTIF(Table2!$A:$A,$A2),1),$E1)

in C2 of Table 1, where Table2 is the sheet name containing Table 2. Fill it
down as required.

It works only if rows are sorted by state and month/year like in your example!

Regards,
Stefi

„Icyhot†ezt írta:

 

[Herbert Seidenberg]

Excel 2007 Tables
No sorting needed.
No volatile functions (offset, indirect).
English readable formulas.
http://www.mediafire.com/file/vnajtgljaom/09_17_09.xlsx

 

[Icyhot]

Brilliant! Thanks a lot!