Problem with Nested IFs Using Find

[Alex20850]

The following is sample data: The first column is the original data: The second column is what I am getting when I use the formula in the third column. The fourth column is what I would like to get. I thought I understood nested iFs, but I am missing something. Thanks! 719 S ABERDEEN ST 1,CICERO IL	CICERO	=IF(FIND("CICERO",B2),"CICERO",IF(FIND("CHICAGO",B2),"CHICAGO",""))	CICERO
1656 W HOWARD ST 1ST,CICERO IL	CICERO	=IF(FIND("CICERO",B3),"CICERO",IF(FIND("CHICAGO",B3),"CHICAGO",""))	CICERO
700 N GREEN ST 3RD 304,CHICAGO IL	#VALUE!	=IF(FIND("CICERO",B8),"CICERO",IF(FIND("CHICAGO",B8),"CHICAGO",""))	CHICAGO
11 E ILLINOIS ST,CHICAGO IL	#VALUE!	=IF(FIND("CICERO",B9),"CICERO",IF(FIND("CHICAGO",B9),"CHICAGO",""))	CHICAGO
1008 W LAKE ST 1ST,CHICAGO IL	#VALUE!	=IF(FIND("CICERO",B10),"CICERO",IF(FIND("CHICAGO",B10),"CHICAGO",""))	CHICAGO
113-125 N GREEN ST BSMT - 6TH,SPRINGFIELD IL	#VALUE!	=IF(FIND("CICERO",B14),"CICERO",IF(FIND("CHICAGO",B14),"CHICAGO",""))	OTHER

<tbody>
</tbody>

 

[mikejvir]

If Find does not find the text, then there is an Error so you must use ISERROR() to trap.

Here is line 14. The logic is reversed.

=IF(ISERROR(FIND("CICERO",B14)),IF(ISERROR(FIND("CHICAGO",B14)),"OTHER","CHICAGO"),"CICERO")

Remember you can only go 7 deep with the IF statement.

Mike Virostko

 

[C Moore]

If you have a list of cities you can expand it later. I have Cicero and Chicago in adjacent cells and name the range city_list. Next to the address (in A5) I used =IFERROR(LOOKUP(2^15,SEARCH(city_list,A5),city_list),"Other").

 

[Alex20850]

thanks much!

If Find does not find the text, then there is an Error so you must use ISERROR() to trap.

Here is line 14. The logic is reversed.

=IF(ISERROR(FIND("CICERO",B14)),IF(ISERROR(FIND("CHICAGO",B14)),"OTHER","CHICAGO"),"CICERO")

Remember you can only go 7 deep with the IF statement.

Mike Virostko

 

[Alex20850]

Thanks for the response, but I don't follow the part about 2^15.

If you have a list of cities you can expand it later. I have Cicero and Chicago in adjacent cells and name the range city_list. Next to the address (in A5) I used =IFERROR(LOOKUP(2^15,SEARCH(city_list,A5),city_list),"Other").

 

[C Moore]

I don't really either! It was a shortcut I found a couple of years ago on a site similar to this. The 2^15 has something to do with the number of characters allowed in a cell, but not sure how it relates. The nice thing about this is its like index-match, in that the last range of the lookup is the range to return (index), but the match function is not based on the whole cell contents (search instead). The SEARCH setup is kind of opposite what you expect, with a range of values to look for instead a single value in a single cell instead of a range. The IFERROR was just to return other. I promise this will come in handy one day.

 

[XOR LX]

Thanks for the response, but I don't follow the part about 2^15.

In this construction with LOOKUP, it is necessary that the choice of lookup_value be greater than the largest possible return from the SEARCH function.

Hence, with your data, if you knew that the result of the SEARCH function would never be greater than, say 100, then a lookup_value of 101 would suffice.

Of course, working out what the largest possible return of the SEARCH function would be, based on your data, might be a time-consuming and difficult task. As such, it makes sense to choose a suitably large, generic value for this parameter, such that not only will it always be sufficient, but will mean that no such calculations have to be performed by yourself.

Since the largest possible return from the SEARCH function is 32,767, 2^15, which is 32,768, will always be a sufficient choice.

Of course, any value greater than this will also suffice, hence why you will also see the use of arbitrarily large values in this construction, e.g. 100^100, or the so-called "BigNum" (the largest allowable positive number in Excel), i.e. 9.99999999999999E+307.

I personally find some logic (and even elegance) in the idea of choosing the smallest possible value in these cases, though each to their own, I suppose.

Regards