CODEWITHSUNDEEP
excel
Masato Indou
Masato Indou

Reputation: 5

Excel Spreadsheet - One to Many matching excercise

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?

enter image description here

Upvotes: 0

Views: 67

Answers (2)

Harun24hr
Harun24hr

Reputation: 36965

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

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

enter image description here

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27408

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

FORMULA_SOLUTION

• 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_SOLUTION

• 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.

Upvotes: 1

Related Questions