|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 8th, 2009, 03:04 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup
How do you use vlookup when you have several items you want to look up and
return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5 
|
|
#2
April 8th, 2009, 03:21 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1...
Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5
|
|
#3
April 8th, 2009, 03:32 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Would I still use vlookup? I want to total R5 which would be 11 on the R5 on
the other page. All the other infor 1st page 2nd page R5 5 R5 11 R4 2 R4 2 R3 3 R3 3 R5 4 R2 3 R2 3 R5 2 "Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5
|
|
#4
April 8th, 2009, 03:50 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
No... This will sum all rows (Col B values) where Sheet2 COL A matches ...the value in Sheet1 A1... If you want to copy it down then use this =SUMPRODUCT(--(Sheet2!A$1:A$100=A1),(Sheet2!B$1:B$100)) so that lookup range remains same... of course you need to change 100 to the last row of your data on Sheet2... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: Would I still use vlookup? I want to total R5 which would be 11 on the R5 on the other page. All the other infor 1st page 2nd page R5 5 R5 11 R4 2 R4 2 R3 3 R3 3 R5 4 R2 3 R2 3 R5 2 "Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5
|
|
#5
April 8th, 2009, 04:05 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Thank you
"Sheeloo" wrote: No... This will sum all rows (Col B values) where Sheet2 COL A matches ...the value in Sheet1 A1... If you want to copy it down then use this =SUMPRODUCT(--(Sheet2!A$1:A$100=A1),(Sheet2!B$1:B$100)) so that lookup range remains same... of course you need to change 100 to the last row of your data on Sheet2... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: Would I still use vlookup? I want to total R5 which would be 11 on the R5 on the other page. All the other infor 1st page 2nd page R5 5 R5 11 R4 2 R4 2 R3 3 R3 3 R5 4 R2 3 R2 3 R5 2 "Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5
|
|
#6
April 15th, 2009, 04:07 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
How do I use this on 2 different worksheets
"Networking Issues Error 0X80070005" wrote: Thank you "Sheeloo" wrote: No... This will sum all rows (Col B values) where Sheet2 COL A matches ...the value in Sheet1 A1... If you want to copy it down then use this =SUMPRODUCT(--(Sheet2!A$1:A$100=A1),(Sheet2!B$1:B$100)) so that lookup range remains same... of course you need to change 100 to the last row of your data on Sheet2... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: Would I still use vlookup? I want to total R5 which would be 11 on the R5 on the other page. All the other infor 1st page 2nd page R5 5 R5 11 R4 2 R4 2 R3 3 R3 3 R5 4 R2 3 R2 3 R5 2 "Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5
|
|
#7
April 15th, 2009, 05:06 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Vlookup
Will this work on different workbooks?
"Sheeloo" wrote: If the data shown is in Col A & B of sheet2 and you have R5 in A1 of sheet1... Enter this in B1 of sheet1 =SUMPRODUCT(--(Sheet2!A1:A100=A1),(Sheet2!B1:B100)) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Networking Issues Error 0X80070005" wrote: How do you use vlookup when you have several items you want to look up and return a total value. In this example I want to add all 3 items for a total of 15. Using vlookup it only returns the first R5 for a total of 5 R5 5 R5 5 R5 5
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
