Nested if

[kochan122]

I am new to excel. I am trying to categorise numbers of employees in groups. In one column I have numer of employees and other has nothing in it and I am writing formula like this in the second column, if the number of employees is less that 10, then write 4-9, if less than 50 then 10-49 etc and if any of this then last one 100000-max. Every time I try to use it there is communicate that there is problem with this formula and I don't know where is a problem. From what I looked on internet, this formula should be working.
=IF(G11<10,"4 - 9",
IF(G11<50,"10 - 49",
IF(G11<300,"50 - 299",
IF(G11<500,"300 - 499",
IF(G11<1000,"500 - 999",
IF(G11<5000,"1000 - 4999",
IF(G11<10000,"5000 - 9999",
IF(G11<20000,"10000 - 19999",
IF(G11<50000,"20000 - 49999",
IF(G11<100000,"50000 - 99999",
"100000 - max"))))))))))

 

[James006]

Hi,

Instead of a rather complex formula with Nested Ifs ... you should make your life easier and use VLOOKUP() ...

see https://exceljet.net/tips/how-to-use-vlookup-instead-of-nested-ifs

Hope this will help

 

[Yongle]

or Index and Match

=INDEX($B$1:$B$12,MATCH(G11,$A$1:$A$12,-1))

<colgroup><col></colgroup><tbody>
</tbody>

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	99999999​	100000 - max​
2​	99999​	50000 - 99999​
3​	49999​	20000 - 49999​
4​	19999​	10000 - 19999​
5​	9999​	5000 - 9999​
6​	4999​	1000 - 4999​
7​	999​	500 - 999​
8​	499​	300 - 499​
9​	299​	50 - 299​
10​	49​	10 - 49​							FORMULA in H11
11​	9​	4 - 9​					999​	500 - 999	=INDEX($B$1:$B$12,MATCH(G11,$A$1:$A$12,-1))
12​	4​	0 - 4​

Sheet: Sheet1

 

[kochan122]

Thank you, it worked!

 

[Yongle]

thanks for your feedback