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 na...

excel

Formulas and Functions

gms4b

Brass Contributor

Mar 29, 2019

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

Diamond Contributor

Mar 29, 2019

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

Forum Discussion

Search row for value with * and return that value?

Resources