Vlookup

  • Thread starter Thread starter Faiz
  • Start date Start date
F

Faiz

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.
 
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
 
Back
Top