Lookup (between) formula

[SimonHughes]

Hi All, I need to return a value, (Band) based on the Score and where it fits between From and To and I cannot work out the formula for doing so. Can anyone help? TIA

Name	Score	Band		From	To	Band
Bill	2.74			1.00	1.50	1
Jim	1.80			1.51	2.00	2
Sue	3.15			2.01	2.50	3
Joe	2.12			2.51	3.00	4
				3.01	3.50	5
				3.51	4.00	6

 

[Joe4]

You already have your LOOKUP table set up correctly, so this is easy if you use the approximate match argument of VLOOKUP.

Formula in C2:

=VLOOKUP(B2,E$2:G$7,3,TRUE)

and copy down to cell C5.

 

[Phuoc]

Or try this:

Formula.xlsx
	A	B	C	D	E	F	G
1	Name	Score	Band		From	To	Band
2	Bill	2.74	4	4	1	1.5	1
3	Jim	1.8	2	2	1.51	2	2
4	Sue	3.15	5	5	2.01	2.5	3
5	Joe	2.12	3	3	2.51	3	4
6					3.01	3.5	5
7					3.51	4	6
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=MATCH(1,($E$2:$E$7<=B2)*($F$2:$F$7>=B2),0)
D2:D5	D2	=MATCH(B2,$E$2:$E$7,1)

 

[SimonHughes]

You already have your LOOKUP table set up correctly, so this is easy if you use the approximate match argument of VLOOKUP.

View attachment 117308

Formula in C2:

=VLOOKUP(B2,E$2:G$7,3,TRUE)

and copy down to cell C5.

This is it, many thanks indeed. Appreciated

 

[SimonHughes]

Or try this:

Formula.xlsx
	A	B	C	D	E	F	G
1	Name	Score	Band		From	To	Band
2	Bill	2.74	4	4	1	1.5	1
3	Jim	1.8	2	2	1.51	2	2
4	Sue	3.15	5	5	2.01	2.5	3
5	Joe	2.12	3	3	2.51	3	4
6					3.01	3.5	5
7					3.51	4	6
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=MATCH(1,($E$2:$E$7<=B2)*($F$2:$F$7>=B2),0)
D2:D5	D2	=MATCH(B2,$E$2:$E$7,1)

Also solves the question, many thanks indeed