Dec 10 2021 02:10 AM
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 »)
Dec 10 2021 02:38 AM - edited Dec 10 2021 02:39 AM
What about:
=INDEX(ColumnContainingDesiredValues,
MATCH(YourCriteria,ColumnContainingDataAsPerYourCriteria,0),1)
Dec 10 2021 05:39 AM
Click on the blue button in cell H1 of the attached sheet. Maybe this is what you want to do.
Dec 10 2021 05:51 AM
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.
Dec 13 2021 03:42 AM
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
Dec 13 2021 04:56 AM
SolutionDec 13 2021 08:09 AM
Dec 13 2021 04:56 AM
Solution=IFERROR(
INDEX($Y$2:$Y$4,MATCH(N2,$Z$2:$Z$4,0),1),"Not found")
Find attached