# Alternative for IF formula for office 2016 for more than 64 Nesting



## Sufiyan97 (Jun 20, 2022)

I am using IF formula and the limit for nesting is 64, is there any way to nest more than 64 conditions, I don't have Office 365 so IFs is not available in my system.


----------



## jasonb75 (Jun 20, 2022)

If you need to nest that many IF's then it's likely that you're trying to do something in a very inefficient manner. 

The question you should be asking is not how to nest more IF's but how to to do your task without using as many IF's.


----------



## Sufiyan97 (Jun 20, 2022)

I have data like below, I have more than 64 descriptions may be around 200 so for each description I need a formula to identify the text and take category based it.

Book3ABCD1DescriptionCategory2Other text Amazon Other textOffice SuppliesBased on AmazonOffice Supplies3Other text Amzn Other textOffice SuppliesBased on AmznOffice Supplies4Other text Amazon.com Other textOffice SuppliesBased on Amazon.comOffice Supplies5Other text Amazon Prime Other textDues and SubscriptionsBased on Amazon PrimeDues and Subcriptions6Other text Walmart Other textOffice SuppliesBased on WalmartOffice Supplies7Other text WM Supercenter Other textOffice SuppliesBased on WM SupercenterOffice Supplies8Other text Shell Other textFuelBased on ShellN/A9Other text Cheveron Other textFuelBased on CheveronN/ASheet1Cell FormulasRangeFormulaD2:D9D2=IF(ISNUMBER(AGGREGATE(15,6,SEARCH({"Amazon Prime"},A2),1)),"Dues and Subcriptions",IF(ISNUMBER(AGGREGATE(15,6,SEARCH({"Amazon","Amzn","Walmart","Wm Super"},A2),1)),"Office Supplies","N/A"))


----------



## jasonb75 (Jun 20, 2022)

Simple index and match with wildcards based on the example. Assuming that C2 contains a valid criteria to search for in the description list.

```
=INDEX(B:B,MATCH("*"&C2&"*",A:A,0))
```
Or do you want it the other way around? When you squash everything together like that details are easily mixed up.


----------



## Sufiyan97 (Jun 20, 2022)

Column C won't be there there will be just column A, and category will be based on formula, I provided column C for just criteria that on what basis I put category and column D is a formula I am currently using

I don't have any issue with helper columns


----------



## jasonb75 (Jun 20, 2022)

Column D wasn't there when I looked at your post, I think you must have done an edit between when I read it and when I replied.

With such messy data there is no real ideal solution, this will work with your example and similar data by using the table in columns D and E as a lookup reference.
Note that the lookup table is sorted by column D in descending order. It can be done in descending order if you change the aggregate function number to 14. Unsorted data has the potential to give incorrect results when a short description is a partial match for a longer one (Amazon and Amazon Prime in your example).

Book1ABCDE1DescriptionCategoryDescCat2Other text Amazon Other textOffice SuppliesWM SupercenterOffice Supplies3Other text Amzn Other textOffice SuppliesWalmartOffice Supplies4Other text Amazon.com Other textOffice SuppliesShellFuel5Other text Amazon Prime Other textDues and SubcriptionsCheveronFuel6Other text Walmart Other textOffice SuppliesAmznOffice Supplies7Other text WM Supercenter Other textOffice SuppliesAmazon.comOffice Supplies8Other text Shell Other textFuelAmazon PrimeDues and Subcriptions9Other text Cheveron Other textFuelAmazonOffice SuppliesSheet1Cell FormulasRangeFormulaB2:B9B2=INDEX($E:$E,AGGREGATE(15,6,ROW($D$2:$D$9)/ISNUMBER(SEARCH($D$2:$D$9,A2)),1))


----------



## Sufiyan97 (Wednesday at 9:00 AM)

Hello @jasonb75

Thank you very much for the formula, I tried the day you have provided but as per your comment, it was giving wrong result when data was not sorted.

I did not use at that time, I thought it will result in error when I provide it to someone.

But when I slightly modified the formula:
(I have changed references as per my data)


```
=INDEX($F$2:$F$255,AGGREGATE(15,6,ROW($F$2:$F$255)-ROW($F$2)+1/ISNUMBER(SEARCH($G$2:$J$255,A2)),1))
```

then

It works without or with sorted data.
It is very helpful for me, the task is done in seconds which may take around 1 or 1.5 hours based on size of data.

I can't thank you enough for this.

Thank you very very much!


----------

