need help with #N/A removal

ajcrew

New member
Joined
Jul 25, 2013
Messages
1
Reaction score
0
Points
0
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?
 

Attachments

  • example_2.xlsx
    64.2 KB · Views: 20

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,769
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
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)
 

Pecoflyer

Admin Alumnus
Joined
Oct 13, 2011
Messages
1,769
Reaction score
0
Points
36
Location
Brussels Belgium
Excel Version(s)
2010 on Xubuntu - O365
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)[COLOR=#ff0000][B]+2[/B][/COLOR],1)
which supposes that there are no more then 3 identical consecutive values for the well ID
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
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
 
Top