If any text within the range matches the text inside the cell, output the corresponding text
I need to figure out if any text in the range C1: D3 matches any text in A1. If so, output this text to B1. If it is not, do not output anything to B1.
Using the data from the list below, I have successfully used this array formula when the search range is in one column C1: C3, but cannot get it to work when propagating the search across two columns from C1: D3.
This worked when searching in one column: Column C
{=IFERROR(INDEX($C$1:$C$3,MATCH(1,COUNTIF(A1,"*"&$C$1:$C$3&"*"),0)),"")}
Changed this to search across two columns - doesn't work: Columns C and 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 in New York, death certificates in Los Angeles, marriage certificates, divorce certificates in San Diego, marriage certificates, jacksonville divorce.
- B1 contains the formula, then drag it to B6
Any ideas how to do this?
source to share
This can be done by inserting an IFERROR function to pass the search to the second column if the first does not match, but I am that your sample data does not adequately describe the volume of C1: D3. For your sample data, this will 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 must be completed with Ctrl+ Shift+ Enter↵. once entered correctly, you can fill in as needed.
If the scale of C1: D3 is significantly different from what you described, other methods may be more appropriate; including moving your search to horizontal if you actually have more columns than rows.
source to share