+ Reply to Thread
Results 1 to 7 of 7

Index match formula problem

  1. #1
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    10

    Post Index match formula problem

    Hi there, I struggle with my index/match formula - it only works for the first row.

    I'm trying to auto-populate the values from column H ('BOM' sheet) into the column E ('Options' sheet) as long as there is a match in BOM Codes (please refer to the attached spreadsheet). I developed this formula:

    =INDEX(BOM!H2:H2000,MATCH(B9,BOM!B2:B2000,0))

    where BOM!H2:H2000 refers to the value source column, B9 to the specific code, BOM!B2:B2000 to the code list column.

    In the 'Options' sheet, I would expect values 16, 1 & 1 in cells E9, E17 & E18. However, I only get a proper value in E9, even though E17 & E18 do not return #N/A value.

    Do you have a clue what causes this problem?

    Many thanks,
    Kapi
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    10-05-2020
    Location
    St.Lucia
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Index match formula problem

    The problem is with your data set. you cant have any blanks in the data. thats why u only getting 16 back. The other values , a couple 1's i think have a blank cell before them that basically cancels your index and match.

  3. #3
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    10

    Re: Index match formula problem

    Hi, thanks for your reply. Unfortunately, putting data in those blank cells didn't help. Thanks for trying, tho.

  4. #4
    Registered User
    Join Date
    10-05-2020
    Location
    St.Lucia
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Index match formula problem

    ok it worked for me. but yea there were more issues than I highlighted.

    your formula should be

    =INDEX(BOM!$H$2:$H$2000,MATCH(BOM!B3,BOM!$B$2:$B$2000,0))

    and your data set along with fulling in cells B8,B16 , G8, G16, with data , you also need your code # column. the numbers should be sequential. (100..101...102..etc

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,615

    Re: Index match formula problem

    INDEX/MATCH looks for the very 1st of the criteria - then it stops looking.

    In your case, the 1st match for 110 is in H9, which is empty, so the formula returns "nothing"
    The same is happening for 111

    To test this, put something (anything) in H9 and H17 - you will see that gets pulled through.

    I know this doesnt solve your question, but it explains what is happening, so that will maybe give you some ideas on how to fix it.

    hmm looking again, it appears that you are looking for a match of BOM and 70l PN? Maybe a combo of those 2 will give you what you want?
    =INDEX(BOM!$H$3:$H$23,MATCH(Options!B9&"70l",INDEX(BOM!$B$3:$B$23&BOM!$C$3:$C$23,0),0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    10-27-2020
    Location
    Australia
    MS-Off Ver
    MS 2019
    Posts
    10

    Re: Index match formula problem

    Thank you so much for pointing this out, Ford

    I reckon I will be able to get it fixed now.

    Best regards,
    Kapi

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,615

    Re: Index match formula problem

    Happy to help and thanks for the feedback

+ 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. Problem with the Index and Match formula
    By Eric Tsang in forum Excel General
    Replies: 1
    Last Post: 07-14-2019, 01:41 AM
  2. Index Match Formula with problem
    By geliedee in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-20-2016, 03:52 AM
  3. Problem with index and match formula
    By i82 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2016, 03:04 AM
  4. [SOLVED] Index match formula problem
    By nellyc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2013, 06:37 AM
  5. Problem with Index and Match formula
    By annupojupradeep in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-03-2013, 08:01 PM
  6. Problem with Index and Match Formula
    By rbpd5015 in forum Excel General
    Replies: 1
    Last Post: 12-01-2011, 10:38 AM
  7. I'm having a problem with a Index/Match formula
    By PaulEast in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-25-2011, 07:38 AM

Tags for this Thread

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