How to combine Vlookup and Sumproduct

[Shihab]

Hi Dear,

I have a file with two work sheet, in 1 sheet have monthly allowance to staff in 2nd sheet I need the data in a schedule format. Please see the attached file, my formula not working here properly. Kindly suggest me a appropriate formula to solve the issue.

Thanks in advance

[Olly]

Which version of Excel are you actually using? Your profile says 2003, but you've attached a .xlsx format file which suggest 2007 or later...

Would like to know, as a SUMIFS function would work nicely, but wouldn't work if you ARE still using 2003....

Please Login or Register  to view this content.

A pivot table would be another potential solution.

[RobertMika]

IF you will have more than one value(works for one value as well)
=SUMPRODUCT(--($B4=Sheet1!$B$3:$B$3539)*(Sheet1!$C$3:$C$3539=Schedule!F$2)*Sheet1!$D$3:$D$3539)
or
=IF(ISERROR(INDEX(Sheet1!$D$3:$D$3539,MATCH($B4&F$2,Sheet1!$B$3:$B$3539&Sheet1!$C$3:$C$3539,0))),"",INDEX(Sheet1!$D$3:$D$3539,MATCH($B4&F$2,Sheet1!$B$3:$B$3539&Sheet1!$C$3:$C$3539,0)))
Confirm Controil+Shift+Enter

[Vuqar]

Why You don't use just pivot table?ID to row,month to column and allowance(sum of) to value area.

[benishiryo]

hi Shihab. like Olly says, change your profile for the MS-Off Ver accordingly so we can give the better alternatives. to also show the "-", add this to Olly's solution:
=IFERROR(1/(1/SUMIFS(Sheet1!$D:$D,Sheet1!$B:$B,$B4,Sheet1!$C:$C,F$2)),"-")

[Olly]

hi Shihab. like Olly says, change your profile for the MS-Off Ver accordingly so we can give the better alternatives. to also show the "-", add this to Olly's solution:
=IFERROR(1/(1/SUMIFS(Sheet1!$D:$D,Sheet1!$B:$B,$B4,Sheet1!$C:$C,F$2)),"-")

The '-' for zeros is already there, in the numberformat - no need to accommodate it in the formula

[benishiryo]

@Olly:
there are usually more than 1 way in Excel to do something, and mine is just 1 of the many..... nah i just totally forgot about it!!