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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Year
[/TD]
[TD]Client
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2005
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2004
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2002
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2006
[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]

I have a list of ID codes with year ranges:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]From
[/TD]
[TD]To
[/TD]
[TD]Client
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2001
[/TD]
[TD]2002
[/TD]
[TD]Barry
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2003
[/TD]
[TD]2004
[/TD]
[TD]Barry-Elwood
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]2005
[/TD]
[TD]2006
[/TD]
[TD]Barry-Yearling
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2001
[/TD]
[TD]2002
[/TD]
[TD]Todd
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]2003
[/TD]
[TD]2004
[/TD]
[TD]Todd-Young
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2001
[/TD]
[TD]2002
[/TD]
[TD]Smith
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2003
[/TD]
[TD]2004
[/TD]
[TD]Smith-Armour
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Year[/TD]
[TD]Client[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2005[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2004[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2002[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2006[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

I have a list of ID codes with year ranges:

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Client[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]Barry[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]Barry-Elwood[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2005[/TD]
[TD]2006[/TD]
[TD]Barry-Yearling[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]Todd[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]Todd-Young[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2001[/TD]
[TD]2002[/TD]
[TD]Smith[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]Smith-Armour[/TD]
[/TR]
</tbody>[/TABLE]

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)))}