How do I output a client name based on a year range and ID code for that year?

[kmprice710]

I have a bunch of clients on a list with the year they purchased the product:

ID	Year	Client
1	2005	?
2	2004	?
3	2002	?
1	2006	?

<tbody>
</tbody>

I have a list of ID codes with year ranges:

ID	From	To	Client
1	2001	2002	Barry
1	2003	2004	Barry-Elwood
1	2005	2006	Barry-Yearling
2	2001	2002	Todd
2	2003	2004	Todd-Young
3	2001	2002	Smith
3	2003	2004	Smith-Armour

<tbody>
</tbody>

How do I write a formula to output the client names given the ID code and year ranges?

 

[kmprice710]

I have a bunch of clients on a list with the year they purchased the product:

ID	Year	Client
1	2005	?
2	2004	?
3	2002	?
1	2006	?

<tbody>
</tbody>

I have a list of ID codes with year ranges:

ID	From	To	Client
1	2001	2002	Barry
1	2003	2004	Barry-Elwood
1	2005	2006	Barry-Yearling
2	2001	2002	Todd
2	2003	2004	Todd-Young
3	2001	2002	Smith
3	2003	2004	Smith-Armour

<tbody>
</tbody>

How do I write a formula to output the client names given the ID code and year ranges?

I tried lookup and sumproduct and couldn't get it to work. Any ideas?

 

[zaquin]

I tried lookup and sumproduct and couldn't get it to work. Any ideas?

If I'm understanding your goal correctly, assuming A1:C5 is the top table and A7:D14 is the bottom table, you can put this formula in place of the ?:
=INDEX($D$8:$D$14,IF(ISEVEN(B2),MATCH(A2&B2,$A$8:$A$14&$C$8:$C$14),MATCH(A2&B2,$A$8:$A$14&$B$8:$B$14,0)))
It has to be an array formula, so while still in the cell, press Ctrl+Shift+Enter which will result in this:
{=INDEX($D$8:$D$14,IF(ISEVEN(B2),MATCH(A2&B2,$A$8:$A$14&$C$8:$C$14),MATCH(A2&B2,$A$8:$A$14&$B$8:$B$14,0)))}