"IF" function for up to 6 comparison values

[JerryMason]

Hey everyone,

I have another interesting problem I can't find the answer to (exactly).

I have a value in Column A (we'll call it value A) and with a single formula I'd like to try and be able to compare it to 6 other values in cells adjacent to it (Columns B, C, D, E, F, G) and in Column H we have a formula tell us if A is:

Below B = 0
Between B and C = 1
Between C and D = 2
Between D and E = 3
Between E and F = 4
Between F and G = 5
Above G = 6

For example if A = 21 and the comparison values are ------->>> 3 | 17 | 24 | 29 | 38 | 51
The formula in H will return a "2"

*All values from B to G are sequential counting upwards, nothing is out of order.

I've tried this with two values (some form of an if(and) ) but now I'm at a loss with up to six. Is there a way to do this even?

 

[jasonb75]

Try something like

=IFERROR(MATCH(A2,B2:G2),0)

Book1
	A	B	C	D	E	F	G	H	I
2	21	3	17	24	29	38	51		2
Sheet6

Cell Formulas
Range	Formula
I2	I2	=IFERROR(MATCH(A2,B2:G2),0)

 

[etaf]

not sure but does this work
=IFERROR(MATCH(A2,B2:G2,1),0)

Book2
	A	B	C	D	E	F	G	H
1
2	3	2	4	12	22	32	101	1
3
Sheet1

Cell Formulas
Range	Formula
H2	H2	=IFERROR(MATCH(A2,B2:G2,1),0)

 

[Kaper]

If your data is in row 1, so tested value in A1, and "limits" in B1:G1 then

=IFERROR(MATCH(A1,B1:G1),0)

shall return exactly what you need

 

[Fluff]

Another option

=IFNA(XMATCH(A2,B2:G2,-1),5)

 

[JerryMason]

Thanks Guys, the IFERROR(MATCH etc worked like a charm. Its so simple, I had no idea, Learn something new every day!

 

[Fluff]

Glad we could help & thanks for the feedback.