Excel VLookup help needed

Penguin_

Hello

I am trying to use VLookUp to group & sum a payroll report, the raw data is a long list of employees then column headers showing Gross Pay, Tax, EE Ni, Er Ni etc going across the top. I have already put each member of staff into a category such as admin, finance, marketing etc on the last column for reference.

My question is how can I use the VLookUp (or anything else) to find all the staff by category & then add each relevant line from each column?

	Gross	EE Ni	Er Ni	PAYE	EE Pen	Er Pen	Net Pay
Admin	X	X	X	X	X	X	X
Admin	X	X	X	X	X	X	X
Marketing	X	X	X	X	X	X	X
Finance	X	X	X	X	X	X	X
Admin	X	X	X	X	X	X	X
Finance	X	X	X	X	X	X	X
Marketing	X	X	X	X	X	X	X

Jenni_D

Sounds like the SUMIF or SUMIFS functions may be better for you.

WaywardDriver

Not altogether clear but seems you have employees in one column then category and the various pay items in adjacent columns. If so then use PivotTable to sum each pay item by category.  

Heedtheadvice

Wayward has it again!

Pivot tables are very powerful and designed for your purpose. Will provide a summary report based upon the categories you have that you choose and will sum up wages (or for that matter find max min, averages, number of and lots more) and chart the results too if you wish.

Not sure what you want from "find staff" but if you want to see which staff nake up a particular category then it allws you to what is called drill down to find all staff that fall into a particular category or sum and copy them to a new sheet..

You can have pivot table that you can select categories on the fly as it were such as just displaying admin, or admin and managers or excluding a category(s) or lots more and do calculation on the results or put the data in chronological order or display on month by month basis (or day or week etc).

Well worth spending a little time to get familiar!

Sandtree

Pivot is a great tool but manipulating data from them into other tables etc is a bit of a pain. Certainly something to learn well irrespective of this task.

SumIf  as Jenni suggests will also work well but you need a list of all the categories else you’ll miss people if a new category is added but you don’t update.

for completeness, vlookup only returns the value in the cell in the row which first matches your criteria and therefore isn’t suitable as you want every one not just the first.

WaywardDriver

Several points from other replies:

• PivotTables are indeed very powerful; for 2-dimensional tables to sum values, they are effectively the equivalent of SUMIFS functions at the intersection of each row and column although I'm sure Excel does this much more efficiently including automatically detecting the unique row and column labels
• as Heedtheadvice says, click on entries in a PivotTable to drill down to see individual staff displayed on a new sheet
• PivotTables do not automatically refresh if the data source changes
• the last parameter in VLOOKUP specifies the column to return e.g. VLOOKUP(cell with value to be looked up, lookup array, 3) returns the value in the 3rd column of the lookup array, so in this case there would be several VLOOKUP functions in adjacent columns with parameter values of 2, 3, 4 etc.

Heedtheadvice

Good info wayward. To add to one point:

PivotTables do not automatically refresh if the data source changes. Very true but they can be set to auto update whenever the workbook is opened. A bit more advanced but they can also be made to update, via a macro,  everytime time a cell entry is changed or recalculation takes place.

Lots of info on the net how to do both quite simply such as https://www.excelcampus.com/vba/refresh-pivot-tables-automatically/