CODEWITHSUNDEEP
excelexcel-formulavlookupexcel-2013worksheet-function
user2823183
user2823183

Reputation: 77

Value between a range of values

Having this: 

Class   Min    Max
Alfa    0      16.5
Beta    16.5   18.5
Charlie 18.5   25
Delta   25     30

And this:

Value X
35.52600894
26.27816853
29.53159178
29.84528548
26.77130341
25.07792506
19.2850645
42.77156244
29.11485934
29.5010482
19.30982162

I want a cell to have something like an IF statement (it's got a few more values in it, not this small, it has 8 class). An IF statement this long would probably not work (IF limit of 7) and is an ugly way of doing it. I was thinking of using hlookup, but I'm not sure if that's the best bet. I can also swap the columns within a table, so I could have "Min| Max| Class" X values are in a column.

Basically: =IF(X>=0 && X<16.5, Alpha, IF(X>=16.5 && X<18.5, Beta, IF(...

Upvotes: 1

Views: 242

Answers (2)

pnuts
pnuts

Reputation: 59475

With your Max range named MaxVal and your Class range named Class, please try: 

    =IF(A2>30,"",INDEX(Class,MATCH(A2,MaxVal)))

(adjust references to suit).

=MATCH() here is using the match_type parameter of 1: “The MATCH function will find the largest value that is less than or equal to value. You should be sure to sort your array in ascending order.

If the match_type parameter is omitted, the MATCH function assumes a match_type of 1.”

Any X value greater than 30 returns a blank ("") but text may be inserted to suit (eg "Unidentified" instead of "").

The formula could be simplified by removing the error trap, if a row were inserted immediately under the labels with Alpha under Class and 0 under Max. Also by removing the condition, in a similar way.

It is not necessary to specify both bounds of each range.

INDEX/MATCH was chosen rather than say VLOOKUP for reasons as given here.

PS For the Greek *alpha*bet α is usually Alpha.

Edit re clarification

The easiest fix for 25 is Delta rather than Charlie may be to deduct a small amount from each Max value, eg change 25 to =25-1/1E100.

Upvotes: 1

asantaballa
asantaballa

Reputation: 4048

I think you mean VLOOKUP and would be much better way to go.

Make a Ranges sheet like this

Min    Class
0      Alfa
16.5   Beta
18.5   Charlie
25     Delta
30.5   Unidentified

In your detail sheet use formula "=VLOOKUP(A2,Ranges!A:B,2,TRUE)" [The True is important]

And you get 

Value X Class
35.52600894 Unidentified
26.27816853 Delta
29.53159178 Delta
29.84528548 Delta
26.77130341 Delta
25.07792506 Delta
19.2850645  Charlie
42.77156244 Unidentified
29.11485934 Delta
29.5010482  Delta
19.30982162 Charlie

Upvotes: 2

Related Questions