Log In
Register
Home
July 11, 2017
Offline
Hi,
I need to add a match formula to a VLOOKUP formula.
On attached as example Claudiu Negru works at 2 sites, 67 & 169,
When I change cell A1 to be Site 169 from Site 67 the 'Actual Hours TW' in col O still shows the hours worked for site 67 as that is the first row in the 'Salary Report TW worksheet.
How do I get the correct hours for the site I choose?
Any help gratefully accepted.
Thanks
Paul
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Hi Paul,
VLOOKUP returns a value for the first match it finds. Your formula look only for the name "Claudiu Negru" and the first hit happens to be for Site 67. You need to include the Site reference from A1 also. But that can't be done with VLOOKUP.
With SUMIFS you can do it this way:
=SUMIFS('Salary Report TW'!K2:K321,'Salary Report TW'!D2:D321,J3,'Salary Report TW'!A2:A321,A1)
Alternatively, use SUMPRODUCT:
=SUMPRODUCT(('Salary Report TW'!D2:D321=J3)*('Salary Report TW'!A2:A321=A1)*'Salary Report TW'!K2:K321)
See which one works for you.
Riny
1 Guest(s)
