Search function with multiple dynamic named ranges

How can I write up a formula that utilizes search referencing two named dynamic ranges.


Here are the base formulas I found from another site:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

The dynamic named range formula I use are:

Please Login or Register  to view this content.

as named range "range_list1"
and

Please Login or Register  to view this content.

as named range "range_list2"


I thought I could replace the range in the base formulas with just one named range for test purposes but I get a zero.

Please Login or Register  to view this content.

Hi oeldere,

I attached a sheet with examples. Hope this helps.
SearchWithDynamiceRange.xlsx

The result is 1 because it determines if the criteria is found in the range. If it counts any of this greater than 0, the criteria exists in the range. If it is 0, it leaves the cell blank. I am first trying to figure out how to use criteria 1 or criteria 2 for one range. If criteria 1 or criteria 2 exists in range 1, then the result is 1. Later I would like for this to index match the criteria to a letter-number code in a column adjacent to the criteria columns. But before I can come to this approach, I am trying to figure out how to use criteria 1 or criteria two for one range.

Instead of searching

Please Login or Register  to view this content.

where the range is $A$1:$A$3, I would like to replace this with two named dynamic ranges within the same formula.

I'm trying to find criteria that composes of two dynamic named ranges, that if any of those criteria are found within a string of text in a cell, it returns a 1. Otherwise a blank. Not sure if it's the lack of sleep that I'm not making sense Hope this helps.