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 got it to install, but it does not seem to function. It shows up in my excel, but does not allow me to select a range of cells.

with and without cellsselected, the mini sheet and tabel only do not let me select them.

 

[AhoyNC]

Maybe this will help (you may be able to use something like the MID function to get the data after the x. I didn't take the time to change the formula to match both [x] with and without the spaces. You can probably do that with the IFERROR or IFNA function as in post 3 above.
You will have to change ranges to match your data. You will also need to change the search for each category (1J, 1K, 2A..etc.)
I also put in a box showing the breakdown of the formula.

Book1
	A	B	C	D	E
1
2	Height # for OFFSET function
3	2	1J Building Type	[_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME
4	2	1K Property Leasehold	[ ] Yes [x] No
5	4	2A Appraisal Type
6	4	2B Loan Use	[x] ENERGY EFFICIENCY MORTGAGE (EEM)
7	4	2C Building Status
8
9			Formula breakdown
10			MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0)	12	Returns row # for 1J
11			ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0),1)	$A$12	Returns address
12	1J. BUILDING TYPE		OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0),1,4)),1,0,2)	[_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME	Returns rows under 1K Building Type
13	[_] SINGLE FAMILY RESIDENCE [x] MANUFACTURED HOME			[_] CONDO [_] MULTI-FAMILY RESIDENCE
14	[_] CONDO [_] MULTI-FAMILY RESIDENCE		ISNUMBER(SEARCH("[x]",OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0),1,4)),1,0,2)))	TRUE	Returns TRUE for row with [x]
15	2B Loan Use			FALSE
16	[_] RENOVATION (Alterations/Repairs)
17	[_] CONSTRUCTION
18	[x] ENERGY EFFICIENCY MORTGAGE (EEM)
19	[ ] OTHER
20	1K Property Leasehold
21	[ ] Yes [x] No
22
23
Sheet2

Cell Formulas
Range	Formula
C3	C3	=INDEX(OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0),1,4)),1,0,$A3),MATCH(TRUE,ISNUMBER(SEARCH("[x]",OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0),1,4)),1,0,$A3))),0))
C4	C4	=INDEX(OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1K",A2:A26)),0),1,4)),1,0,$A4),MATCH(TRUE,ISNUMBER(SEARCH("[x]",OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1K",A2:A26)),0),1,4)),1,0,$A4))),0))
C6	C6	=INDEX(OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("2B",A1:A25)),0),1,4)),1,0,$A6),MATCH(TRUE,ISNUMBER(SEARCH("[x]",OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("2B",A1:A25)),0),1,4)),1,0,$A6))),0))
D10	D10	=MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0)
D11	D11	=ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0),1)
D12:D13	D12	=OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0),1,4)),1,0,2)
D14:D15	D14	=ISNUMBER(SEARCH("[x]",OFFSET(INDIRECT(ADDRESS(MATCH(TRUE,ISNUMBER(SEARCH("1J",A1:A25)),0),1,4)),1,0,2)))
Dynamic array formulas.

 

[Peter_SSs]

I got it to install, but it does not seem to function. It shows up in my excel, but does not allow me to select a range of cells.

Look at the link that I gave you in post #10