need help with #N/A removal

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?

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)

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 which supposes that there are no more then 3 identical consecutive values for the well ID

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