Quick Sumproduct vs Text return
 

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.

Quick Sumproduct vs Text return
 

Try this:

Entered in E19 and copied down to E27:

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

--
Biff
Microsoft Excel MVP


"Madduck" wrote in message
...
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.

Quick Sumproduct vs Text return
 

Thanks Much.

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

Good works as always. Ta
Madduck

"T. Valko" wrote:

Try this:

Entered in E19 and copied down to E27:

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

--
Biff
Microsoft Excel MVP


"Madduck" wrote in message
...
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.

Quick Sumproduct vs Text return
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Madduck" wrote in message
...
Thanks Much.

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

Good works as always. Ta
Madduck

"T. Valko" wrote:

Try this:

Entered in E19 and copied down to E27:

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

--
Biff
Microsoft Excel MVP


"Madduck" wrote in message
...
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.