Mutlipe IF

[Welsh Mark3]

I would like to write a formula that picks a value from below if it falls within that range. Can someone help?

• 0 – 250,000
• 250,001 – 500,000
• 500,001 – 1,000,0000
• 1,000,001 – 5,000,000
• 5,000,001 – 10,000,000
• 10,000,001 to 25,000,000
• 25,000,001 – 50,000,000
• 50,000,001 – 100,000,000
• 100,000,001 – 500,000,000
• > 500,000,001

 

[FryGirl]

The typical way to do this is to create two columns for the ranges and then use a lookup formula. You did not specify what the value would be, but here's an example.

Book1.xlsx
	A	B	C	D	E
1	0	250,000	1
2	250,001	500,000	2
3	500,001	10,000,000	3		6,700,001
4	1,000,001	5,000,000	4		5
5	5,000,001	10,000,000	5
6	10,000,001	25,000,000	6
7	25,000,001	50,000,000	7
8	50,000,001	100,000,000	8
9	100,000,001	500,000,000	9
Sheet1

Cell Formulas
Range	Formula
E4	E4	=LOOKUP(E3,$A$1:$A$9,$C$1:$C$9)