Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
Example:
Monthly Exp.xlsx Cloumn 1 Col2 Col3 Expense Heads Dept Amount 5121 100 Formula(Need the data here) 5122 100 Formula(Need the data here) 5123 100 Formula(Need the data here) Jan09.xlsx Expense Heads Dept Amount 5121 100 6500.00 5122 100 1215.00 5123 100 200.00 5121 120 3500.00 5122 120 1215.00 5123 120 3250.00 5121 140 2500.00 5122 140 1215.00 5123 140 3150.00 I need in worksheet 1 i.e Monthly exp in column 3 to look(bring) the amount from sheet Jan09, amount of 5121 where the dept is 100. It is clearly visible that 5121 head is present with dept 100,120 & 140 in my example Can anyone please help me on this? Hope that is clear. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup
When you have multiple criteria use SUMPRODUCT()
=SUMPRODUCT((A1:A10=criteria1)*(B1:B10=criteria2), C1:C10) If you are using Excel 2007 you can use SUMIFS() to acheive the same result =SUMIFS(C1:C10,A1:A10,criteria1,B1:B10,criteria2) 'or with cells F1 and F2 holding the criteria =SUMIFS(C1:C10,A1:A10,F1,B1:B10,F2) If this post helps click Yes --------------- Jacob Skaria "Faiz" wrote: Example: Monthly Exp.xlsx Cloumn 1 Col2 Col3 Expense Heads Dept Amount 5121 100 Formula(Need the data here) 5122 100 Formula(Need the data here) 5123 100 Formula(Need the data here) Jan09.xlsx Expense Heads Dept Amount 5121 100 6500.00 5122 100 1215.00 5123 100 200.00 5121 120 3500.00 5122 120 1215.00 5123 120 3250.00 5121 140 2500.00 5122 140 1215.00 5123 140 3150.00 I need in worksheet 1 i.e Monthly exp in column 3 to look(bring) the amount from sheet Jan09, amount of 5121 where the dept is 100. It is clearly visible that 5121 head is present with dept 100,120 & 140 in my example Can anyone please help me on this? Hope that is clear. |
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 |