If and vlookup questions - I think

[snowy3uk]

In one cell (let's call it A1) I have a list with three values - "Non Std", "Vacant", "Let"
In cell A2 a value will be entered - any number between 0 and 5000000
Cell A3 sets a discount depending on both the value selected in A1 and the number in A2 but:
If the A1 = "Non Std" then depending on the value in A2 the value in A3 is taken from column 2

Non Std
<250000	0
>250000 and <400000	-10
>400000 and <750000	-15
>750000	-20

If A1 = "Let" then depending on the value in A2 the value in A3 is taken from column 2

Let
<250000	0
>250000 and <500000	-5
>500000 and <1000000	-10
>1000000	-15

If A1 = "Vacant then depending on the value in A2 the value in A3 is taken from column 2

Vacant
<500000	0
>500000	-10

 

[Jeffrey Mahoney]

Hey Snowy, please try this:

I created 3 named ranges called NonStd, Let, and Vacant. I created 2 formulas to help find the values.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Non Std				Non Std			Let			Vacant
2	10,000,000				0	0		0	0		0	0
3	0.5				250,001	-10		250,001	-5		500,001	-10
4					400,001	-15		500,001	-10
5	Choice:	2	-20		750,001	-20		1,000,001	-15
Sheet2

Cell Formulas
Range	Formula
B5	B5	=IF(A1="Non Std",2,IF(A1="Let",3,IF(A1="Vacant",4,1)))
C5	C5	=CHOOSE(B5,A3*A2,VLOOKUP(A2,Non_Std,2,TRUE),VLOOKUP(A2,Let,2,TRUE),VLOOKUP(A2,Vacant,2,TRUE))

Cells with Data Validation
Cell	Allow	Criteria
A1	List	Non Std,Let,Vacant

 

[snowy3uk]

Thank you very much - that works like a charm!