Forum Discussion
Search row for value with * and return that value?
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)))
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 (%)
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
Ah, I think I got it.
21
;-)
Greg
