Hello all:

I need your help with the following:

I have a 2 spreadsheets and I want to do a vlookup based on the value in column A of sheet1. If the value in column A of sheet1 is same as column A of Sheet2, I want it to return the value of column B of sheet2.

The problem is: The value in column A of sheet1 is duplicated but the other columns in this worksheet is not duplicated. The value of column A of sheet 2 is not duplicated e.g.

sheet 1

accts $ center
6000 120.00 A
6000 130.00 B
e.t.c.

column A of sheet 2 is not duplicated, sheet 2 looks like this:

accts $
6000 1000

When I lookup the value of sheet 2 into sheet 1 based on accts, I get this:

accts $ center value from Sheet2
6000 120.00 A 1000
6000 130.00 B 1000

This is not correct, the $1000 should be prorated based on the $ in sheet1
i.e.

accts $ center value from Sheet2
6000 120.00 A 120/(120+130)*1000
6000 130.00 B 130/(120+130)*1000

Sometimes the accts in sheet 1 column A is not duplicated, other times it could be duplicated 10 times, but there is just one value in sheet2. The lookup value in sheet2 is not always $1000, there are different values for different accts.

How do I make the macro prorate the value of sheet 2 into sheet1.

Thanks in advance,