Formula to create a classification

[linksavage]

This will be an easy one for someone out there.

I need a formula that will evaluate 3 combinations of 2 variables (Sales and Employees) and lookup the correct classification. Tried using Nested IFs but it got very confusing very quickly for me!

For example:

Sales	Employees	Classification
< 1600	< 10	Micro
< 8000	< 50	Small
< 40000	< 250	Medium
If there is not a	match on both, then:	Large

<tbody>
</tbody>

Thanks!

 

[shg]

	A​	B​	C​	D​
1​	Sales​	Employees​	Classification​
2​	1,600​	10​	Micro
3​	8,000​	50​	Small
4​	40,000​	250​	Medium
5​
6​
7​	1,000​	9​	Micro	C7: =IFERROR(INDEX($C$2:$C$4, MATCH(1, INDEX((A7<$A$2:$A$4) * (B7<$B$2:$B$4), 0), 0)), "Large")
8​	1,000​	10​	Small
9​	40,000​	1​	Large
10​	2,000​	40​	Small

 

[linksavage]

Thank you! Seems to be just what I needed ...

 

[shg]

You're welcome.

 

[linksavage]

Oops, I spoke too soon ...

Your recommended formula works exactly as desired in my Excel 2010, but I need the formula to work in a system that uses an "embedded" set of Excel 97-2003 capabilities, as shown here:

http://poi.apache.org/spreadsheet/formula.html

Unfortunately, the guidance on supported vs non-supported Excel functions is limited ... from the link above:

Supported Features

• References: single cell & area, 2D & 3D, relative & absolute
• Literals: Number, text, boolean, error and array
• Operators: arithmetic and logical, some region operators
• Built-in functions: over 350 recognised, 280 evaluatable
• Add-in functions: 3 from Analysis Toolpack

Not yet supported

• Manipulating array/table formulas (In Excel, formulas that look like "{=...}" as opposed to "=...")
• Region operators: union, intersection
• Parsing of previously uncalled add-in functions
• Preservation of whitespace in formulas (when POI manipulates them)

Can this be accomplished in any other way?

 

[shg]

I would have no way to test a suggestion. Maybe you should try an Apache POI forum.

 

[linksavage]

Based upon this, it appears that ISERROR can be used in place of IFERROR using this syntax ... and I believe would be supported in the POI framework:

if(iserror(your formula),"",your formula)

So will work on trying this next ...

 

[linksavage]

I've now determined that I cannot use ISERROR or IFERROR, but that I can use the IF and ERROR.TYPE formulas ... so can someone please help me convert the formula provided by shg (below) to using IF and ERROR.TYPE instead:

=IFERROR(INDEX($C$2:$C$4, MATCH(1, INDEX((A7<$A$2:$A$4) * (B7<$B$2:$B$4), 0), 0)), "Large")

Thanks!