vlookup with conditions

[rdev]

See the table below
Month Acc_code Amount
Jan-11 OP1102 5000
Jan-11 OP1105 4000
Jan-11 OP1108 3000
Jan-11 OP1109 2000
Jan-11 OP1201 -14000
Feb-11 OP1102 -13999
Feb-11 OP1108 -13998
Feb-11 OP1105 -13997

I have the table above in column A , the month , column B , the Acc-code and column C the Amount. I am trying a vlookup to work such that say if cell I5 = Cell A1 , then vlookup will concatenate OP with 1102 and return value in A3 above, if Feb 11 is in cell I5 , then lookup will concatenate to 1102 to return value -13999. I will then drag and drop formula over a range .

Jan-11( Cell I5)
OP
1102 0
1108 0
1201 0

Any clue how i can achieve this??

 

[trunten]

Hi rdev,

The only way to achieve what you want using lookups is to use dynamic ranges, however, IF you only have one instance of an OP code for each month you could use sumifs.
eg:

=SUMIFS(Amount_Range,Date_Range,I5,Account_Range,"OP"&I7)

 

[rdev]

Hi Can you guide me thru the dynamic range just in case i have to use it one day