vlookup useful?

[Timo]

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

 

[NBVC]

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

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Eduardo]

Hi,
try

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

 

[NBVC]

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 nam
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


Office Discussion' (http://www.thecodecage.com))

------------------------------------------------------------------------

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

--
NBV

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com

 

[Timo]

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