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