Formula Help, CSE & Match with Multiple Criteria

B

bk1702

New Member
Joined
Jul 18, 2012
Messages
38
How do I get the value that matches for each of these?

Desired result in C9 is "Alpha". This is also a CSE formula.

{=IF(AND(INDEX(G3:G5,MATCH(E3&E4&E5&E6,H3:H5&I3:I5&J3:J5&K3:K5,0))=INDEX(G3:G5,MATCH(E3&D4&D5&D6,H3:H5&I3:I5&J3:J5&K3:K5,0)),INDEX(G3:G5,MATCH(E3&C4&C5&C6,H3:H5&I3:I5&J3:J5&K3:K5,0))=INDEX(G3:G5,MATCH(E3&D4&D5&D6,H3:H5&I3:I5&J3:J5&K3:K5,0))),"How do I show the value that matches for each of these?","false")}


Thanks for any help you can provide!

[TABLE="class: grid, width: 773"]
<TBODY>[TR]
[TD][/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[TD]F</SPAN>[/TD]
[TD]G</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[TD]I</SPAN>[/TD]
[TD]J</SPAN>[/TD]
[TD]K</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name</SPAN>[/TD]
[TD]# Cuts</SPAN>[/TD]
[TD]Type 1</SPAN>[/TD]
[TD]Type 2</SPAN>[/TD]
[TD]Type 3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>[/TD]
[TD][/TD]
[TD]CUT 1</SPAN>[/TD]
[TD]CUT 2</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]d</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>[/TD]
[TD]Type 1:</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>[/TD]
[TD]Type 2:</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>[/TD]
[TD]Type 3:</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[TD]d</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>[/TD]
[TD][/TD]
[TD="colspan: 5"]How do I show the value that matches for each of these?</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL span=4></COLGROUP>[/TABLE]
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi,

I'm not sure I fully understand? Perhaps like this:
Excel Workbook
BCDEFGHIJK
2Name# CutsType 1Type 2Type 3
3CUT 1CUT 2CUT 3AlphaCUT 3acd
4Type 1:aabAlphaCUT 3ace
5Type 2:cccAlphaCUT 3bce
6Type 3:edeBetaCUT 3abc
7BetaCUT 3abe
8BetaCUT 3ace
9AlphaAlphaAlpha
Sheet1
Excel 2010
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
bk1702 said:
How do I get the result of "Alpha" in one cell, if and only if all three match?
Click to expand...
Maybe try:
Excel Workbook
BCDEFGHIJK
2Name# CutsType 1Type 2Type 3
3CUT 1CUT 2CUT 3AlphaCUT 3acd
4Type 1:aabAlphaCUT 3ace
5Type 2:cccAlphaCUT 3bce
6Type 3:edeBetaCUT 3abc
7BetaCUT 3abe
8BetaCUT 3ace
9Alpha
Sheet1
Excel 2010
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
Unfortunatly, it doesn't hold true when the values change and one of them matches another option.

[TABLE="width: 773"]
<TBODY>[TR]
[TD][/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[TD]F</SPAN>[/TD]
[TD]G</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[TD]I</SPAN>[/TD]
[TD]J</SPAN>[/TD]
[TD]K</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name</SPAN>[/TD]
[TD]# Cuts</SPAN>[/TD]
[TD]Type 1</SPAN>[/TD]
[TD]Type 2</SPAN>[/TD]
[TD]Type 3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>[/TD]
[TD][/TD]
[TD]CUT 1</SPAN>[/TD]
[TD]CUT 2</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]d</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>[/TD]
[TD]Type 1:</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>[/TD]
[TD]Type 2:</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>[/TD]
[TD]Type 3:</SPAN>[/TD]
[TD]d</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>[/TD]
[TD][/TD]
[TD]#N/A</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]<-my original, errors are OK if neither is true[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]Beta</SPAN>[/TD]
[TD]Alpha</SPAN>[/TD]
[TD]<-1st try[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]<-2nd try[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL span=4></COLGROUP>[/TABLE]
 
Upvote 0
Doesn't matter I can mess with it later. The main concern is getting all of them to match. In the second try the three are all not Alpha, but it's still giving a response of Alpha.
 
Upvote 0
bk1702 said:
Doesn't matter I can mess with it later. The main concern is getting all of them to match. In the second try the three are all not Alpha, but it's still giving a response of Alpha.
Click to expand...
How would you know if 'ace' for example is meant to be Alpha or Beta - as it matches both?

Or is this what your trying to find out - i.e. where 1 of them can be either you want to get the name that would make all three match?
 
Last edited:
Upvote 0
Maybe:
Excel Workbook
BCDEFGHIJK
2Name# CutsType 1Type 2Type 3
3CUT 1CUT 2CUT 3AlphaCUT 3acd
4Type 1:aaaAlphaCUT 3ace
5Type 2:cbbAlphaCUT 3bce
6Type 3:eceBetaCUT 3abc
7BetaCUT 3abe
8BetaCUT 3ace
9Beta
Sheet1
Excel 2010
#VALUE!
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
 
Upvote 0
All three conditions have to be true, the name is really the item that ties the 3 cuts together.

This formula works to get the correct result (correct for true/false), I just don't know how to get the name that matches for all three conditions.

{=IF(AND(INDEX(G3:G5,MATCH(E3&E4&E5&E6,H3:H5&I3:I5&J3:J5&K3:K5,0))=INDEX(G3:G5,MATCH(E3&D4&D5&D6,H3:H5&I3:I5&J3:J5&K3:K5,0)),INDEX(G3:G5,MATCH(E3&C4&C5&C6,H3:H5&I3:I5&J3:J5&K3:K5,0))=INDEX(G3:G5,MATCH(E3&D4&D5&D6,H3:H5&I3:I5&J3:J5&K3:K5,0))),"How do I show the value that matches for each of these?","false")}



[TABLE="width: 773"]
<TBODY>[TR]
[TD][/TD]
[TD]B</SPAN>[/TD]
[TD]C</SPAN>[/TD]
[TD]D</SPAN>[/TD]
[TD]E</SPAN>[/TD]
[TD]F</SPAN>[/TD]
[TD]G</SPAN>[/TD]
[TD]H</SPAN>[/TD]
[TD]I</SPAN>[/TD]
[TD]J</SPAN>[/TD]
[TD]K</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]2</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Name</SPAN>[/TD]
[TD]# Cuts</SPAN>[/TD]
[TD]Type 1</SPAN>[/TD]
[TD]Type 2</SPAN>[/TD]
[TD]Type 3</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]3</SPAN>[/TD]
[TD][/TD]
[TD]CUT 1</SPAN>[/TD]
[TD]CUT 2</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]d</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]4</SPAN>[/TD]
[TD]Type 1:</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]5</SPAN>[/TD]
[TD]Type 2:</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]6</SPAN>[/TD]
[TD]Type 3:</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[TD]d</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]7</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]b</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]8</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Beta</SPAN>[/TD]
[TD]CUT 3</SPAN>[/TD]
[TD]a</SPAN>[/TD]
[TD]c</SPAN>[/TD]
[TD]e</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]9</SPAN>[/TD]
[TD][/TD]
[TD]#N/A</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]original [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD]Alpha</SPAN>[/TD]
[TD]Beta</SPAN>[/TD]
[TD]first[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]second[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Alpha</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]third[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL span=4><COL><COL span=4></COLGROUP>[/TABLE]


Thanks for your help this is driving me crazy...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Nearest date based on three criteria
Replies
2
Views
256
chris_bosten
C
T
  • Question Question
What if investment
Replies
2
Views
55
tigerzen
T
W
  • Solved
Index and Match on Multiple Columns with Duplicate Values
Replies
4
Views
566
wsr429
W
D
  • Solved
Index/match multiple criteria not working - HELP please
Replies
5
Views
325
NickFru
N
S
  • Solved
Adding extra step to Index Match Formula
Replies
4
Views
185
Sundance_Kid
S

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

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