Forum Discussion

gms4b's avatar
gms4b
Brass Contributor
Mar 29, 2019

Search row for value with * and return that value?

I feel like this should be easy, but I can't figure it out. I cut/paste alot of data into this spreadsheet of mine, but sometimes the column name changes as well as where the column is. The column name changes as such: 

Calculated Concentration (ug/g) or

Calculated Concentration (ng/g) or

Calculated Concentration (g/L) etc

...and this name could be in any column from A to Z. (though it is always in row 1)

 

So, I need a formula to search row 1 (columns A to Z) for a cell that has any instance of "Calculated Concentration" and then return the exact term that it finds (i.e. Calculated Concentration (ug/g)).

 

Any ideas?

 

Thanks,


Greg

 

 

5 Replies

  • gms4b 

    There are so many possibilities!

    Some start with searching each heading of a table for a sub-string, for example "Peak Area" or "Concentration"

    = SEARCH( "Concentration", Table1[#Headers] )

    That returns an array of numbers and #VALUE! errors for matches and non-matches respectively.

    From there

    = LOOKUP( 1, SIGN( SEARCH( "Concentration", Table1[#Headers] ) ), Table1[#Headers] )

    would return the matched heading (or the final match if there are more than one).

     

    In future another function that would return one or more matches is FILTER, 

    = FILTER( Table1[#Headers], ISNUMBER( SEARCH( "Concentration", Table1[#Headers] ) ) )

    A further trick that FILTER offers is to return the entire data column associated with the matched heading

    = FILTER( Table1, ISNUMBER( SEARCH( "Concentration", Table1[#Headers] ) ) )

     

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    gms4b , it could be few variants, for example

    =INDEX($A$1:$Z$1,SUMPRODUCT((LEFT($A$1:$Z$1,24)="Calculated Concentration")*COLUMN($A$1:$Z$1)))

     

    • gms4b's avatar
      gms4b
      Brass Contributor

      SergeiBaklan 

       

      So, In one case it works and in another it doesn't. For the "Calculated Concentration (xx/xx)" example that I gave it works fine. However, a similarly named column "Analyte Concentration (xx/xx)" it fails - i just get #VALUE!. If I remove the parentheses from the term then it works well. Below I listed all the column headers that are in this sample set. Is it possible the all of the parentheses in the column headers are causing problems?

       

      Sample Name

      Sample ID

      Sample Type

      File Name

      Dilution Factor

      Analyte Peak Area (counts)

      Analyte Peak Height (cps)

      Analyte Concentration (ng/mL)

      Standard Query Status IS Peak Area (counts)

      IS Peak Height (cps)

      Use Record

      Record Modified

      Calculated Concentration (ng/mL)

      Accuracy (%)

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor

        gms4b , yes, length is different. Formula could be made bit more flexible if, for example, use it as

        =INDEX($A$1:$Z$1,SUMPRODUCT((LEFT($A$1:$Z$1,LEN("Calculated Concentration"))="Calculated Concentration")*COLUMN($A$1:$Z$1)))

        and even more better if text like "Calculated Concentration" is in some cell not to hardcode it within the formula

         

Resources