See if any values in array exist in a column (excel)

Question

I have an array of 30 columns and 800 rows (Sheet 1), each cell has either text or is blank. I want to check if ANY values in this array (specificially row by row) exist in a column (Custody List Hedge):

Example:

Sheet 1:

 ASKF    AQA6               
 ARO3    ASKD    AQA5           
 ASKA    ARMA    ARNA    ARMB    ARBA   
 ARMV    ARN6    ARNS    ARO6    AQFS   
 ARQ4    ARMJ    ARN4    ARNJ    ARO4    ARBJ

Custody List Hedge:

MCH Fund Number

BWTZ
AEGF
AEGH
AEGJ
AEGL
AEGM
AEGP

I've tried count, match index, vlookups but nothing seems to really be working. I don't want to use VBA. I know this could be done with loops but there has to be a function set to do this. So far I tried this but I don't think it's working everytime, not sure why...

=INDEX('Sheet 1'!D:AE,MATCH('Custody list - Hedge'!A3,'Custody list - Hedge'!A:A,0),1)

Answer 1

For this, I put the text to search in A1 to F5, and the codes to search for in A11 to A17

First, we need to FIND the text in the string: FIND($A$11:$A$17,A1:F1)

Next, we need to realize we need this needs to be an array formula (else it will only check A11 and A1)
This is done by using CTRL+SHIFT+ENTER when we actually finish construction the formula
This results in a list that looks like #VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1

Now we convert this into something that tells us if the text was found
The values are converted by the ISERROR into TRUE;TRUE;TRUE;TRUE;TRUE;FALSE

Then we swap the values, so we have TRUE if found and FALSE if not, with NOT, which inverts everything to FALSE;FALSE;FALSE;FALSE;FALSE;TRUE

Next we convert those to numbers we can add with --. This is a trick to conver TRUE into 1 and FALSE into 0. We then have 0;0;0;0;0;1

Finally, we SUM the numbers, which will then tell us how many words in the list were found in the string you are searching

This will build a final formula that looks like this:

{=SUM(--(NOT(ISERROR(FIND($A$11:$A$17,A1:F1)))))}

(The curly brackets, {} will let you know you entered an array formula)

You can then test this against 0 for nothing found, and >0 for some words found