Quick Sumproduct vs Text return

[Madduck]

Hi Team,

I think I've lost it

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.

 

[T. Valko]

Try this:

Entered in E19 and copied down to E27:

=INDEX(C$9:C$13,MATCH(1,INDEX((D19>=D$9$13)*(D19<=E$9:E$13),,1),0))

 

[Madduck]

Thanks Much.

Now I just have to work out how that works and why

Good works as always. Ta
Madduck

 

[T. Valko]

You're welcome. Thanks for the feedback!