1. Workbook 1 is a monthly summary of contracts activity. In column A is a listing of each contract number (approx 75-100 each month) that had activity during the month. In column F, is the contract name, and in column H the quantity of widgets purchased.

Workbook 2 is a Master contract listing book. In column C is a listing of all contract names in effect (>2000). What I would like to do in each cell in column N of the Master contract listing book is multiply the respective quantity in Workbook 1 that has the same in both Workbook 1 and Workbook 2, by .75. In other words, if if row 2 in column F in Workbook 1 is "New York", and column H has 90, I would like a formula in column N of Workbook 2 that would multiply the 90 by .75 in the cell in the row where "New York" appears in column C of Workbook 2. Any ideas?

2. You write workbook 1 and 2 - did you mean worksheet?
I assume that row 1 contains column headings, and that the data start in row 2.
In cell N2 in "Workbook 2", enter the following formula:

=IF(ISERROR(VLOOKUP(C2,'Workbook 1'!\$F\$2:\$H\$200,3,FALSE)),"",VLOOKUP(C2,'Workbook 1'!\$F\$2:\$H\$200,3,FALSE)*0.75)

If you really meant workbooks, you need to specify both the workbook and worksheet names:

=IF(ISERROR(VLOOKUP(C2,'[Workbook 1.xls]Sheet1'!\$F\$2:\$H\$200,3,FALSE)),"",VLOOKUP(C2,'[Workbook 1.xls]Sheet1'!\$F\$2:\$H\$200,3,FALSE)*0.75)

If you prefer to return 0 instead of an empty string if there is no match, replace "" with 0 in the formula.

3. Thanks Hans. I meant workbook. One additional fact I failed to mention is that in workbook 1, there could be repeats of contract numbers and names. Therefore I would need a formula that would sum all of the same names to the cell in workbook 2. I'm not sure your formula does this...

Also, I don't see the multiplication by .75

4. The VLOOKUP formula doesn't do that, because you explicitly stated in your original question that you wanted to look up a single value.

You can use a SUMPRODUCT formula:

=SUMPRODUCT(('[Workbook 1.xls]Sheet1'!\$F\$2:\$F\$200=C2)*'[Workbook 1.xls]Sheet1'!\$H\$2:\$H\$200)*0.75

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•