I think I need a match, vlookup, or index function?

[Jason M]

I need to assign retail locations to a certain classification based on the
sales tier and their profit margin tier. I have 16 classifications in total.
I can do it with IF functions, but there has to be an easier way.

For example,

col 1 col2 col3 col4 col5
sales low sales high profit low profit high classification
$0 $100 0% 0.50% 1
$0 $100 0.51% 1.00% 2
$101 $200 0% 0.50% 3
$101 $200 0.51% 1.00% 4


So if my store did $150 in sales and has a profit margin of 0.35%, then they
would be in classification 3. I have a data list that has the sales and
profit margin by location. Anyone know a way to simply do this? Thanks for
any help!!

 

[Niek Otten]

Change the setup of your table, like this:

0 1 2
100 3 4

Say it is in A1:C2

The sales is in A5 and the profit in B5

=VLOOKUP(A5,A1:C2,IF(B5>0.5%,3,2))