Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Greer, South Carolina, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index/Match from multiple sheets (2000)

    I have given up. I cannot find an answer for this problem already posted, so I'm asking at the risk of being redundant. I want to be able to take information from a sheet (call it "Adders") and transfer it to another sheet (call it "Form") and add some pricing information to it to come up with a true price for the material. Of course there is a catch. There is a third sheet (call it "Items"), which is a database query from our POS software. It bridges information from the other two sheets. It should work this way. I type in the item code on Form. The item code matches the item code on Items. There is a field on Items ("Short Description") which matches a field on Adders. The price information is located in the Location 1 field on Adders and needs to be added to the amount in the "Cost" column on Form. The resulting true material price should be in the "Cost +" column on Form. I have tried a couple of index, match lookups, but have had no success. I think my problem is formatting of input and output. But it wouldn't surprise me if that wasn't the problem at all.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Index/Match from multiple sheets (2000)

    Form!H6 is description:
    =if(isblank(A6),"",VLOOKUP($A6,Items!$A$2:$C$18,2, FALSE))
    Form!F6 (Cost+) is:
    =IF(ISBLANK($A6),"",VLOOKUP(VLOOKUP($A6,Items!$A$2 :$C$18,3,FALSE),Adders!$B$2:$D$22,2,FALSE)+E6)

    One thing to note(CRITICAL) is that the "shortdescription" on items are all text and many of the corresponding items in Adders are NUMBERS. The numbers in adders need to be converted to TEXT or the TEXT in ITEMS need to be converted to NUMBERS so that they will match. A text "16" <> a VALUE 16!

    [I think this is really the root of your problem!
    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    Greer, South Carolina, USA
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index/Match from multiple sheets (2000)

    Thanks so much. That was exactly what I was looking to do. I agree the major problem was the text formatting versus the numbers. But, once my brain unfogged enough to remember how to actually change that, the lookup you sent worked like a charm. I'm curious, however. Will it work even if the item list isn't sorted? Thanks again.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Index/Match from multiple sheets (2000)

    Yes, it will work if the list is NOT sorted.

    I set the optional last parameter to FALSE in VLOOKUP, so it MUST find an exact match and the list can be in any order. If it does NOT find an exact match it will give a #N/A error.

    You are welcome,
    Steve

Posting Permissions

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