I am trying to calculate commissions for our sales rep. Every month a
report is run that lists each invoice, the total for that invoice and
the sales reps associated with the sale. The problem is there may be
up to four reps per invoice. The report I get looks something like the
grid below...

# Rep1 Rep2 Rep3 Rep4 $Amount
1 Bob Ted Phil 300.00
2 Ted Ralph Ted Mike 500.00
3 Phil Bob Mike Ted 435.00
4 Ralph Bob 198.00

What I need is to come up with a formula that would give me the
following based off the grid above...

Bob Total $Amount
Ted Total $Amount
Phil Total $Amount
Ralph Total $Amount
Mike Total $Amount

I was looking at VLookup but could not make it fit the problem. Any
ideas would be helpful.

As always thanks for any help you can give,
Steve