Creating a unique reference if cell data is between a number range

[MartKenn08]

Hi,

Looking for the correct formula to use to generate a unique reference based off the number value shown in a column.

e.g. the number in cell D1 is between 1-2 produce reference R100 in in cell E1, if the number is between the range 3-5 populate the reference R200 etc.

Any help will be greatly appreciated, using excel for MSO 365.

Thanks,

 

[awoohaw]

try this:

Book1
	A	B	C	D	E	F	G	H
1				1.50	R100
2
3							1.00	R100
4							3.00	R200
5							5.00	R300
Sheet4

Cell Formulas
Range	Formula
E1	E1	=XLOOKUP(D1,G3:G5,H3:H5,"not found",-1,1)

 

[MartKenn08]

Thank you that's worked perfectly!

 

[awoohaw]

Happy to help. Best Wishes.