Copy cell value based on 2 other criteria. Ive searched and tried for hours

[rubidriver]

Capture.PNGThis is what I am working with (image:capture). As you can see column P may have a number or it may not, IF it has a CPI number in it, i want to give each person half credit for having a CPI by copying the CPI number and pasting it automatcally by their name in another table starting in column Z (pictured below:capture 2)

Capture.PNG

Let me know if this makes sense. I was basically trying
=if(and(isnumber(p74:p78), q74:r78="Shawn".....give him that CPI number

So in the new table (image:capture 2), Shawn should have 53 by his name
kevin should have 54
chad should have 54 also

DISREGARD The middle image i cannot get it to delete PLEASE HELP

[Kaper]

1) try such array formula (array formula is commited not just with Enter but with Ctrl+Shift+Enter):

Please Login or Register  to view this content.

2) formula could be shorter if you have excel 2007+ but in your profile (below your nick) there is 2003 - correct if it is not true. - bottom of: http://www.excelforum.com/profile.php?do=editprofile
3) spend some time reading http://www.excelforum.com/forum-rule...rum-rules.html for instance:

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[rubidriver]

Tried it and it works!! BUTTTTT, I forgot to mention that Shawn (and all the other people) could have multiple CPI numbers and I would like to post multiple CPI numbers in his cell if his name correlates to a CPI number in any way. Your formula is posting only one number, and im guessing it is the max number, how can we get it to post all of the CPI numbers in the Shawn's cell.
Thanks for your quick reply

[Kaper]

HAve you read my whole answer or just tried the formula?

[rubidriver]

I did read what you said, but please understand that Im in a hurry and dont ask a lot of stuff on this site.
I have excel 2010. Sorry I made this more difficult than I should have. Thanks for your help.

[rubidriver]

Unable to post the images any other way

[Kaper]

image<>workbook

[rubidriver]

[rubidriver]

<Capture.PNG>
<Capture2.PNG>

[Kaper]

For me this is end of the topic.
You ignore (may be I am not clear enough - sorry if it was a reason) my requests -> I ignore this thread.

... if you have excel 2007+ ... correct if it is not true. - bottom of: http://www.excelforum.com/profile.php?do=editprofile
...Post a WORKBOOK. Nobody wants to type data from a picture...