Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup useful?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup useful?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup useful?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup useful?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup useful?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup useful?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |