2

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

Improve this question
2
  • Is there only one occurrence of C4 in column B? Commented Aug 6, 2016 at 22:54
  • Yes, each value should only exist once within the entire column of comma separated lists Commented Aug 7, 2016 at 19:49

2 Answers 2

Reset to default
0

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,".

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you very much, this did work, however it is not pratical to have an auxiliary column for each value, since there are hundreds of them. What I need to do is combine the two formulas ... I did find this solution : [code]{=IFERROR(ADDRESS(LARGE(ISNUMBER(SEARCH($E1;$B$1:$B$5))*ROW($B$1:$B$5);COLUMNS($A:A));1);"")}[/code] However I need to extract the contents of the cell, instead of the formula just returning the cell reference (which is what ADDRESS does). So far my attempts to mate this formula with INDEX and / or MATCH have failed .. do you have any tips?
Can't believe one can only edit comments for 5 minutes after they are posted ... sorry for incorrect [code] tags, I can't now delete them ...
0

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

Improve this answer

3 Comments

Thank you for your answer, sorry I haven't been able to reply sooner! I got a VALUE error when I tried the SUMPRODUCT formula ... I will look into this more in the coming days, thank you for your help!
This is what works for me: fs5.directupload.net/images/160812/zqwc9ieu.png
Thanks, I have saved your picture, I will try again and let you know what happens!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.