Return Text based on multiple Criteria

[Sandeep Warrier]

Hey All,

How do I return text based on multiple criteria. I can return count or sum based on multiple criteria using CSE+SUM or SUMPRODUCT. But I am not sure how to return text for based on multiple criteria.

A sample table:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 103px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Input 1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Input 2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Result</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid"> </TD></TR></TBODY></TABLE>

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 103px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center" colSpan=3>Data Table</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Sand</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Water</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Clay</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Sodium</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Chlorine</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Sodium Chloride</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Sand</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Heat</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Glass</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Clay</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Heat</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Bricks</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

The user inputs values in the columns for Input 1 & Input 2 and based on that gets a result. For example if the user inputs Sand and Water, the result should show clay.

However, if the user inputs a combination that is not present in the data table, the result should return "No such Combination exists"

Thanks for all help.

Regards,
Sandeep.

 

[sanrv1f]

try this with CSE

=INDEX($E$16:$E$19,MATCH(C8&D8,$C$16:$C$19&$D$16:$D$19,0),1)

 

[Sandeep Warrier]

Hey Sankar,

Works fine....

Modified it to

=IF(ISERROR(INDEX($E$16:$E$19,MATCH(C9&D9,$C$16:$C$19&$D$16:$D$19,0))),"No Such Combination Exists",INDEX($E$16:$E$19,MATCH(C9&D9,$C$16:$C$19&$D$16:$D$19,0)))

 

[Sandeep Warrier]

To add to this, how do I get it to work with numbers.

For example, instead of text, if I had numbers in the data table as shown below:

Excel Workbook
	C
12	Input 1
13
Sheet1

Excel Workbook
	C	D	E
15	Data Table
16	350	400	Clay
17	100	200	Sodium Chloride
Sheet1

I would like to return text where the following conditions are met...

column C < input1 & column D > input1

 

[Domenic]

Try...

=INDEX($E$16:$E$19,MATCH(1,IF($C$16:$C$19 < C13,IF($D$16:$D$19 > C13,1)),0))

or

=LOOKUP(REPT("z",255),CHOOSE({1,2},"No Such Combination Exists",INDEX($E$16:$E$19,MATCH(1,IF($C$16:$C$19 < C13,IF($D$16:$D$19 > C13,1)),0))))

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER.

 

[Sandeep Warrier]

Hey Domenic,

Works fine. thanks a lot

Regards,
Sandeep

 

[Domenic]

You're very welcome!