Hi Team,

I think I've lost it :P

I've got two lists.
c d e
9 Morry 2000 2004
10 Howie 2005 2009
11 Johno 2010 2019
12 Smithy 2020 2029
13 Jones 2030 3000

and
c d
19 John 2005
20 Sam 2010
21 Ralph 2011
22 fred 2001
23 mike 2020
24 Nat 2008
25 Sally 2015
26 steff 2025
27 diane 2027

I want to pull the name from list 1 and place it in column e if the number
in list 2 column d falls in the range in list1 columns d&e

I have this formula working to an extent

=SUMPRODUCT(--($E$9:$E$13=$D19),--($D$9:$D$13<=$D19)*($C$9:$C$13))

This returns a #value error.

if I do some checking and modify list 1 to add a unique number in column f

=SUMPRODUCT(--($E$9:$E$13=$D19),--($D$9:$D$13<=$D19),$F$9:$F$13)

It returns the correct unique number.....
So I figure the problem is returning the text??

Changing the forumla to this
=SUMPRODUCT(--($E$9:$E$13=$D19),--($D$9:$D$13<=$D19),$C$9:$C$13)

does not work as it returns all 0.

any help is much welcomed.