# Vlookup and average

Discussion in 'Windows & Other Software' started by Colbaker, 21 Oct 2016.

1. Colbaker

# Location: Paignton, Devon

Hi all.

I have 3 sheets. In Column a on each sheet is a name, in column B on each sheet is there time each month that they have been working which varies.

On sheet 4 I want to match the name then average the times they have been working over the 3 other sheets.

What formula should I use?

2. Namsnik

# Location: Bucks

I assume that
(A) each name only appears once on each sheet
(B) each name appears on every sheet.
(C) the time is in a suitable numeric/time format
If those assumptions are not right then these solutions will need tweaking.

Option1 Restructure your data and use a pivot table:
This will be the quickest and require least maintenance.
Merge the info in the three sheets by:
Adding a new column containing the sheetname/nonthname or whatever else distinguishes the three sheets. Then copy the data from the sheets one below the other so that you have a single table with all the data.
Then use a pivot table to create an average for each name. (Row field = name, datafield =time ....

Option2 Live with the data in the format you have it.
With the names in column A of sheet4, with titles in row 1
This formula in BB2 will give the average time:
=AVERAGE(VLOOKUP(A2,Sheet1!\$A\$1:\$C\$14,0),VLOOKUP(A2,Sheet2!\$A\$1:\$C\$14,0),.......)

# Posts: 8,101

Do the sheets have a standard naming convention eg: month1, month2....?

If so and its numeric you can use an indirect and column number to pull the data into one sheet and average it.

I used a similar system for recording machine logs, i just named a standard sheet template by the batch number and it was setup to pull the column averages through for each batch. Its a pita to set up but once working you can just add sheets by the naming convention and itll automatically filter through.

The pivot table would be better as a quick dynamic display, or if you can restructure your spreadsheet to keep all the data in one table then its just a case of adding columns

4. BigT

# Location: UK

As long as each name is unique then on sheet 4 if you have:

Column A - Name
Column B - Average

In B2 you can put:

=AVERAGE(VLOOKUP(A1,Sheet1!A:B,2,FALSE),VLOOKUP(A1,Sheet2!A:B,2,FALSE),VLOOKUP(A1,Sheet3!A:B,2,FALSE))