How do you use sumproduct to return specific cell data?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brian - 13 Sep 2007 19:36 GMT

I have the following scenario:
A B C D E
1Hotel# 1Job# 2Hotel# 2Job# Staff#
101 6001 100 5002 231356
103 5002 101 6001 253586
406 2025 503 2025 365412
503 2025 601 6004 894561

The data from col A & B are connected by row ... meaning 101 & 6001 go
together and should not be worked individually (thus the number 1 in the col
headers connect those two cols, and the number 2 in the col headers connect
those two columns C and D).

I want to find a way for the formula to take the data that is in the same
row in col A/B, in this case 101/6001, and find a match anywhere in col C/D,
and return to me the data in col E that is in the same row as the match. So,
for instance, row 1, I have 101 and 6001. I look down col C/D and find a
match 101 and 6001 in the second row. So, the returned data would be 253586
because it is in the same row as the match found in C/D.

Sorry, for the long way about this. I thought I could use sumproduct.

Reply to this Message

Toppers - 13 Sep 2007 19:48 GMT

=SUMPRODUCT(--($C$2:$C$5=F2),--($D$2:$D$5=G2),$E$2:$E$5)

F2=101
G2=6001

HTH

> I have the following scenario:
> A B C D E
[quoted text clipped - 17 lines]
>
> Sorry, for the long way about this. I thought I could use sumproduct.

Reply to this Message

Brian - 14 Sep 2007 19:42 GMT

Thank you so much and yes, this works ... but I failed to mention a few
things.

There is a possibility that there will be duplicated data pairs in col C/D.
Right now, the formula adds them together (and rightfully so). I just would
like it to return a single piece of data in col E).

And ... there is the possibility that the data pair up in col A/B may be
duplicated several times. So for the second and third duplicated data pair,
it will still give me the result in col E that corresponds always to the very
first match it finds. Is it possible for the second duplicate data pair to
move past the first find (since it is theoretically "taken" by the first data
pair from col A/B).

I do apologize for not giving you all the info. I am very new at this.
Thanks!

> =SUMPRODUCT(--($C$2:$C$5=F2),--($D$2:$D$5=G2),$E$2:$E$5)
>
[quoted text clipped - 24 lines]
> >
> > Sorry, for the long way about this. I thought I could use sumproduct.

Reply to this Message