CODEWITHSUNDEEP
excelindexingmatchvlookup
anjaryes
anjaryes

Reputation: 111

How To Return Multiple Match Values from A Cell That Contains A Word/Text in Excel

I have a bunch of data that consist of Id Number and Names.

Image 1

enter image description here

{=IF(ISERROR(INDEX($A$2:$B$12;SMALL(IF($A$1:$A$16=$E$1;ROW($A$1:$A$12));ROW(1:1));2));"";INDEX($A$1:$B$12;SMALL(IF($A$1:$A$16=$E$1;ROW($A$1:$A$12));ROW(1:1));2))}

Previously (Image 1) I successfully able to return multiple value with A Name value from column Names that consist of only one name. I am using Index function Array formula to solve this problem.

But,I got stuck when I have multiple names in that Names column. What I want to do is to return multiple value of Id Number that consist of a multiple names separated by 'comma' inside Names column without modifying that column. Expected result is shown in Image 2.

Image 2

enter image description here

The problems are :

  1. I want to get the value from the ID Number column based on Names column that contain a name inside
  2. I want to automatically get multiple values just like displayed in Image 1
  3. If there're two ID Number value that is the same, it will be deleted or not be shown in the result.

I don't mind any kind of method you guys will purposed to me. I will appreciate any solutions you offered. Thank you very much.

Upvotes: 0

Views: 2298

Answers (1)

Tom Sharpe
Tom Sharpe

Reputation: 34380

You can use 

=IFERROR(INDEX($A:$A,SMALL(IF(ISNUMBER(FIND(E$1,$B$2:$B$11))*(COUNTIF(E$1:E1,$A$2:$A$11)=0),ROW($B$2:$B$11)),1)),"")

entered as an array formula using CtrlShiftEnter

or

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($B$2:$B$11)/(ISNUMBER(FIND(E$1,$B$2:$B$11))*(COUNTIF(E$1:E1,$A$2:$A$11)=0)),1)),"")

entered normally.

enter image description here

EDIT

@Ron Rosenfeld is absolutely correct that the formulas as they stand would match (for example) Jo as well as John, although the effect is mitigated somewhat by the fact that they are using case-sensitive find with a capital letter at the beginning of each name (so Ange wouldn't match Hanger).

The modified formulas would be

=IFERROR(INDEX($A:$A,SMALL(IF(ISNUMBER(FIND(","&E$1&",",","&$B$2:$B$11&","))*(COUNTIF(E$1:E1,$A$2:$A$11)=0),ROW($B$2:$B$11)),1)),"")

and

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($B$2:$B$11)/(ISNUMBER(FIND(","&E$1&",",","&$B$2:$B$11&","))*(COUNTIF(E$1:E1,$A$2:$A$11)=0)),1)),"")

Upvotes: 1

Related Questions