Alternative to nested If

[Mansel]

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")))))))))))))))))))))))

 

[Joe4]

Duplicate to: Alternative to nested IF Formula

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.