Complicated formula searching for an excel expert for a good time.

[Stumpped]

I have a formula that works for certain situations

=IF(INDEX(A573:A801,MATCH("*2B*",A572:A800,0))="[x] RENOVATION (Alterations/Repairs)", "Renovation", IF(INDEX(A574:A802,MATCH("*2B*",A572:A800,0))="[x] CONSTRUCTION", "Construction", IF(INDEX(A575:A803,MATCH("*2B*",A572:A800,0))="[x] ENERGY EFFICIENCY MORTGAGE (EEM)", "Energy Efficiency", IF(INDEX(A576:A804,MATCH("*2B*",A572:A800,0))="[x] OTHER", "Other", ""))))


CELL DATA IN CELL
A599 2B. LOAN USE
A600 [_] RENOVATION (Alterations/Repairs)
A601 [_] CONSTRUCTION
A602 [_] ENERGY EFFICIENCY MORTGAGE (EEM)
A603 [x] OTHER


When importing the data, it turns out that sometimes the cell with the "x" in the cell is either populated as [x] or [ x ], so it renders the formula above I have completely useless in determining what the final result should be because of the extra spaces. I have since modified the formula to the following:

=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", ""))))

My formula has no errors or throws up no issues when entered, but it also does not return the desired result of "Other"
I need help with my formula to make it function as intended. I honestly don't know enough about all the commands to fully understand what needs correcting. I am pretty good at modifying formulas to make them work but this one I've come across one I have not been able to solve yet.

Any help would be appreciated. Thanks in advance.

 

[Stumpped]

I would also need help with another monkey in the wrench.
there is a field that's imported that has 2 options in the same cell. so instead of one item per cell it has more. Here is the example


CELL DATA
A 586 1J. BUILDING TYPE
A587 [_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME
A588 [_] CONDO [_] MULTI-FAMILY RESIDENCE

so the formula would have to find the "x" near the item and then populate the result as "Single Family", Manufactured", "Condo", or "Multi-Family".
I'm pretty sure this is an entirely different set of formulas than the one above.

 

[AhoyNC]

You need to let us know what version of Excel you are using. Please update your profile
See if one of these formulas works. The formulas are the same for both of your scenarios.

Book2
	A	B	C
1	[_] RENOVATION (Alterations/Repairs)		With Excel 365
2	[_] CONSTRUCTION		[x] ENERGY EFFICIENCY MORTGAGE (EEM)
3	[x] ENERGY EFFICIENCY MORTGAGE (EEM)
4	[ ] OTHER		Earlier version of Excel
5			[x] ENERGY EFFICIENCY MORTGAGE (EEM)
6
7
8	1J. BUILDING TYPE		With Excel 365
9	[_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME		[_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME
10	[_] CONDO [_] MULTI-FAMILY RESIDENCE		Earlier version of Excel
11			[_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME
Sheet1

Cell Formulas
Range	Formula
C2	C2	=FILTER(A1:A4,(ISNUMBER(SEARCH("[x]",A1:A4))=TRUE)+(ISNUMBER(SEARCH("[ x ]",A1:A4))=TRUE),"No Match")
C5	C5	=IFERROR(IFERROR(INDEX(A1:A4,MATCH(TRUE,ISNUMBER(SEARCH("[x]",A1:A4)),0)),INDEX(A1:A4,MATCH(TRUE,ISNUMBER(SEARCH("[ x ]",A1:A4)),0))),"No Match")
C9	C9	=FILTER(A8:A10,(ISNUMBER(SEARCH("[x]",A8:A10))=TRUE)+(ISNUMBER(SEARCH("[ x ]",A8:A10))=TRUE),"No Match")
C11	C11	=IFERROR(IFERROR(INDEX(A9:A10,MATCH(TRUE,ISNUMBER(SEARCH("[x]",A9:A10)),0)),INDEX(A9:A10,MATCH(TRUE,ISNUMBER(SEARCH("[ x ]",A9:A10)),0))),"No Match")

 

[Stumpped]

Office professional plus 2019

 

[Peter_SSs]

Office professional plus 2019

Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

You could also give us better/easier to work with sample data (& expected results) with XL2BB

 

[Stumpped]

The formula appears to be heading in the right direction, however, the data is not always going to be in the same position. the reason I have the formula I typed in =IF(INDEX(A573:A801,MATCH("*2B*",A572:A800,0)) is that it searches the column from A572 down to A800, then finds the cell directly below it because the data needed always follows the search for "1J" text.

The exports I do can be off by as many as 8 or more cells, that's why I search for the 1J or 1G, or 2B, etc then find the cell directly below it. The problem with the formulas you made is that there are other cells which have "x" in them and if used could populate the incorrect data. I appreciate the thought though.

Here is the brute-forced version of the formula I need.

=IF(INDEX(A573:A801,MATCH("*2B*",A572:A800,0))="[x] RENOVATION (Alterations/Repairs)", "Renovation", IF(INDEX(A573:A801,MATCH("*2B*",A572:A800,0))="[ x ] RENOVATION (Alterations/Repairs)", "Renovation", IF(INDEX(A574:A801,MATCH("*2B*",A572:A800,0))="[x] CONSTRUCTION", "Construction", IF(INDEX(A574:A801,MATCH("*2B*",A572:A800,0))="[ x ] CONSTRUCTION", "Construction", IF(INDEX(A575:A802,MATCH("*2B*",A572:A800,0))="[x] ENERGY EFFICIENCY MORTGAGE (EEM)", "Energy Efficiency", IF(INDEX(A575:A802,MATCH("*2B*",A572:A800,0))="[x] ENERGY EFFICIENCY MORTGAGE (EEM)", "Energy Efficiency", IF(INDEX(A576:A802,MATCH("*2B*",A572:A800,0))="[x] OTHER", "Other", IF(INDEX(A576:A802,MATCH("*2B*",A572:A800,0))="[ x ] OTHER", "Other", ""))))))))

Just looking for a more efficient way to put this in.

 

[AhoyNC]

As Peter_SSs points out, it would help to supply some actual samples and the expected results. Using XL2BB if possible.

 

[Stumpped]

It says it can't be isntalled in protected view? Give me a bit, to see if I can open an unprotected excel sheet.

 

[Stumpped]

This is the best I can do in this situation uploading a screen shot. So the building type cells under 1J BUILDING TYPE have values of
[_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME
[_] CONDO [_] MULTI-FAMILY RESIDENCE cells A18 and A19 respectively

But the check next to the manufactured home may have a space like this [ x ] or have no spaces like this [x].
For instance on line 38 wehre there are extra spaces in the brackets next to EXISTING. It sometimes will not have extra spaces.
So whatever data needed like 1J BUILDING TYPE, the formula I showed looks for one cell below where it finds 1J then pulls the data in the cell which can be checked SINGLE FAMILY or MANUFACTURED HOME. Again, the x's could either be [x] or [ x ].
then the second part of the formula would check the cell below that because BUILDING TYPE has 4 possible options but are located on 2 lines. So the second part of the formula would check CONDO and MULTI FAMILY for x's for either Condo or Multi-Family.
It would be so much easier if they were on 4 lines but that's not the way the data is imported.

The end result is intended to be Manufactured, because the brute forced formula I showed to you looks for either the [x] or the
[ x ] near its indicator either SINGLE FAMILY, MANUFACTURED HOME, CONDO or MULTI FAMILY. As with cell M10

 

[Peter_SSs]

It says it can't be isntalled in protected view?

See if this helps: Xl2bb is disabled

.. and please don't forget:

Please add that to your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Example: