Excel - find exact string from comma separated list

Question

sorry if this has already been asked, have read many answers about this, (this is the one that best describes my situation - Excel - Match cells that contains exact match from list )

I would like Excel to index a column where the cells contain comma separated lists of values. Some lists have only one entry and thus no comma.

I would like Excel to find an exact match for a given string, for example, if I search for "C2" I would like it only to find "C2" and not "C22" or "C230". It should also find "C2,"

I have got this to work for single cells with this formula :

=ISNUMBER(FIND(" "&E$1&", "; " "&$B1&", "))

Where "C2" is contained in cell E1 and the comma separated list is in cell B1.

However, if I try to incorporate this into an INDEX formula (I would like it to return the corresponding value from the cell in column A where C2 exists), it once again finds all instances of "C2". I wrote the formula as follows :

=INDEX(A:A;ISNUMBER(FIND(" "&E$1&", "; " "&B:B&", ")))

If anyone has any advice on how to get this to work, I would be most grateful!

Katrina

Answer 1

Use the =ISNUMBER(FIND(" "&E$1&", "; " "&$B1&", "))function in an auxiliary column, say F, and pull it down along the comma-separated values in B.

Use =INDEX(A:A;MATCH(TRUE;F:F;0)). This will find the first occurrence of C2 or #NV in cases where C2 does not occur.

Note that the function will find "C2" and "C2, " but not "C2,".

Answer 2

=SUMPRODUCT(ISNUMBER(FIND(" "&E$1&", "; " "&B:B&", "))*A:A)

SUMPRODUCT works great with arrays. The ISNUMBER function returns an array of false/0 and true/1 that is then multiplied with the respective value in column A, returning only that value in A where ISNUMBER is true. If there are several occurrences of the E1 value, the sum of the respective values in A is returned.

For better performance the ranges in A and B should be restricted to those where values are possible, say A1:A100 and B1:B100.