Results 1 to 4 of 4

Thread: need help with #N/A removal

  1. #1

    need help with #N/A removal



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

    I am running into some "#N/A" issues. It could be a formatting issue but I need some help.

    I have a tab called raw. The "raw ordered" tab reorders the "raw" tab by pulling data from it using INDEX and MATCH function. It works nicely overall. But scroll over to columns AC and on, and it does not pull in the data from "raw" tab even though the data is there. It displays the "#N/A". That is my first issue. Can someone see why it is doing this? I checked for spaces in the IDs that the formula is pulling from and they are in identical format.

    In the "tables" tab, it pulls from "raw ordered" tab. But the "S10 1:1000" displays "#N/A", even though in "raw ordered" tab, the "S10 1:1000" contains data. This is my second issue. How to fix this?
    Attached Files Attached Files

  2. #2
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,317
    Articles
    0
    Excel Version
    2010 on Xubuntu
    I think there is an error in your formula in col AE it should prbably be =INDEX(raw!$E:$E,MATCH(TRIM($AC2),raw!$C:$C,0),1)

    your formula is =INDEX(raw!$E:$E,MATCH(TRIM($AD2),raw!$C:$C,0),1)

  3. #3
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,317
    Articles
    0
    Excel Version
    2010 on Xubuntu
    In the "tables" sheet you use formulas where teh MATCH function with a match type of 3. Xl only allows -1,0 and +1

    Instead of =INDEX('raw ordered'!K:K,MATCH(TRIM(B19),'raw ordered'!I:I,3),1) try
    Code:
    =INDEX('raw ordered'!K:K,MATCH(TRIM(B19),'raw ordered'!I:I,0)+2,1)
    which supposes that there are no more then 3 identical consecutive values for the well ID

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Your OK using the index route in the tables sheet examples, as there are 3 instances for each Well ID in every case. This means that the offset (+2) together with changing the match type from 3 to 0 (as suggested by Pecoflyer) will return the correct result. However if there were situations where there were anything other than 3, then a different offset would be needed making the formulae time consuming to construct.
    If this were an issue, I would consider:
    (1) Placing the average calculation against the 1st instance, instead of last
    or
    (2) Using the SUMPRODUCT function, which handles multiple record matches better than INDEX/MATCH.

    HTH

Posting Permissions

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