INDEX MATCH multiple criteria with true value for blank cell condition

C

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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
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
AlanY said:
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​
Click to expand...

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
AlanY said:
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]
Click to expand...

Peter_SSs said:
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
Click to expand...

Thanks Peter_SSs and AlanY
 
Upvote 0
cincao said:
Thanks Peter_SSs and AlanY
Click to expand...
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
You must log in or register to reply here.

Similar threads

D
  • Solved
Conditional Formatting Finding Multiple Matched Values
Replies
6
Views
577
Joe4
Joe4
megera716
  • Question Question
INDEX/MATCH for lookup criteria and value between low/high range (like VLOOKUP "TRUE")
Replies
3
Views
232
etaf
etaf
T
  • Solved
Two Value if true, one value if false
Replies
5
Views
365
Joe4
Joe4
B
  • Question Question
Conditional Formatting for multiple cells based on other cell value
Replies
4
Views
922
Benong
B
B
  • Solved
How to return closest value based on two criteria?
Replies
6
Views
133
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

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