INDEX MATCH multiple criteria with true value for blank cell condition

[cincao]

Hi there,
please someone help me to solve this problem. I am really confused.
I have a reference table like this.

Category	condition 1	condition 2	condition 3	condition 4
A	-150 to 140	0 to 15
B			0 to 50
C	-150 to 140	0 to 1000	0 to 50000	4.5 to 14
D	-150 to 200	0 to 1.5	0 to 5000	5 to 14

<tbody>
</tbody>

I want to check with the multiple condition in range is classified in which category. However for the blank cells, I want to make the true condition whatever the value.
For example,
Condition 1 = 180
Condition 2 = 1.3
Condition 3 = 30
Condition 4 = 6

The result I want is B (the acceptable condition is B and D, but I want to choose the first selection).

Any suggestion for the formula? Really appreciate for your support.

 

[AlanY]

are you ok to work with 2 tables like this instead

Excel 2013/2016
	A	B	C	D	E
1	Category	condition 1	condition 2	condition 3	condition 4
2	A	-150	0	0
3	B
4	C	-150	0	0	4.5
5	D	-150	0	0	5
6
7
8	Category	condition 1	condition 2	condition 3	condition 4
9	A	140	15
10	B			50
11	C	140	1000	50000	14
12	D	200	1.5	5000	14
Sheet3

 

[cincao]

are you ok to work with 2 tables like this instead

Excel 2013/2016

	A	B	C	D	E
1	Category	condition 1	condition 2	condition 3	condition 4
2	A	-150	0	0
3	B
4	C	-150	0	0	4.5
5	D	-150	0	0	5
6
7
8	Category	condition 1	condition 2	condition 3	condition 4
9	A	140	15
10	B			50
11	C	140	1000	50000	14
12	D	200	1.5	5000	14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

It's okay. Do you have any suggestion?

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C	D	E
1	Category	condition 1	condition 2	condition 3	condition 4
2	A	-150 to 140	0 to 15
3	B			0 to 50
4	C	-150 to 140	0 to 1000	0 to 50000	4.5 to 14
5	D	-150 to 200	0 to 1.5	0 to 5000	5 to 14
6
7	condition 1	180
8	condition 2	1.3
9	condition 3	30
10	condition 4	6
11	Result	B
Meet conditions

 

[AlanY]

this is a simple man's approach

Excel 2013/2016
	A	B	C	D	E	F
1	Category	condition 1	condition 2	condition 3	condition 4
2	A	-150	0	0
3	B
4	C	-150	0	0	4.5
5	D	-150	0	0	5
6
7
8	Category	condition 1	condition 2	condition 3	condition 4
9	A	140	15
10	B			50
11	C	140	1000	50000	14
12	D	200	1.5	5000	14
13
14	Category	condition 1	condition 2	condition 3	condition 4	selection
15	A	FALSE	TRUE	TRUE	TRUE
16	B	TRUE	TRUE	TRUE	TRUE	Y
17	C	FALSE	TRUE	TRUE	TRUE
18	D	TRUE	TRUE	TRUE	TRUE	Y
19
20	Category	condition 1	condition 2	condition 3	condition 4
21	B	180	1.3	30	6
Sheet3

Cell Formulas
Range	Formula
B15	=AND(OR(ISBLANK(B2),B$21>=B2),OR(ISBLANK(B9),B$21<=B9))
F15	=IF(COUNTIF(B15:E15,TRUE)=4,"Y","")
A21	=INDEX($A$15:$A$18,MATCH("Y",$F$15:$F$18,0))

 

[cincao]

Thanks Peter_SSs and AlanY

 

[cincao]

this is a simple man's approach

Excel 2013/2016

	A	B	C	D	E	F
1	Category	condition 1	condition 2	condition 3	condition 4
2	A	-150	0	0
3	B
4	C	-150	0	0	4.5
5	D	-150	0	0	5
6
7
8	Category	condition 1	condition 2	condition 3	condition 4
9	A	140	15
10	B			50
11	C	140	1000	50000	14
12	D	200	1.5	5000	14
13
14	Category	condition 1	condition 2	condition 3	condition 4	selection
15	A	FALSE	TRUE	TRUE	TRUE
16	B	TRUE	TRUE	TRUE	TRUE	Y
17	C	FALSE	TRUE	TRUE	TRUE
18	D	TRUE	TRUE	TRUE	TRUE	Y
19
20	Category	condition 1	condition 2	condition 3	condition 4
21	B	180	1.3	30	6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B15 =AND(OR(ISBLANK(B2),B$21>=B2),OR(ISBLANK(B9),B$21<=B9)) F15 =IF(COUNTIF(B15:E15,TRUE)=4,"Y","") A21 =INDEX($A$15:$A$18,MATCH("Y",$F$15:$F$18,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Welcome to the MrExcel board!

See if this does what you want. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Meet conditions

	A	B	C	D	E
1	Category	condition 1	condition 2	condition 3	condition 4
2	A	-150 to 140	0 to 15
3	B			0 to 50
4	C	-150 to 140	0 to 1000	0 to 50000	4.5 to 14
5	D	-150 to 200	0 to 1.5	0 to 5000	5 to 14
6
7	condition 1	180
8	condition 2	1.3
9	condition 3	30
10	condition 4	6
11	Result	B

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:82px;"><col style="width:84px;"><col style="width:82px;"><col style="width:82px;"><col style="width:82px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B11 {=INDEX(A2:A5,AGGREGATE(15,6,(ROW(A2:A5)-ROW(A2)+1)/ ((B7>=IFERROR(LEFT(B2:B5,FIND("",B2:B5))+0,-1E+100))*(B7<=IFERROR(REPLACE(B2:B5,1,FIND("to",B2:B5)+1,"")+0,1E+100)) *(B8>=IFERROR(LEFT(C2:C5,FIND("",C2:C5))+0,-1E+100))*(B8<=IFERROR(REPLACE(C2:C5,1,FIND("to",C2:C5)+1,"")+0,1E+100)) *(B9>=IFERROR(LEFT(D2:D5,FIND("",D2:D5))+0,-1E+100))*(B9<=IFERROR(REPLACE(D2:D5,1,FIND("to",D2:D5)+1,"")+0,1E+100)) *(B10>=IFERROR(LEFT(E2:E5,FIND("",E2:E5))+0,-1E+100))*(B10<=IFERROR(REPLACE(E2:E5,1,FIND("to",E2:E5)+1,"")+0,1E+100))),1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks Peter_SSs and AlanY

 

[Peter_SSs]

Thanks Peter_SSs and AlanY

You are welcome.

BTW, best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly. If you want to quote, quote small, relevant parts only.

 

[cincao]

Okay Peter, noted. Thank you for your kind advise. I apologize for this inconveniences.