14 August, 2012

Alternative to Nested IFs in Excel

Using Excel for multiple comparisons with Nested IFs has some limitations. There a couple of different solutions. One would be to use values on a separate sheet and the vlookup function. Another option is to use multiple IFs separate by an ampersand (&).

Here's and example:
IF(NOT(ISERROR(FIND("Incident Created",A2))),"Incident Created page",""))&IF(AND(ISERROR(FIND(" | ",A2)),ISERROR(FIND("Community",A2)),ISERROR(FIND("Search Results",A2))),"Help Landing page","")
Note the false condition sets an empty value.

I see a benefit in that the code is much easier to read, as nesting can become difficult to follow. The down side is you need to test the negative condition of other IFs so you don't have multiple matches.

No comments: