|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 03:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com