Alternative to nested IF Formula

Mansel

New Member
Joined
Nov 17, 2006
Messages
41
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1Keyword to search forResult to display if foundDescription as supplied to meNested IF being used
2OwnerOwner/FounderHR Operations and Rewards Manager Spain&PortugalHR
3Co FounderOwner/FounderHR Operations and Rewards Manager Spain&PortugalHR
4DirectorDirectorVP Operations, IT, Organisation & HR MarketingHR
5ChiefSenior ManagementMarketing and Sales Operations DirectorDirector
6HRHRDirector, Digital Marketing & Operations - EMEADirector
7PersonnelHRDirector, Digital Marketing & Operations - EMEADirector
8PeopleHRDirector, Digital Marketing & Operations - EMEADirector
9HeadHeadGlobal Operational Marketing ManagerMarketing
10ExecutiveExecutiveGlobal Operational Marketing ManagerMarketing
11OperationsOperationsMarketing Operational Specialist SwedenMarketing
12MarketingMarketingMarketing Operational Specialist SwedenMarketing
13ManagerManagerBid Manager/Sales OperationsOperations
14Account ManagerAccount ManagerBid Manager/Sales OperationsOperations
15FinanceFinanceFMCG Field Operations Manager \ Sales ManagerOperations
16SalesSalesGroup Sales Operations DirectorDirector
17TechnicalTechnicalInfrastructure Manager - Sales & Operations Operations
18EngineerTechnicalManager Sales & Operations PlanningOperations
19AdministratorAdministratorRegional Sales Operations ManagerOperations
20ChefSenior ManagementRemote Sales Operations - SMB ChannelOperations
21ProjectProjectsSales and Operations Director for North AmericaDirector
22DigitalDigitalSales and Operations Director for North AmericaDirector
23EcommerceDigitalSales and Operations LeaderOperations
24AccountingFinanceSales Operations Line ManagerOperations
25
26Current 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
RangeFormula
D2:D24D2=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")))))))))))))))))))))))
 
That's cracked it! You are a star sir! Am I able to offer and send you the price of a coffee/pint?
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top