+ Reply to Thread
Results 1 to 13 of 13

If contains formula with index match formula produces errors

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2010
    Posts
    29

    If contains formula with index match formula produces errors

    Thanks in advance for your time and help!
    Dear Excel Gurus'

    I have a master list of inventory items (ITEM MASTER EXTRACT) that comprises of item numbers and item descriptions. I also have a 2nd list of items (PURCH_HIST_ALBLIVSAL_EXP_072213) that i only have item descriptions for. I am trying to create a formula that will take the item description from the 2nd list of items and search all of the item descriptions in the 1st list and if it contains any of the same description, then return the corresponding item number. I have created the formula below. I also have attached a sample. Please help me figure out what I am missing?? I keep getting only "NOT FOUND".

    =IFERROR(CONTAINS(I90&"*",ITEM_MASTER_EXTRACT_072213!B:B,1)*INDEX(ITEM_MASTER_EXTRACT_072213!A:A,MATCH(PURCH_HIST_ALBLIVSAL_EXP_072213!A:A&"*",ITEM_MASTER_EXTRACT_072213!B:B,0),ITEM_MASTER_EXTRACT_072213!A92),"NOT FOUND")
    Attached Files Attached Files

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If contains formula with index match formula produces errors

    i dont see how the attached is the same as your description and what is contains()
    Last edited by martindwilson; 07-28-2013 at 06:44 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    07-25-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: If contains formula with index match formula produces errors

    I have attached a more detailed file. My actual file is very large and I have had to take a smaller sample. Hopefully this will make more sense. You will see that I have tried 3 different formulas in the 1st tab, column labeled "Item".

    thanks in advance!
    Attached Files Attached Files

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If contains formula with index match formula produces errors

    where is one that should work? ie one you think should match with another

  5. #5
    Registered User
    Join Date
    07-25-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: If contains formula with index match formula produces errors

    Nicole Matthews sample 2.xls above

    thanks!!

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If contains formula with index match formula produces errors

    well i cant see indicated one that should work what row is one on that should match and where is that on the sheet to be matched against
    eg something like row 25 of first sheet should match with row 70 of second sheet i cant even see exactly what your search value is

  7. #7
    Registered User
    Join Date
    07-25-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: If contains formula with index match formula produces errors

    Oh okay, thanks for clarifying. Apology that I was not specific.
    For example, on the 1st tab - "Purch_Hist..etc" Row 92, Col.B should yield one of the following results from the 2nd tab- "Item Master Extract..." row 1893, row 1894 or row 1910 in Column B.

    Essentially what I am trying to do is see if these items from 1st tab already exist anywhere in our master inventory so I will not have to recreate it.

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If contains formula with index match formula produces errors

    there are so many variations this will find some but im tired its 2:32 am !
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-25-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: If contains formula with index match formula produces errors

    thank you so much! I will review this to see what i missed and hopefully follow-up with you tomorrow on my other options for the rest!! Get some rest!! Humbly, I thank you!!!

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If contains formula with index match formula produces errors

    ah a nights sleep
    ok this one (a bit formula heavy) searches from 5 to 24 leading characters and returns the longest match made
    its still pretty much of a best guess scenario though
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    07-25-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: If contains formula with index match formula produces errors

    Thanks so much! Yeah, I know the there was alot of inconsistencies in the data, which I knew would be hard to find; however I knew the right combination of formulas would yield a majority if they have any close matches. Sample 4 and Sample 5 in some instances pulled different information, so I will compare those two and take all values in Sample 5 that returned and use those values if Sample 4 did not return a value for the same item in the Item field.

  12. #12
    Registered User
    Join Date
    07-25-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: If contains formula with index match formula produces errors

    Hey martindwilson ...also Formula Guru...

    I was going though the file and I just caught something. Looking at sample 4, why isn't line 227 on "PURCH_HIST_ALBLIVSAL_EXP_072213" not returning line 1649 or 1650 on "ITEM_MASTER_EXTRACT_072213"?

    If I understand the formula correctly, it is taking the left 8 characters from the lookup value and returning matches from "ITEM_MASTER_EXTRACT_072213"???

  13. #13
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: If contains formula with index match formula produces errors

    thats why 5 is better!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Index Match Formula doesn't recognize output from Right Formula as lookup value.
    By GoGators in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-10-2015, 11:33 AM
  2. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  3. [SOLVED] Formula whit INDEX and MATCH. I try to make this formula to result.
    By Anka in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-28-2012, 06:26 PM
  4. Nested index formula - need to ignore errors
    By Fursmanm in forum Excel General
    Replies: 0
    Last Post: 09-20-2012, 09:50 AM
  5. Replies: 5
    Last Post: 02-29-2012, 08:51 PM

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