Alternative to nested IF Formula

[Mansel]

Hi All,

I wonder if anyone is able to offer an alternative to this nested if formula. The actual spreadsheet I am supplied with has 2,500 rows so this would never work, but thought it the best way to display what it is I am trying to achieve. Many thanks for any help offered.

Test Formula 3.xlsx
	A	B	C	D	E	F	G
1	Keyword to search for	Result to display if found	Description as supplied to me	Nested IF being used
2	Owner	Owner/Founder	HR Operations and Rewards Manager Spain&Portugal	HR
3	Co Founder	Owner/Founder	HR Operations and Rewards Manager Spain&Portugal	HR
4	Director	Director	VP Operations, IT, Organisation & HR Marketing	HR
5	Chief	Senior Management	Marketing and Sales Operations Director	Director
6	HR	HR	Director, Digital Marketing & Operations - EMEA	Director
7	Personnel	HR	Director, Digital Marketing & Operations - EMEA	Director
8	People	HR	Director, Digital Marketing & Operations - EMEA	Director
9	Head	Head	Global Operational Marketing Manager	Marketing
10	Executive	Executive	Global Operational Marketing Manager	Marketing
11	Operations	Operations	Marketing Operational Specialist Sweden	Marketing
12	Marketing	Marketing	Marketing Operational Specialist Sweden	Marketing
13	Manager	Manager	Bid Manager/Sales Operations	Operations
14	Account Manager	Account Manager	Bid Manager/Sales Operations	Operations
15	Finance	Finance	FMCG Field Operations Manager \ Sales Manager	Operations
16	Sales	Sales	Group Sales Operations Director	Director
17	Technical	Technical	Infrastructure Manager - Sales & Operations	Operations
18	Engineer	Technical	Manager Sales & Operations Planning	Operations
19	Administrator	Administrator	Regional Sales Operations Manager	Operations
20	Chef	Senior Management	Remote Sales Operations - SMB Channel	Operations
21	Project	Projects	Sales and Operations Director for North America	Director
22	Digital	Digital	Sales and Operations Director for North America	Director
23	Ecommerce	Digital	Sales and Operations Leader	Operations
24	Accounting	Finance	Sales Operations Line Manager	Operations
25
26	Current Formula!
27	=IF(ISNUMBER(SEARCH($A$2,C2)),$B$2,IF(ISNUMBER(SEARCH($A$3,C2)),$B$3,IF(ISNUMBER(SEARCH($A$4,C2)),$B$4,IF(ISNUMBER(SEARCH($A$5,C2)),$B$5, IF(ISNUMBER(SEARCH($A$6,C2)),$B$6,IF(ISNUMBER(SEARCH($A$7,C2)),$B$7,IF(ISNUMBER(SEARCH($A$8,C2)),$B$8,IF(ISNUMBER(SEARCH($A$9,C2)),$B$9, IF(ISNUMBER(SEARCH($A$10,C2)),$B$10,IF(ISNUMBER(SEARCH($A$11,C2)),$B$11,IF(ISNUMBER(SEARCH($A$12,C2)),$B$12,IF(ISNUMBER(SEARCH($A$13,C2)),$B$13, IF(ISNUMBER(SEARCH($A$14,C2)),$B$14,IF(ISNUMBER(SEARCH($A$15,C2)),$B$15,IF(ISNUMBER(SEARCH($A$16,C2)),$B$16,IF(ISNUMBER(SEARCH($A$17,C2)),$B$17, IF(ISNUMBER(SEARCH($A$18,C2)),$B$18,IF(ISNUMBER(SEARCH($A$19,C2)),$B$19,IF(ISNUMBER(SEARCH($A$20,C2)),$B$20,IF(ISNUMBER(SEARCH($A$21,C2)),$B$21, IF(ISNUMBER(SEARCH($A$22,C2)),$B$22,IF(ISNUMBER(SEARCH($A$23,C2)),$B$23,IF(ISNUMBER(SEARCH($A$24,C2)),$B$24,"No Result")))))))))))))))))))))))
28
29
30
31
32
33
34
35
Nested IFs

Cell Formulas
Range	Formula
D2:D24	D2	=IF(ISNUMBER(SEARCH($A$2,C2)),$B$2,IF(ISNUMBER(SEARCH($A$3,C2)),$B$3,IF(ISNUMBER(SEARCH($A$4,C2)),$B$4,IF(ISNUMBER(SEARCH($A$5,C2)),$B$5,IF(ISNUMBER(SEARCH($A$6,C2)),$B$6,IF(ISNUMBER(SEARCH($A$7,C2)),$B$7,IF(ISNUMBER(SEARCH($A$8,C2)),$B$8,IF(ISNUMBER(SEARCH($A$9,C2)),$B$9,IF(ISNUMBER(SEARCH($A$10,C2)),$B$10,IF(ISNUMBER(SEARCH($A$11,C2)),$B$11,IF(ISNUMBER(SEARCH($A$12,C2)),$B$12,IF(ISNUMBER(SEARCH($A$13,C2)),$B$13,IF(ISNUMBER(SEARCH($A$14,C2)),$B$14,IF(ISNUMBER(SEARCH($A$15,C2)),$B$15,IF(ISNUMBER(SEARCH($A$16,C2)),$B$16,IF(ISNUMBER(SEARCH($A$17,C2)),$B$17,IF(ISNUMBER(SEARCH($A$18,C2)),$B$18,IF(ISNUMBER(SEARCH($A$19,C2)),$B$19,IF(ISNUMBER(SEARCH($A$20,C2)),$B$20,IF(ISNUMBER(SEARCH($A$21,C2)),$B$21,IF(ISNUMBER(SEARCH($A$22,C2)),$B$22,IF(ISNUMBER(SEARCH($A$23,C2)),$B$23,IF(ISNUMBER(SEARCH($A$24,C2)),$B$24,"No Result")))))))))))))))))))))))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

Thanks for updating your profile.
How about

=TAKE(FILTER($A$2:$A$24,ISNUMBER(SEARCH($A$2:$A$24,C2))),1)

 

[Mansel]

Hi Fluff,

Many thanks for the suggestion, will give it a go, not heard of the take/filter before!

 

[Mansel]

Hi Fluff,

This appears to work perfectly, thank you. 2 questions if I may? is there a limit on the number of rows for the Take/Filter combination? and is there a way to add in "Not Found" to replace the #CALC if no keyword is found? Really Appreciate your help.

 

[Fluff]

No there is no limit to the range & you can add the not found like

=TAKE(FILTER($A$2:$A$1000,ISNUMBER(SEARCH($A$2:$A$1000,C2)),"Not found"),1)

 

[Mansel]

Perfect, Thank you so much, really appreciate you taking the time to help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Mansel]

Hi Fluff,

On testing I noticed, if the keyword can not be found in the C column the formula returns 0 instead of Not Found but if its Blank in the C Column it returns the correct Not Found message. Any ideas?

Test Formula 4 Using Take-Filter.xlsx
	A	B	C	D
2	Owner	Owner/Founder	dfgsfg gss sfdgsdfg sg sdf gsg sdg sdf	0
3	Co Founder	Owner/Founder		Not found
Take-Filter Formula

Cell Formulas
Range	Formula
D2:D3	D2	=TAKE(FILTER($B$2:$B$1000,ISNUMBER(SEARCH($A$2:$A$1000,C2)),"Not found"),1)

 

[Fluff]

Ok, try

=TAKE(FILTER($A$2:$A$1000,(ISNUMBER(SEARCH($A$2:$A$1000,C2)))*($A$2:$A$1000<>""),"Not found"),1)