software

I have the following Data

Sheet 1 Sheet
2
Column A Column B Column C Column A
Column B Column C
556859 BF144256 456879
BF556982 $55.22
456879 BF556982 556859
BF144256 $68.23
456879 BF224896 456879
BF224896 $25.32
456879 BF364896 456879
BF364896 $35.45I have a woorkbook with 2 tabs that are essentially, Column A in each
tab contains Invoice #, column B in each tab contains part #, and
Column C in sheet/tab 2 contains Avg Cost. I need a formula in column
C of sheet 1 that will return the Average cost that is associated with
a particular Invoice# and Part #. So I need a formula that will say
that.....If Column a and Column B of sheet 1 equals column A and column
B of sheet 2 return Column C on sheet 2 for the line that matches. A
vlookup wont work because there are invoices that have multiple part
numbers on them that is why i need a formula that will compare both
Invocie and part number for matches.

Here is what I have so far, it comes up N/A not sure if I am close or
way off base let me know if you can help
=IF((AND(VLOOKUP(A3,Sheet1!A2:C570,1,FALSE),VLOOKU P(B3,Sheet2!B2:J570,1,FALSE))),quot;quot;,C1:C570)

Thanks,
LeeAssuming data starts in row1 down in both Sheets 1 and 2

In Sheet1,

Put in C1, then array-enter the formula
i.e. press CTRL SHIFT ENTER
(instead of just pressing ENTER):

=INDEX(Sheet2!$C$1:$C$100,
MATCH(1,(Sheet2!$A$1:$A$100=A1)*(Sheet2!$B$1:$B$10 0=B1),0))

Copy C1 down

Adapt to suit (eg. the ranges in Sheet2)
--
Rgds
Max
xl 97
---
Singapore, GMT 8
xdemechanik
savefile.com/projects/236895
--
gt; wrote in message ups.com...
gt; I have the following Data
gt;
gt; Sheet 1 Sheet
gt; 2
gt; Column A Column B Column C Column A
gt; Column B Column C
gt; 556859 BF144256 456879
gt; BF556982 $55.22
gt; 456879 BF556982 556859
gt; BF144256 $68.23
gt; 456879 BF224896 456879
gt; BF224896 $25.32
gt; 456879 BF364896 456879
gt; BF364896 $35.45
gt;
gt;
gt; I have a woorkbook with 2 tabs that are essentially, Column A in each
gt; tab contains Invoice #, column B in each tab contains part #, and
gt; Column C in sheet/tab 2 contains Avg Cost. I need a formula in column
gt; C of sheet 1 that will return the Average cost that is associated with
gt; a particular Invoice# and Part #. So I need a formula that will say
gt; that.....If Column a and Column B of sheet 1 equals column A and column
gt; B of sheet 2 return Column C on sheet 2 for the line that matches. A
gt; vlookup wont work because there are invoices that have multiple part
gt; numbers on them that is why i need a formula that will compare both
gt; Invocie and part number for matches.
gt;
gt; Here is what I have so far, it comes up N/A not sure if I am close or
gt; way off base let me know if you can help
gt;
=IF((AND(VLOOKUP(A3,Sheet1!A2:C570,1,FALSE),VLOOKU P(B3,Sheet2!B2:J570,1,FALS
E))),quot;quot;,C1:C570)
gt;
gt; Thanks,
gt; Lee
gt;