Dear all,

I thought I could use vlookup and combine it with an if function. However I
don't know how to do it.

example sheet:

ID name shs test results ID name shs

1234 Timo 13 D2 1225 Timo 20
1225 Timo 20 D3 1234 Timo 10


I thought vlookup can help if (A2+B2) match (E2+F2), then return the value
in column G.

Result: Cell D2 should show value "10" (G3) and Cell D3 should show value
"20" (G2).

Thank you all in advance.

Regards, Timo

Try:

=Sumproduct(--($E$2:$E$100=$A2),--($F$2:$F$100=$B2),$G$2:$G$100)

adjust ranges to suit...

... assumes one match only will be found.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125354

Hi NBVC,

nope, I did not mention,

in this sheet I combine two sheets with data (A-C) and (E-G). The "Sum" of
"A2+B2" do exist in E:F somehow, but I don't know where. The name itself
exists more than once, but not as a combination. If these two cell
combinations match, I would like to get the value in G2,3,4...

Later, after this step, I can compare the values in C and D...

Regards,

Timo

T

"NBVC" wrote:


Try:

=Sumproduct(--($E$2:$E$100=$A2),--($F$2:$F$100=$B2),$G$2:$G$100)

adjust ranges to suit...

... assumes one match only will be found.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125354

Hi,
try

=SUMPRODUCT(--(F2=B2:B10),--(E2=A2:A10),D2:D10)

"Timo" wrote:

Dear all,

I thought I could use vlookup and combine it with an if function. However I
don't know how to do it.

example sheet:

ID name shs test results ID name shs

1234 Timo 13 D2 1225 Timo 20
1225 Timo 20 D3 1234 Timo 10


I thought vlookup can help if (A2+B2) match (E2+F2), then return the value
in column G.

Result: Cell D2 should show value "10" (G3) and Cell D3 should show value
"20" (G2).

Thank you all in advance.

Regards, Timo

Timo;452918 Wrote:
Hi NBVC,

nope, I did not mention,

in this sheet I combine two sheets with data (A-C) and (E-G). The "Sum"
of
"A2+B2" do exist in E:F somehow, but I don't know where. The name
itself
exists more than once, but not as a combination. If these two cell
combinations match, I would like to get the value in G2,3,4...

Later, after this step, I can compare the values in C and D...

Regards,

Timo

T

"NBVC" wrote:


Try:

=Sumproduct(--($E$2:$E$100=$A2),--($F$2:$F$100=$B2),$G$2:$G$100)

adjust ranges to suit...

... assumes one match only will be found.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread: 'vlookup useful? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=125354)



Althought the formula looks odd in that it looks like it is trying to
sum.. it is in fact finding instances where both column E and column F
values match together what is in A2 and B2.. and then it returns what is
in G2.. it works like Vlookup with multiple column matches.. but only
works if the column with return results is numeric (as is your
sample).... try it and see.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125354

Hi NBVC,

now it worked fine! Thanks. Thought I could quickly try, but A2 was not
within the first 100 rows. Now I changed it to 4000...

Thanks a million.

Timo

"NBVC" wrote:


Timo;452918 Wrote:
Hi NBVC,

nope, I did not mention,

in this sheet I combine two sheets with data (A-C) and (E-G). The "Sum"
of
"A2+B2" do exist in E:F somehow, but I don't know where. The name
itself
exists more than once, but not as a combination. If these two cell
combinations match, I would like to get the value in G2,3,4...

Later, after this step, I can compare the values in C and D...

Regards,

Timo

T

"NBVC" wrote:


Try:

=Sumproduct(--($E$2:$E$100=$A2),--($F$2:$F$100=$B2),$G$2:$G$100)

adjust ranges to suit...

... assumes one match only will be found.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' ('The Code Cage - Microsoft Office Help - Microsoft
Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------
NBVC's Profile: 'The Code Cage Forums - View Profile: NBVC'
(http://www.thecodecage.com/forumz/members/nbvc.html)
View this thread: 'vlookup useful? - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=125354)



Althought the formula looks odd in that it looks like it is trying to
sum.. it is in fact finding instances where both column E and column F
values match together what is in A2 and B2.. and then it returns what is
in G2.. it works like Vlookup with multiple column matches.. but only
works if the column with return results is numeric (as is your
sample).... try it and see.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125354