Excel Spreadsheet - One to Many matching excercise-CodePudding

Basically from the picture linked here, what I'm trying to do is to look for a formula that can compare the value of the second row, with the values of the first column. If it finds that, for example, the first cell has the letter "A" in it then that row cell for A would get a yes, otherwise it would remain blank

Is this possible in excel?

CodePudding user response：

Use ISNUMBER() & SEARCH() within an IF()

• Formula used in cell B3

=IF(ISNUMBER(SEARCH(B$2,$A3)),"Yes","")

Fill Down & Fill Right!!!

You can also use the formula as shown below, and showing the cells as Yes using a custom formatting though it will be a façade, still.

• Formula used in cell B3

=--ISNUMBER(SEARCH(B$2,$A3))

And Fill down & fill right, next select all the cells, where you applied the formula, Press CTRL 1 to open Format Cells Dialogue, Number Tab, Category will be Custom and type by removing the General,

[=1]"Yes";;

So the above Custom Formatting turns the cells which returns 1 to Yes while the 0's as blank.

CodePudding user response：

XMATCH() also can be considered with wild card character matching mode..

=IFNA(XMATCH("*"&B$2&"*",$A3,2),"")