Can I use a VLOOKUP with IF criteria of greater than and less than

[Kylie_B]

I'm at a loss and not sure what formula I need.

The value is between the range of two other numbers I need the answer to be a specific cell, the result being a revised fee.

Value $1,444,1111
Range Greater than $1,000,000 and less than $1,999,999
Result = $2,000

I've tried VLOOKUP with If of greater and less than and the result true/false not the specific cell of $2,000

There are five additional ranges, I will need the formula to adapt to the range.

 

[bebo021999]

"Range Greater than $1,000,000 and less than $1,999,999"
Is this text string in a cell?
For instant,
A1 = 1444111
A2 = "Range Greater than $1,000,000 and less than $1,999,999"
?

 

[Kylie_B]

In columns
A1 - 1444111
B1 - 1000000
C1 - 1999999
D1 - 2000

Formula cell E1 = If A1 is greater than B1 and Less than C1 = D1

 

[Kylie_B]

This is how I'm trying to make it work, I have a list of 300 that I need to work out the fee amount based on the range.

If the Criteria is between the lower range and the upper range then the result is equal to the fee

Lower Range	Upper Range	Fee
$ 10,000,000		$2,200.00
$ 5,000,000	$9,999,999	$1,100.00
$ 2,000,000	$4,999,999	$ 875.00
$ 1,000,000	$1,999,999	$ 650.00
$ 500,000	$ 999,999	$ 550.00
$ 250,000	$ 499,999	$ 325.00
$ 1	$ 249,999	$ 220.00
Criteria	Fee amount
$ 144,204,698	$ 2,200.00
$ 104,909,722	$ 2,200.00
$ 98,614,922	$ 2,200.00
$ 91,108,185	$ 2,200.00
$ 1,583,043	$ 650.00
$ 1,271,662	$ 650.00
$ 25,148	$ 220.00
$ 654,281	$ 550.00
$ 24,598	$ 220.00

 

[bebo021999]

In fact, we do not need upper range, by using LOOKUP:

Book2
	A	B	C	D	E	F
1	Lower Range	Fee			Criteria	Fee amount
2	1	220			144,204,000	2,200
3	250,000	325			104,000,000	2,200
4	500,000	550			98,000,000	2,200
5	1,000,000	650			9,000,000	1,100
6	2,000,000	875			1,500,000	650
7	5,000,000	1,100			24,000	220
8	10,000,000	2,200
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=LOOKUP(E2,$A$2:$A$8,$B$2:$B$8)

 

[Kylie_B]

Works perfectly, thank you.