Simplifying nested IF formula
Posted by Paul Wakefield on January 04, 2001 9:41 AM
Is there any way of simplifying the formula below. I thought it could be by using an array formula but I have tried this without success.
The formula is designed to test whether each of 4000 given strings (A2 to A4000) appear in any of 6 cells (N9 to N13) and, if so, return a reference relative to the cell in which the string occurs (A9 to A13). The formula for the first test (on A2) is:
=IF(ISNUMBER(FIND(A2,Codes!$N$9)),Codes!$A$9,IF(ISNUMBER(FIND(A2,Codes!$N$10)),Codes!$A$10,IF(ISNUMBER(FIND(A2,Codes!$N$11)),Codes!$A$11,IF(ISNUMBER(FIND(A2,Codes!$N$12)),Codes!$A$12,Codes!$A$13))))
Any help gratefully received.
Paul
Posted by Aladin Akyurek on January 04, 2001 10:19 AM
Array-enter
=IF(ISERR(FIND(A2:A4000,Codes!N9:N13),"NoValue",FIND(A2:A4000,Codes!N9:N13))
and copy this as far as needed.
Aladin
Posted by Aladin Akyurek on January 09, 2001 2:54 PM
: Paul
Paul's problem is solved by creating a table of categories out of his source data (which was used in the original approach to construct strings the IF-formula above processed to establish in order to categorize the input). The long and the short is (alas, I have to be brief) the following formula
=IF(AND(A2>=VLOOKUP(A2,CATEGORIES,1),A2<=VLOOKUP(A2,CATEGORIES,2)),VLOOKUP(A2,CATEGORIES,3),"")
does what is desired. Another brainteaser, given the paucity of information.
Aladin
