If any text within a range matches text within a cell, output matching text

Question

I need to see if any text in the range C1:D3 matches any text in A1. If it does, output that text to B1. If it doesn't, output nothing in B1.

Using the data from the list below, I've successfully used this array formula when the lookup range is in one column C1:C3, but can't get it to work when spreading the lookup across two columns from C1:D3.

This worked when searching across one column: Column C

{=IFERROR(INDEX($C$1:$C$3,MATCH(1,COUNTIF(A1,"*"&$C$1:$C$3&"*"),0)),"")}

Changed it it this to search across two columns - didn't work: Columns C & D

{=IFERROR(INDEX($C$1:$D$3,MATCH(1,COUNTIF(A1,"*"&$C$1:$D$3&"*"),0)),"")}

Data

• C1,2,3 contains: new york, los angeles, san diego
• D1,2,3 contains: toronto, jacksonville, nyc
• A1,2,3,4,5,6 contains: birth certificates new york, los angeles death certificates, marriage certificates, san diego divorce certificates, marriage certificates, divorce jacksonville
• B1 contains the formula, then drag it down through to B6

Any ideas how to do this?

Answer 1

This can be accomplished by nesting your IFERROR functions to pass the lookup into the second column if the first does not produce a match but I suspect that your sample data does not adequately describe the scope of C1:D3. For your sample data this would be accomplished with the following array formula in B1.

 =IFERROR(INDEX($C$1:$C$3,MATCH(1,COUNTIF(A1,"*"&$C$1:$C$3&"*"),0)),IFERROR(INDEX($D$1:$D$3,MATCH(1,COUNTIF(A1,"*"&$D$1:$D$3&"*"),0)),""))

Array formulas need to be finalized with Ctrl+Shift+Enter↵. once entered correctly you can fill down as necessary.

        

If the scope of C1:D3 is significantly different from you've described, other methods may be more appropriate; including transposing the lookup to a horizontal one if you actually had more columns than rows.