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