Forum Discussion
More then 64 levels of nesting need a simpler formula
Hello All,
I'm new to excel and just bumped into the max nesting . Can anyone please help me to simplify the formula or help me find a way around it please?
I need the IF Formula to run a search value from A2 to A36 and from C2 to C36 and if true assign value on the cell next to it. I've done the formula here but u cant use it since is too big. 😞
I can I work around this?
Thank you in advance
=IF(B2>0;IF(ISNUMBER(SEARCH(Recap!$A$2;N2));Recap!$B$2;IF(ISNUMBER(SEARCH(Recap!$A$3;N2));Recap!$B$3;IF(ISNUMBER(SEARCH(Recap!$A$4;N2));Recap!$B$4;IF(ISNUMBER(SEARCH(Recap!$A$5;N2));Recap!$B$5;IF(ISNUMBER(SEARCH(Recap!$A$6;N2));Recap!$B$6;IF(ISNUMBER(SEARCH(Recap!$A$7;N2));Recap!$B$7;IF(ISNUMBER(SEARCH(Recap!$A$8;N2));Recap!$B$8;IF(ISNUMBER(SEARCH(Recap!$A$9;N2));Recap!$B$9;IF(ISNUMBER(SEARCH(Recap!$A$10;N2));Recap!$B$10;IF(ISNUMBER(SEARCH(Recap!$A$11;N2));Recap!$B$11;IF(ISNUMBER(SEARCH(Recap!$A$12;N2));Recap!$B$12;IF(ISNUMBER(SEARCH(Recap!$A$13;N2));Recap!$B$13;IF(ISNUMBER(SEARCH(Recap!$A$14;N2));Recap!$B$14;IF(ISNUMBER(SEARCH(Recap!$A$15;N2));Recap!$B$15;IF(ISNUMBER(SEARCH(Recap!$A$16;N2));Recap!$B$16;IF(ISNUMBER(SEARCH(Recap!$A$17;N2));Recap!$B$17;IF(ISNUMBER(SEARCH(Recap!$A$18;N2));Recap!$B$18;IF(ISNUMBER(SEARCH(Recap!$A$19;N2));Recap!$B$19;IF(ISNUMBER(SEARCH(Recap!$A$20;N2));Recap!$B$20;IF(ISNUMBER(SEARCH(Recap!$A$21;N2));Recap!$B$21;IF(ISNUMBER(SEARCH(Recap!$A$22;N2));Recap!$B$22;IF(ISNUMBER(SEARCH(Recap!$A$23;N2));Recap!$B$23;IF(ISNUMBER(SEARCH(Recap!$A$24;N2));Recap!$B$24;IF(ISNUMBER(SEARCH(Recap!$A$25;N2));Recap!$B$25;IF(ISNUMBER(SEARCH(Recap!$A$26;N2));Recap!$B$26;IF(ISNUMBER(SEARCH(Recap!$A$27;N2));Recap!$B$27;IF(ISNUMBER(SEARCH(Recap!$A$28;N2));Recap!$B$28;IF(ISNUMBER(SEARCH(Recap!$A$29;N2));Recap!$B$29;IF(ISNUMBER(SEARCH(Recap!$A$30;N2));Recap!$B$30;IF(ISNUMBER(SEARCH(Recap!$A$31;N2));Recap!$B$31;IF(ISNUMBER(SEARCH(Recap!$A$32;N2));Recap!$B$32;IF(ISNUMBER(SEARCH(Recap!$A$33;N2));Recap!$B$33;IF(ISNUMBER(SEARCH(Recap!$A$34;N2));Recap!$B$34;IF(ISNUMBER(SEARCH(Recap!$A$35;N2));Recap!$B$35;IF(ISNUMBER(SEARCH(Recap!$A$36;N2));Recap!$B$36;IF(ISNUMBER(SEARCH(Recap!$C$2;N2));Recap!$D$2;IF(ISNUMBER(SEARCH(Recap!$A$3;N2));Recap!$D$3;IF(ISNUMBER(SEARCH(Recap!$C$4;N2));Recap!$D$4;IF(ISNUMBER(SEARCH(Recap!$C$5;N2));Recap!$D$5;IF(ISNUMBER(SEARCH(Recap!$C$6;N2));Recap!$D$6;IF(ISNUMBER(SEARCH(Recap!$C$7;N2));Recap!$D$7;IF(ISNUMBER(SEARCH(Recap!$C$8;N2));Recap!$D$8;IF(ISNUMBER(SEARCH(Recap!$C$9;N2));Recap!$D$9;IF(ISNUMBER(SEARCH(Recap!$C$10;N2));Recap!$D$10;IF(ISNUMBER(SEARCH(Recap!$C$11;N2));Recap!$D$11;IF(ISNUMBER(SEARCH(Recap!$C$12;N2));Recap!$D$12;IF(ISNUMBER(SEARCH(Recap!$A$13;N2));Recap!$D$13;IF(ISNUMBER(SEARCH(Recap!$C$14;N2));Recap!$D$14;IF(ISNUMBER(SEARCH(Recap!$C$15;N2));Recap!$D$15;IF(ISNUMBER(SEARCH(Recap!$C$16;N2));Recap!$D$16;IF(ISNUMBER(SEARCH(Recap!$C$17;N2));Recap!$D$17;IF(ISNUMBER(SEARCH(Recap!$C$18;N2));Recap!$D$18;IF(ISNUMBER(SEARCH(Recap!$C$19;N2));Recap!$D$19;IF(ISNUMBER(SEARCH(Recap!$C$20;N2));Recap!$D$20;IF(ISNUMBER(SEARCH(Recap!$C$21;N2));Recap!$D$21;IF(ISNUMBER(SEARCH(Recap!$C$22;N2));Recap!$D$22;IF(ISNUMBER(SEARCH(Recap!$C$23;N2));Recap!$D$23;IF(ISNUMBER(SEARCH(Recap!$C$24;N2));Recap!$D$24;IF(ISNUMBER(SEARCH(Recap!$C$25;N2));Recap!$D$25;IF(ISNUMBER(SEARCH(Recap!$C$26;N2));Recap!$D$26;IF(ISNUMBER(SEARCH(Recap!$C$27;N2));Recap!$D$27;IF(ISNUMBER(SEARCH(Recap!$C$28;N2));Recap!$D$28;IF(ISNUMBER(SEARCH(Recap!$C$29;N2));Recap!$D$29;IF(ISNUMBER(SEARCH(Recap!$C$30;N2));Recap!$D$30;IF(ISNUMBER(SEARCH(Recap!$C$31;N2));Recap!$D$31;IF(ISNUMBER(SEARCH(Recap!$C$32;N2));Recap!$D$32;IF(ISNUMBER(SEARCH(Recap!$C$33;N2));Recap!$D$33;IF(ISNUMBER(SEARCH(Recap!$C$34;N2));Recap!$D$34;IF(ISNUMBER(SEARCH(Recap!$C$35;N2));Recap!$D$35;IF(ISNUMBER(SEARCH(Recap!$C$36;N2));Recap!$D$36; « False »)
6 Replies
Everything in Excel could be done by several ways. If at first you explain your logic, illustrate it with small sample (with manually added desired result if possible), mention on which Excel version/platform you are that will be much easier and faster to make concrete suggestion.
In general you did such way, but it's not clear you search for entire value and text as part in another text, are all values texts or some numbers, which Excel do you use.
Above as a comment, I vote for Juliano-Petrukio suggestion. It could be more concrete with more concrete sample.
Hello Sergei, Thank you for this precisions:
The Version I use is Excel 2016 in Windows 10 (1607)
The logic I'm searching is:
I have a table of values that associate with certain users - eg If "RED" user "John"
After that I have an extract that I need to run through for this data
eg. the value color comes out in column "N" and I must assign user in column "S" but the assignment needs to respect the row
If "N1" the cell contains the word "RED" the cell "S1" needs to be populated with "John" .The suggestion of Juliano, can I keep the matching row condition?
I've redone a new sample file
Hope this is more clear.Thank you for you help and time
Click on the blue button in cell H1 of the attached sheet. Maybe this is what you want to do.
What about:
=INDEX(ColumnContainingDesiredValues, MATCH(YourCriteria,ColumnContainingDataAsPerYourCriteria,0),1)
