Unique search

[Stumpped]

I have some imported data that I’d like to search for a partial string within a sentence.
fields that are imported populate cells A1 through A250.
as the data is imported as a colum and not an array, sometimes cell A13 in one import does not match another pasted import.
i‘m looking for a way to search the cell column for “g1.“ In a Cell that contains “g1. Contractor name“.
so if i search for this in a cell and it’s located in cell A13 in one import and cell 15 in another that fits the criteria for the text containing “g1.”
‘now here’s where my issue occurs. I find the cell containing “g1.“ and is cell A11, I need the data in cell A12. Can this be done in one formula in a single cell?

 

[jasonb75]

You could try something like

=INDEX(A2:A251,MATCH("*g1*",A1:A250,0))

Note that the index range is offset from the match range by 1 row.

 

[Stumpped]

OMG, freaking genius, thank you so much. I am eternally grateful for your help. This formula worked perfectly! That saves me so much in the way of needed code, reducing the size of my spreadsheet by a tremendous amount! Did I say thank you?

 

[Stumpped]

I am still a NOOB with excel. I know enough to get by but not enough to be anywhere near dangerous.
I have a couple of follow-up questions. I have fields I need to find from multiple options based on the same formula above.
So in the import I have, some of the imports have data that either says "[_] YES [x] NO" OR "[x] YES [_] NO" when I search for "1J. " With the formula you provided, I know how to get the data for this, but is there something I could add to the formula above to indicate either just YES or NO instead of "[_] YES [x] NO" OR "[x] YES [_] NO"?

The second question is similar but has a different configuration. Rather than having a checked yes or checked no, I would have to search for the next 4 rows.
As with the formula you provided, I would be able to return the field below "2A. Assignment type" which are

Cell A28 "2A. Assignment type"
Cell A29 [_] Field
Cell A30 [_] Lake
Cell A31 [X] Mountian
Cell A32 [_] Stream

With the formula provided above that works with many of the other fields, it is a bit different for this example where I want to select "Mountain" as its box is checked where field, lake, and stream are not.

 

[jasonb75]

To remove the [X] / [_] part, you could simply omit the first part of the result. As it appears to be consistently the first thing in each cell, the easiest way would be

=REPLACE(INDEX(A2:A251,MATCH("*g1*",A1:A250,0)),1,4,"")

For the second part, if you're looking to return only the cell marked with [X] below the searched field then you could try

=REPLACE(INDEX($A$1:$A$250,MATCH("[X]",INDEX($A$1:$A$250,MATCH("*g1*",$A$1:$A$250,0)+1):INDEX($A$1:$A$250,MATCH("*g1*",$A$1:$A$250,0)+10),0)+MATCH("*g1*",$A$1:$A$250,0)),1,4,"")

If you have Excel 2021 or Office 365 then you could make use of LET to shorten it significantly.

=LET(m,MATCH("*g1*",$A$1:$A$250,0),r,$A$1:$A$250,REPLACE(INDEX(r,MATCH("[X]",INDEX(r,m+1):INDEX(r,m+10),0)+m),1,4,""))

I haven't tested either of these in Excel, hopefully I haven't made any errors in the syntax.

 

[Stumpped]

I’ll give it a try tomorrow, been a long day.

 

[Stumpped]

Got busy with work and had no time to work on my project. Slow again and I have another question.
I've tried several different variations of a formula to extract data from the cell below the one with the found text. In this case, there are 2 lines below the found text that have data. I'm trying to modify the search I had above that worked. Hmmmm, I answered my own question, cool. I must admit I am a little impressed by myself.

here's my updated formula that works


=IF(INDEX(A573:A801,MATCH("*1J*",A572:A800,0))="[x] SINGLE FAMILY RESIDENCE [_] MANUFACTURED HOME", "Single Family", IF(INDEX(A573:A801,MATCH("*1J*",A572:A800,0))="[_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME", "Manufactured", IF(INDEX(A574:A802,MATCH("*1J*",A572:A800,0))="[x] CONDO [_] MULTI-FAMILY RESIDENCE", "Condo", IF(INDEX(A574:A802,MATCH("*1J*",A572:A800,0))="[_] CONDO [x] MULTI-FAMILY RESIDENCE", "Multi-Family", ""))))

 

[Stumpped]

Ok, Run into a problem with the formula above. Turns out that there may be spaces in the text I am searching for, so instead of [x], it could be populated with [ x ] instead. Is there a way to substitute the "[x] SINGLE FAMILY RESIDENCE [_] MANUFACTURED HOME" I've tried many formulas and still have not yet quite figured it out. My last formula that has no errors in it is:

=IF(ISNUMBER(SEARCH("x",INDEX(A573:A801,MATCH("2B",A572:A800,0)))), "Renovation", IF(ISNUMBER(SEARCH("x",INDEX(A574:A802,MATCH("2B",A572:A800,0)))), "Construction", IF(ISNUMBER(SEARCH("x",INDEX(A575:A803,MATCH("2B",A572:A800,0)))), "Energy Efficiency", IF(ISNUMBER(SEARCH("x",INDEX(A576:A804,MATCH("2B",A572:A800,0)))), "Other", ""))))

I'm going to have to back track and fix the formula in the post above too because I'm sure it's going to have similar issues with the [x] versus [ x ] in the field.