CODEWITHSUNDEEP
excelexcel-formula
Brian Imburg
Brian Imburg

Reputation: 1

Looking up a value in a cell with multiple entries based off of a table

I have a sheet that has cells in it with multiple lines in each cell:

Cell A1 has

value A
value B
value C
value D

Cell A2 has

value B
value C
value D

Cell A3 has

value E

The table is on a different sheet and has the name COMP_VAL

value A
value E

I need to put an X in the Associated B column for each row that has a match in the table. Right now I have an IF with an OR and an IFERROR formula that looks like this

=IF(OR(IFERROR(FIND(COMP_VAL!$A$1,A1),0),IFERROR(FIND(COMP_VAL!$A$2,A2),"X","")

So I will get an X in B1, no X in B2 and an X in B3.

Is there a better way to do it?

Upvotes: 0

Views: 44

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use:

=IF(SUM(IFERROR(SEARCH(COMP_VAL!$A$1:$A$2,A1),0)),"X","")

Depending on one's version of Excel this may need to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

Then drag down.

enter image description here

Upvotes: 1

Related Questions