INDEX MATCH multiple criteria with true value for blank cell condition

cincao

New Member
Joined
Nov 13, 2017
Messages
8
Hi there,
please someone help me to solve this problem. I am really confused.
I have a reference table like this.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]Category
[/TD]
[TD="align: center"]condition 1
[/TD]
[TD="align: center"]condition 2
[/TD]
[TD="align: center"]condition 3
[/TD]
[TD="align: center"]condition 4
[/TD]
[/TR]
[TR]
[TD="align: center"]A
[/TD]
[TD="align: center"]-150 to 140
[/TD]
[TD="align: center"]0 to 15
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]B
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0 to 50
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]C
[/TD]
[TD="align: center"]-150 to 140
[/TD]
[TD="align: center"]0 to 1000
[/TD]
[TD="align: center"]0 to 50000
[/TD]
[TD="align: center"]4.5 to 14
[/TD]
[/TR]
[TR]
[TD="align: center"]D
[/TD]
[TD="align: center"]-150 to 200
[/TD]
[TD="align: center"]0 to 1.5
[/TD]
[TD="align: center"]0 to 5000
[/TD]
[TD="align: center"]5 to 14
[/TD]
[/TR]
</tbody>[/TABLE]
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.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
are you ok to work with 2 tables like this instead


Excel 2013/2016
ABCDE
1Categorycondition 1condition 2condition 3condition 4
2A-15000
3B
4C-150004.5
5D-150005
6
7
8Categorycondition 1condition 2condition 3condition 4
9A14015
10B50
11C14010005000014
12D2001.5500014
Sheet3
 
Upvote 0
are you ok to work with 2 tables like this instead

Excel 2013/2016
ABCDE
Categorycondition 1condition 2condition 3condition 4
A
B
C
D
Categorycondition 1condition 2condition 3condition 4
A
B
C
D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]-150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]-150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]-150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]140[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]140[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]14[/TD]

</tbody>
Sheet3

It's okay. Do you have any suggestion?
 
Upvote 0
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
ABCDE
1Categorycondition 1condition 2condition 3condition 4
2A-150 to 1400 to 15
3B0 to 50
4C-150 to 1400 to 10000 to 500004.5 to 14
5D-150 to 2000 to 1.50 to 50005 to 14
6
7condition 1180
8condition 21.3
9condition 330
10condition 46
11ResultB
Meet conditions
 
Last edited:
Upvote 0
this is a simple man's approach


Excel 2013/2016
ABCDEF
1Categorycondition 1condition 2condition 3condition 4
2A-15000
3B
4C-150004.5
5D-150005
6
7
8Categorycondition 1condition 2condition 3condition 4
9A14015
10B50
11C14010005000014
12D2001.5500014
13
14Categorycondition 1condition 2condition 3condition 4selection
15AFALSETRUETRUETRUE 
16BTRUETRUETRUETRUEY
17CFALSETRUETRUETRUE
18DTRUETRUETRUETRUEY
19
20Categorycondition 1condition 2condition 3condition 4
21B1801.3306
Sheet3
Cell Formulas
RangeFormula
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))
 
Upvote 0
this is a simple man's approach

Excel 2013/2016
ABCDEF
Categorycondition 1condition 2condition 3condition 4
A
B
C
D
Categorycondition 1condition 2condition 3condition 4
A
B
C
D
Categorycondition 1condition 2condition 3condition 4selection
A
B
C
D
Categorycondition 1condition 2condition 3condition 4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]-150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]-150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]-150[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]140[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]140[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]200[/TD]
[TD="align: right"]1.5[/TD]
[TD="align: right"]5000[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]FALSE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"][/TD]

[TD="align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Y[/TD]

[TD="align: center"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]FALSE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"][/TD]

[TD="align: center"]18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]TRUE[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Y[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #FFF2CC"]B[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]1.3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet3

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B15[/TH]
[TD="align: left"]=AND(OR(ISBLANK(B2),B$21>=B2),OR(ISBLANK(B9),B$21<=B9))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F15[/TH]
[TD="align: left"]=IF(COUNTIF(B15:E15,TRUE)=4,"Y","")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A21[/TH]
[TD="align: left"]=INDEX($A$15:$A$18,MATCH("Y",$F$15:$F$18,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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


ABCDE
Categorycondition 1condition 2condition 3condition 4
A-150 to 1400 to 15

B

0 to 50
C-150 to 1400 to 10000 to 500004.5 to 14
D-150 to 2000 to 1.50 to 50005 to 14





condition 1


condition 2


condition 3


condition 4


Result



<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="align: right"]180[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="align: right"]1.3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="align: right"]30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="align: right"]B[/TD]

</tbody>

Spreadsheet Formulas
CellFormula
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top