Optimizing FirstOrDefault
The line with from only defines the enumeration, but it doesn't actually run over the list and apply your select clause. In your case, that one is only applied when you call FirstOrDefault.

Therefore, to speed up the call to FirstOrDefault you need to speed up your where condition. I mean this one:

s.Field&lt;string&gt;(Cols.Samplenumber) == sourceRow.Field&lt;string&gt;(Cols.Samplenumber)
      &amp;&amp; s.Field&lt;string&gt;(Cols.ElementName) == elementTest[0].Trim()
      &amp;&amp; s.Field&lt;string&gt;(Cols.AnalysisNumber) == elementTest[1].Trim()


The most expensive parts about this are probably the calls to s.Field&lt;string&gt;(...), followed by the Trim() calls. All of these can be cached. Just convert the maindata into something that's cheap to access, and only do the conversion once, outside of the for loop. Something more or less like this (this won't run as is, only use it as inspiration):

struct TempItem
{
    public string Samplenumber;
    public string ElementName;
    public string AnalysisNumber;
    public string AnalysisStatus;
}
string sourceSampleNumber = sourceRow.Field&lt;string&gt;(Cols.Samplenumber);
var lookUpList = mainData.AsEnumerable().Where(i =&gt; i.Samplenumber == sourceSampleNumber).Select(i=&gt; new TempItem {
        Samplenumber = s.Field&lt;string&gt;(Cols.Samplenumber),
        ElementName = s.Field&lt;string&gt;(Cols.ElementName),
        AnalysisNumber = s.Field&lt;string&gt;(Cols.AnalysisNumber),
        AnalysisStatus = s.Field&lt;string&gt;(Cols.AnalysisStatus)
    }).ToList(); // the ToList() part here forces application of the linq expressions

for (int i = pivotStartIndex; i &lt; sourceTable.Columns.Count; i++)
{
    string[] elementTest = sourceTable.Columns[i].ColumnName.Split('-');
    string elementName = elementTest[0].Trim();
    string analysisNumber = elementTest[1].Trim();
    var mChars = lookUpList.Where(i =&gt; i.ElementName == elementName &amp;&amp; i.AnalysisNumber == analysisNumber).Select(i=&gt;i.AnalysisStatus);
    retList.Add(mChars.FirstOrDefault()); 
}