+ Reply to Thread
Results 1 to 2 of 2

Can Someone Help me With a Nested VLOOKUP

  1. #1
    lee.dykeman@monro.com
    Guest

    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),VLOOKUP(B3,Sheet2!B2:J570,1,FALSE))),"",C1:C570)

    Thanks,
    Lee


  2. #2
    Max
    Guest

    Re: 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$100=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
    --
    <lee.dykeman@monro.com> wrote in message
    news:1134825956.223225.17090@f14g2000cwb.googlegroups.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),VLOOKUP(B3,Sheet2!B2:J570,1,FALS
    E))),"",C1:C570)
    >
    > Thanks,
    > Lee
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1