Mar 29 2019 07:20 AM
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
Mar 29 2019 07:32 AM
@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)))
Mar 29 2019 08:14 AM - edited Mar 29 2019 08:16 AM
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 (%)
Mar 29 2019 08:27 AM
Ah, I think I got it.
21
;)
Greg
Mar 29 2019 08:32 AM
@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
Mar 29 2019 12:42 PM
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] ) ) )