Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can Someone Help me With a Nested VLOOKUP
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.45 I 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))),"",C1:C570) Thanks, Lee |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can Someone Help me With a Nested VLOOKUP
Assuming 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 http://savefile.com/projects/236895 -- wrote in message ups.com... 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.45 I 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,FALS E))),"",C1:C570) Thanks, Lee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested IF Function and VLookup Alternatives | Excel Worksheet Functions | |||
Retry: VLOOKUP nested in IF Statement | Excel Worksheet Functions | |||
Nested Vlookup or alternative? | Excel Worksheet Functions | |||
Nested vlookup | Excel Discussion (Misc queries) | |||
Nested vlookup? | Excel Worksheet Functions |