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
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
Thank you, Riny,
I did not know you could use SUMIFS for this type of query, you never stop learning.
Regards
Paul
