| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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 |
| 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 | |||
Linear Mode
