Keyword search within a single cell mixed with vlookup

I have a function that appears to be working for the most part, but I want to change the output in the event that Excel does not find a true statement for me.

In the attached test worksheet, I would like to search column D for keywords like "Highway" or "Planning" or "Operations" and have column D and E automatically update with corresponding industry and focus information. If that information is not available in Column D, I would like to have Excel vlookup Column C and find the corresponding information in the lookup array in the worksheet called 'Toggles' .
The function I created seems to work, but I don't want '0' entries if excel doesn't find the info it is looking for. Rather, I would like it to report 'Not Defined'.

Thanks in advance for your help.

Originally Posted by martindwilson

if(vlookup=0,"not defined",vlookup)

Hi,

Thanks for your prompt reply.

Can you elaborate a bit on where I would insert this part of the statement into the function?

Here is the function as it currently exists:
=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($AQ$34:$AQ$67,F6)),$AR$34:$AR$67),IFERROR(VLOOKUP(E6,Toggles!$S:$Y,4,FALSE),"Not Defined"))

thx

Originally Posted by ndube50

Hi,

Thanks for your prompt reply.

Can you elaborate a bit on where I would insert this part of the statement into the function?

Here is the function as it currently exists:
=IFERROR(LOOKUP(2,1/ISNUMBER(SEARCH($AQ$34:$AQ$67,F6)),$AR$34:$AR$67),IFERROR(VLOOKUP(E6,Toggles!$S:$Y,4,FALSE),"Not Defined"))

thx

I may have forgotten to include the attachment.
If so, here it is again.