Lookup problem

[species8514]

Hi - I have some data that I need to do a lookup/matching process on, but not sure the best way to do it.

Basically column A the user can enter a name, in the B they enter a number, so;

BOB SMITH..........7

in column C the routine is repeated, so column C and D might have;

JAMES PARKER.....3.5

and so on - there are six sets of these name/number combinations

On another sheet I have a list of employees, and what I would like to do is run a lookup on each of the columns to see when/if they match the employees names, and then return the SUM of all the matches - so if BOB SMITH's name appeared three times, and on each occasion the number next to his name was 7, then the forumla would return 21.

I figured using vlookup on each of the columns (for each employee), and then adding these in the forumla (vlookup + vlookup + vlookup, etc.), but it only works if it finds a match - if it doesnt then I get n/a. I am guessing I need to use something like ISERROR? to say that if it cant find a match return a zero, but I'm not sure how quite to do this.

Also, I am unsure if this is indeed the most efficient method to do this, but I cant think of another way.

To give you an example of what I am looking at;

..............a.........................b........................c.......................d

...........................MON............................................TUE

OT...........David Smith..........7 .......................Frank James.......3
...............Simon Noone.........3.......................David Smith........2


On the second sheet I have

a.................................b

Name...........................OT

David Smith
Frank James
Simon Noone


Any help would be appreciated

 

[energman58]

You could use sumif or sumproduct instead to do this in a simple way

 

[species8514]

Will SUMPRODUCT be able to handle cells that dont have any data in them?

So if I run on say A2:B9 and they all have data except row A5?

 

[energman58]

Yes if you use a formula like this:

=SUMPRODUCT(--(A2:A9="SAM SMITH"),(B2:B9))

You can replace "SAM SMITH" with a reference to your cell so you can change it

It generates an array of true/false depending on whether SAM SMITH is in the cell which the "--" part then turns into ones and zeros which are then multiplied by the corresponding values in column B

If there is no data in A you get a FALSE (=0) and zero times whatever is in B is zero

If you put non numbers in B (like "Holiday") it simply evaluates them as zero so it seems bomb-proof in adding up your hours