Add nested condition to existing excel formula

WirelessJoe

New Member
Joined
Jan 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I'm relatively new to more complex nested formulas in Excel (Windows, 2016) and would appreciate some assistance. I’m trying to add a condition to an existing formula that currently performs vlookups based on the text of another cell (H3); the new condition that says if cell H2 contains the letter “S” anywhere in the text (denoting a specific job role that should not result in a value in the lookup tables) then the result should say “Enter Manually” and skip all the other lookups.

The current, working formula for the lookups is =IFERROR(IF(H3="Analytics Family Group",VLOOKUP(J64,'2021 Data Lookups'!AB:AC,2,FALSE),VLOOKUP(J64,'2021 Data Lookups'!U:V,2,FALSE)),"")

The formulas I’ve tried to build looks something like this:

=IFERROR(IF(ISNUMBER(search("S",H2,)),"Enter Manually",((H3="Analytics Family Group",VLOOKUP(J64,'2021 Data Lookups'!AB:AC,2,FALSE),VLOOKUP(J64,'2021 Data Lookups'!U:V,2,FALSE)),"")))

or

=IFERROR(ISNUMBER(search("S",H2))"Enter Manually",IFERROR(H3="Analytics Family Group",(VLOOKUP(J64,'2021 Data Lookups'!AB:AC,2,FALSE),VLOOKUP(J64,'2021 Data Lookups'!U:V,2,FALSE)),""))

Of course these result in errors; I believe the first one is the closest to being correct, but I've not been able to format it properly by adjusting the parentheses. I'd appreciate help in either correcting my current nested mess, or a novel, working formula. Thank you!
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,029
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
Excel Formula:
=IFERROR(IF(ISNUMBER(SEARCH("S",H2)),"Enter Manually",IF(H3="Analytics Family Group",VLOOKUP(J64,'2021 Data Lookups'!AB:AC,2,FALSE),VLOOKUP(J64,'2021 Data Lookups'!U:V,2,FALSE))),"")
 
Solution

WirelessJoe

New Member
Joined
Jan 7, 2021
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Yes, that worked perfectly. I really appreciate you taking the time to look at what probably is such a simple question.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,029
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

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
Top