Compbination of Vlookup for two lines

[salil nagpal]

Hi,

Friends i need some help in vlookup formula. I have a dump where in i have project code and expenses code with monthly expenses.
Now i have individual sheet where in i need data from the dump.
I have attached a sheet for your reference , i need to fillin data in yellow colour cells.

regards
Salil

 

[kchiba]

You can do this with the SUMIFS formula as below

in Cell H5
=SUMIFS($D$4:$D$21,$B$4:$B$21,$F$5,$C$4:$C$21,G5)

Adjust to suit

kanti

 

[salil nagpal]

Thanks Kanti , it worked...

 

[sgmpatnaik]

@Salil

you can try another method with Array formula Ctrl+Shift+Enter also

=IFERROR(INDEX(D:D,MATCH($F$5&$G5,$B:$B&$C:$C,0)),"")

Thanks

 

[Somendra Misra]

@salil nagpal

Please have a look of uploaded file. I had use two formulas two get exp code & net amount.

To get exp code use :

=IF(ROWS(G$5:G5)<=COUNTIF($B$4:$B$21,F$5),INDEX($C$4:$C$21,SMALL(IF($B$4:$B$21=F$5,ROW($B$4:$B$21)-ROW($B$4)+1),ROWS(G$5:G5))),"")

As this is an Array formula enter with Ctrl+Shift+Enter.

To get net amount use:

=SUMPRODUCT((--($B$4:$B$21=F$5))*(--($C$4:$C$21=G5))*($D$4:$D$21))

Regards!