Add nested condition to existing excel formula

[WirelessJoe]

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!

 

[Fluff]

Hi & welcome to MrExcel.
How about

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

 

[WirelessJoe]

Yes, that worked perfectly. I really appreciate you taking the time to look at what probably is such a simple question.

 

[Fluff]

You're welcome & thanks for the feedback.