Check if number is within range

[Mr_Molesely]

Hello! Hope somebody can kindly help

I have a charging table e.g.

No of items	Charge
1-5	£2.50
6-19	£6.30
20-45	£9.99

I want to be able to put a input cell where I can enter number of items e.g. 8 which will then lookup column 1 and then give me the associated charge (here it will be 6-19 items so £6.30)

Is there an elegant method to to work this out? I can tweak the format of the No of Items column but I cannot split into separate columns.

Thanks,
Moley

 

[Legacy 143009]

Split the first column as A and B.
Formula will be:

=VLOOKPUP(input_cell,$A$1:$C$4,3,1)

 

[Joe4]

Split the first column as A and B.
Formula will be:

=VLOOKPUP(input_cell,$A$1:$C$4,3,1)

I think you meant "VLOOKUP", not "VLOOKPUP"!

 

[JamesCanale]

MrExcelPlayground12.xlsx
	A	B
20	No Items	Price
21	1-5	$2.50
22	6-19	$6.30
23	20-45	$9.99
24
25
26	Items
27	8	$6.30
Sheet22

Cell Formulas
Range	Formula
B27	B27	=LET(o,B21:B23,I,A21:A23,j,A27,sm,VALUE(LEFT(I,SEARCH("-",I)-1)),lg,VALUE(MID(I,SEARCH("-",I)+1,99)),x,(j>=sm)*(j<=lg),FILTER(o,x))

 

[Mr_Molesely]

Thank you, I've used Flashbonds example for now even though I specifically did not want to split into separate columns but I will have a go at James's new Let method soon, You gentlemen/women? are amazing and are helping to solve real business problems!