too many nested IF statements

[joebro]

Hi, my data has a list of vegetables which I want to categorise. See data/results columns below. My formula works but i have hit the limit to how many nested IF statements I can use, also it's very cumbersome, there must be a better more elegant way to do this? Thanks in Advance, Joe

=IF(ISNUMBER(SEARCH("Beans",A13)),"Peas, Beans, Corn & Asparagus",IF(ISNUMBER(SEARCH("Peas",A13)),"Peas, Beans, Corn & Asparagus",IF(ISNUMBER(SEARCH("Corn",A13)),"Peas, Beans, Corn & Asparagus",IF(ISNUMBER(SEARCH("Asparagus",A13)),"Peas, Beans, Corn & Asparagus",IF(ISNUMBER(SEARCH("Beet",A13)),"Carrots & Root Vegetables",IF(ISNUMBER(SEARCH("Carrots",A13)),"Carrots & Root Vegetables","Other Veg"))))))


column data / column results

Beetroot / Carrots & Root Vegetables
Beans / Peas, Beans, Corn & Asparagus
carrots / Carrots & Root Vegetables
Peas / Peas, Beans, Corn & Asparagus
Potatoes / Other Veg

 

[AhoyNC]

Maybe use VLOOKUP

Book1
	A	B
1	Beetroot	Carrots & Root Vegetables
2	Beans	Peas, Beans, Corn & Asparagus
3	carrots	Carrots & Root Vegetables
4	Peas	Peas, Beans, Corn & Asparagus
5	Potatoes	Other Veg
6
7
8	Beans	Peas, Beans, Corn & Asparagus
Sheet1

Cell Formulas
Range	Formula
B8	B8	=VLOOKUP(A8,$A$1:$B$5,2,0)

 

[arthurbr]

VLOOKUP or INDEX/MATCH is the way to go. This being said, you should be able to insert 64 IF functions in XL2013?

 

[joebro]

Column B is not data I can use, It's the results column that is defined by what is in column A. So in need to search column A and when i find a match assign Colum B a text string much like my formula was doing

 

[joebro]

so not sure i can use VLOOKUP

 

[AhoyNC]

Maybe set up a list (as in cells A2 - A6) and then use one of the INDEX formulas below.

Book1
	A	B
1	List
2	Carrots & Root Vegetables
3	Peas, Beans, Corn & Asparagus
4	Carrots & Root Vegetables
5	Peas, Beans, Corn & Asparagus
6	Other Veg
7
8	Find
9	Corn	Peas, Beans, Corn & Asparagus
10
11		Peas, Beans, Corn & Asparagus
Sheet1

Cell Formulas
Range	Formula
B9	B9	=IFERROR(INDEX($A$2:$A$6,MATCH(TRUE,ISNUMBER(SEARCH(A9,$A$2:$A$6)),0)),"Other Veg")
B11	B11	=IFERROR(INDEX($A$2:$A$6,MATCH("*"&A9&"*",$A$2:$A$6,0)),"Other Veg")