Home

Search row for value with * and return that value?

%3CLINGO-SUB%20id%3D%22lingo-sub-390818%22%20slang%3D%22en-US%22%3ESearch%20row%20for%20value%20with%20*%20and%20return%20that%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390818%22%20slang%3D%22en-US%22%3E%3CP%3EI%20feel%20like%20this%20should%20be%20easy%2C%20but%20I%20can't%20figure%20it%20out.%20I%20cut%2Fpaste%20alot%20of%20data%20into%20this%20spreadsheet%20of%20mine%2C%20but%20sometimes%20the%20column%20name%20changes%20as%20well%20as%20where%20the%20column%20is.%20The%20column%20name%20changes%20as%20such%3A%26nbsp%3B%3C%2FP%3E%3CP%3ECalculated%20Concentration%20(ug%2Fg)%20or%3C%2FP%3E%3CP%3ECalculated%20Concentration%20(ng%2Fg)%20or%3C%2FP%3E%3CP%3ECalculated%20Concentration%20(g%2FL)%20etc%3C%2FP%3E%3CP%3E...and%20this%20name%20could%20be%20in%20any%20column%20from%20A%20to%20Z.%20(though%20it%20is%20always%20in%20row%201)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20I%20need%20a%20formula%20to%20search%20row%201%20(columns%20A%20to%20Z)%20for%20a%20cell%20that%20has%20any%20instance%20of%20%22Calculated%20Concentration%22%20and%20then%20return%20the%20exact%20term%20that%20it%20finds%20(i.e.%20Calculated%20Concentration%20(ug%2Fg)).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EGreg%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-390818%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-391080%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20row%20for%20value%20with%20*%20and%20return%20that%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-391080%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20so%20many%20possibilities!%3C%2FP%3E%3CP%3ESome%20start%20with%20searching%20each%20heading%20of%20a%20table%20for%20a%20sub-string%2C%20for%20example%20%22%3CSPAN%3EPeak%20Area%3C%2FSPAN%3E%22%20or%20%22Concentration%22%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20SEARCH(%20%22Concentration%22%2C%20Table1%5B%23Headers%5D%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThat%20returns%20an%20array%20of%3CSPAN%3Enumbers%20%3CSPAN%3Eand%3C%2FSPAN%3E%3C%2FSPAN%3E%23VALUE!%20errors%20for%20matches%20and%20non-matches%20respectively.%3C%2FP%3E%3CP%3EFrom%20there%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20LOOKUP(%201%2C%20SIGN(%20SEARCH(%20%22Concentration%22%2C%20Table1%5B%23Headers%5D%20)%20)%2C%20Table1%5B%23Headers%5D%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ewould%20return%20the%20matched%20heading%20(or%20the%20final%20match%20if%20there%20are%20more%20than%20one).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20future%20another%20function%20that%20would%20return%20one%20or%20more%20matches%20%3CSPAN%3Eis%20FILTER%2C%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E%3D%20FILTER(%20Table1%5B%23Headers%5D%2C%20ISNUMBER(%20SEARCH(%20%22Concentration%22%2C%20Table1%5B%23Headers%5D%20)%20)%20)%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EA%20further%20trick%20that%20FILTER%20offers%20is%20to%20return%20the%20entire%20data%20column%20associated%20with%20the%20matched%20heading%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CSPAN%3E%3D%20FILTER(%20Table1%2C%20ISNUMBER(%20SEARCH(%20%22Concentration%22%2C%20Table1%5B%23Headers%5D%20)%20)%20)%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390894%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20row%20for%20value%20with%20*%20and%20return%20that%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20length%20is%20different.%20Formula%20could%20be%20made%20bit%20more%20flexible%20if%2C%20for%20example%2C%20use%20it%20as%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24A%241%3A%24Z%241%2CSUMPRODUCT((LEFT(%24A%241%3A%24Z%241%2CLEN(%22Calculated%20Concentration%22))%3D%22Calculated%20Concentration%22)*COLUMN(%24A%241%3A%24Z%241)))%3C%2FPRE%3E%0A%3CP%3Eand%20even%20more%20better%20if%20text%20like%20%22Calculated%20Concentration%22%20is%20in%20some%20cell%20not%20to%20hardcode%20it%20within%20the%20formula%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390890%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20row%20for%20value%20with%20*%20and%20return%20that%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390890%22%20slang%3D%22en-US%22%3E%3CP%3EAh%2C%20I%20think%20I%20got%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E21%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_winking-face%22%20title%3D%22%3Awinking_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGreg%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390876%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20row%20for%20value%20with%20*%20and%20return%20that%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390876%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20In%20one%20case%20it%20works%20and%20in%20another%20it%20doesn't.%20For%20the%20%22Calculated%20Concentration%20(xx%2Fxx)%22%20example%20that%20I%20gave%20it%20works%20fine.%20However%2C%20a%20similarly%20named%20column%20%22Analyte%20Concentration%20(xx%2Fxx)%22%20it%20fails%20-%20i%20just%20get%20%23VALUE!.%20If%20I%20remove%20the%20parentheses%20from%20the%20term%20then%20it%20works%20well.%20Below%20I%20listed%20all%20the%20column%20headers%20that%20are%20in%20this%20sample%20set.%20Is%20it%20possible%20the%20all%20of%20the%20parentheses%20in%20the%20column%20headers%20are%20causing%20problems%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESample%20Name%3C%2FP%3E%3CP%3ESample%20ID%3C%2FP%3E%3CP%3ESample%20Type%3C%2FP%3E%3CP%3EFile%20Name%3C%2FP%3E%3CP%3EDilution%20Factor%3C%2FP%3E%3CP%3EAnalyte%20Peak%20Area%20(counts)%3C%2FP%3E%3CP%3EAnalyte%20Peak%20Height%20(cps)%3C%2FP%3E%3CP%3EAnalyte%20Concentration%20(ng%2FmL)%3C%2FP%3E%3CP%3EStandard%20Query%20Status%20IS%20Peak%20Area%20(counts)%3C%2FP%3E%3CP%3EIS%20Peak%20Height%20(cps)%3C%2FP%3E%3CP%3EUse%20Record%3C%2FP%3E%3CP%3ERecord%20Modified%3C%2FP%3E%3CP%3ECalculated%20Concentration%20(ng%2FmL)%3C%2FP%3E%3CP%3EAccuracy%20(%25)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-390827%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20row%20for%20value%20with%20*%20and%20return%20that%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-390827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F301568%22%20target%3D%22_blank%22%3E%40gms4b%3C%2FA%3E%26nbsp%3B%2C%20it%20could%20be%20few%20variants%2C%20for%20example%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24A%241%3A%24Z%241%2CSUMPRODUCT((LEFT(%24A%241%3A%24Z%241%2C24)%3D%22Calculated%20Concentration%22)*COLUMN(%24A%241%3A%24Z%241)))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
gms4b
Contributor

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
Highlighted

@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)))

 

Highlighted

@Sergei Baklan 

 

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 (%)

Highlighted

Ah, I think I got it. 

 

21

 

;-)

 

Greg

Highlighted

@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

 

Highlighted

@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] ) ) )