vlookup name & return total value from other column

chris9277

Board Regular
Joined
Apr 9, 2009
Messages
153
Hi,

I have a master sheet with names on but I would like this to lookup the exact name which will appear numerous times on various sheets in column A&B & return any total figures that are in column D, this is all in the same workbook.

This is what the formula will be looking at across all sheets

Picked ByRoundTotal UnitsTarget Rate by RoundStartFinishSched Time (mins)Actual Mins
Waleed Munawar A85228 30
B27828 10
Alex Ashton D24128 9
Ethan TyeWaleed MunawarE35928 13
Alex Ashton F105328 38
Ethan TyeLeah CurwenG5428 2
P028 0
T61528 22
GREENS78528 28
<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;" span="2"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;" span="5"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <tbody> </tbody>

Many thanks I advance
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Hi chris9277,

Try something like this --> =SUMPRODUCT((A1:A5=M1)+(B1:B5=M1),D1:D5)

In this case M1 would contain the name being evaluated. You will have to adjust ranges and apply to multiple sheets.

Doug
 

chris9277

Board Regular
Joined
Apr 9, 2009
Messages
153
Hi Doug,

Thank you for your reply that formula has done the trick regards totalling up, but is there anyway that the formula could look through all the sheets in the same workbook?

Many thanks,

Chris
 

duggie33

Board Regular
Joined
Nov 19, 2018
Messages
77
Office Version
  1. 365
Platform
  1. Windows
No problem Chris.

Repeat for other sheets something like this... =SUMPRODUCT((Sheet1!A1:A5=Sheet1!M1)+(Sheet1!B1:B5=Sheet1!M1),Sheet1!D1:D5)+SUMPRODUCT((Sheet2!A1:A5=Sheet1!M1)+(Sheet2!B1:B5=Sheet1!M1),Sheet2!D1:D5).

There is probably a more elegant way to do this that I have not remembered or learned yet. Are the ranges the same on the other sheets?

Doug
 

Watch MrExcel Video

Forum statistics

Threads
1,109,447
Messages
5,528,803
Members
409,837
Latest member
karnasrinivas
Top