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

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

 

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

Ah, I think I got it. 

 

21

 

;-)

 

Greg

@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

 

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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies