Results 1 to 8 of 8

Thread: How to avoid #N/A in Index/Match

  1. #1

    Exclamation How to avoid #N/A in Index/Match



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,


    I am trying to pull a list of data from one sheet to another. My formula calculates correctly as I receive my output correctly. However, when I drag my formula down I am given a #N/A for many rows. The reason is because there are blanks in the first worksheet. Is there a way that I can just avoid these blanks and "jump" over them? Meaning that if there is a blank on worksheet 1, it will be completely ignored on worksheet 2.


    =IF('Raw Machining Data'!$A3="","",INDEX('Raw Machining Data'!A3, MATCH('Raw Machining Data'!$F3, 'Raw Machining Data'!$F3,0)))


    I appreciate the help!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    I am not sure what you are trying to do here.

    You are using Index/Match on single cell ranges, and in your MATCH() your lookup value and lookup array parameters are the same.

    Probably a simple IF function would be best here, but I am not sure what logic you are going for.


  3. #3
    Quote Originally Posted by NBVC View Post
    I am not sure what you are trying to do here.

    You are using Index/Match on single cell ranges, and in your MATCH() your lookup value and lookup array parameters are the same.

    Probably a simple IF function would be best here, but I am not sure what logic you are going for.
    Hey NBVC,


    I have two tables - Table 1 and Table 2. Table 1 has a column for suppliers and a comlum for what type of contract these suppliers have. Sometimes these suppliers do not have a contract.


    It is my goal to use this formula to automatically update table 2 when table 1 is updated. Meaning, if a new supplier is entered into Table 1 and it does have a contract, table 2 will automatically populate a new row and show that supplier. However, it has to meet the contract condistion first. If a supplier does not have a contract, than it will not show up on table 2.

  4. #4
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    What is your objective with the information in Table 2? I'm wondering if simply using DATA FILTERS on your data in Table 1 would give you what you want.

    Hope this helps!
    cbug

  5. #5
    Hi Candybg, I appreciate your input. It's very hard to explain what I am doing lol. Simply put, it's a huge financial model that compares a range of different independent variables side by side. These include different types of metals with different suppliers over several foretasted years. The goal is to understand what an increase/decrease in the price of metals over the next several years will do to the company. I think it is going to be an incredibly useful tool that will help members here when I remove any sensitive data and release it here.

    With that being said, your method was clever, but did not work

  6. #6
    Acolyte candybg's Avatar
    Join Date
    Jul 2015
    Posts
    92
    Articles
    0
    Excel Version
    2010 and 2016
    Sorry for not understanding your issue. Can you explain what will happen to the data in Table 2 once it is updated/working properly. Just your next step or 2 so we could maybe help you come up with an alternative to what you are currently trying.

  7. #7
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    perhaps you can try this...

    Assuming your table 1 is in Sheet1, range A1:B20

    where starting from A2 you have a list of companies, and in column B you have an identifier, e.g "Y", to flag the suppliers that are on contract.

    In Sheet2, A2 enter this formula:

    =IFERROR(INDEX(Sheet1!$A$2:$A$20,SMALL(IF(Sheet1!$B$2:$B$20<>"",ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1),ROWS(Sheet1!$A$2:$A2))),"")

    confirm it with CTRL+SHIFT+ENTER, not just ENTER, then copy it down. It should bring back only the supplier that were flagged in Sheet1, column B. Adjust your ranges to suit and reconfirm with CSE before copying down.


  8. #8
    Quote Originally Posted by NBVC View Post
    perhaps you can try this...

    Assuming your table 1 is in Sheet1, range A1:B20

    where starting from A2 you have a list of companies, and in column B you have an identifier, e.g "Y", to flag the suppliers that are on contract.

    In Sheet2, A2 enter this formula:

    =IFERROR(INDEX(Sheet1!$A$2:$A$20,SMALL(IF(Sheet1!$B$2:$B$20<>"",ROW(Sheet1!$A$2:$A$20)-ROW(Sheet1!$A$2)+1),ROWS(Sheet1!$A$2:$A2))),"")

    confirm it with CTRL+SHIFT+ENTER, not just ENTER, then copy it down. It should bring back only the supplier that were flagged in Sheet1, column B. Adjust your ranges to suit and reconfirm with CSE before copying down.
    Hi NBVC, your formula is absolutely brilliant! It worked great. Thank you very much.

    P.S. I apologize for taking so long to respond, I did not realize this thread had been responded to.
    Last edited by AlphaJoe; 2015-07-23 at 04:39 PM.

Posting Permissions

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