# Formula to create a classification

##### New Member
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!

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
 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

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

You're welcome.

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:

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?

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

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:

So will work on trying this next ...

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!

Replies
7
Views
217
Replies
1
Views
580
Replies
14
Views
705
Replies
1
Views
855
Replies
3
Views
379

1,203,212
Messages
6,054,190
Members
444,708
Latest member
David R__

### 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.

### Which adblocker are you using?

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

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