Pulling values by matching all the entries in a single cell

[msharma864512]

Hi,

I am looking for a formula to pull all the values in a single cell matching from all the entries in a single cell.

For e.g.

Sector: Oil & GAS; Consumer Products & Retail, then in the case, using index match or any other look up function, it should pull Energy, CPR in the corresponding column cell.

Details are in the attached spreadsheet.

Regards,
Manish

 

[shrivallabha]

If sequence is not of importance then you can use.
=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH('Sector Mapping'!$A$2:$A$18,Sheet1!A4,1)),'Sector Mapping'!$B$2:$B$18,""))

 

[bosco_yip]

Or, if you wish to follow your criteria sequence order, try this longer formula.

In B4, copied down :

=IF(ISERR(FIND(";",A4)),VLOOKUP(A4,'Sector Mapping'!$A$2:$B$18,2,0),TEXTJOIN(", ",1,INDEX('Sector Mapping'!$B$2:$B$18,N(IF(1,MATCH(TRIM(MID(SUBSTITUTE(";"&$A4,";",REPT(" ",50)),{50,99},50)),'Sector Mapping'!$A$2:$A$18,0))))))




Regards

 

[msharma864512]

Or, if you wish to follow your criteria sequence order, try this longer formula.

In B4, copied down :

=IF(ISERR(FIND(";",A4)),VLOOKUP(A4,'Sector Mapping'!$A$2:$B$18,2,0),TEXTJOIN(", ",1,INDEX('Sector Mapping'!$B$2:$B$18,N(IF(1,MATCH(TRIM(MID(SUBSTITUTE(";"&$A4,";",REPT(" ",50)),{50,99},50)),'Sector Mapping'!$A$2:$A$18,0))))))


View attachment 67209

Regards

Thank you Bosco, The formula is great, however its only pulling two values, however in my database, there could be more than 2 value, or it could be "n" values as well, hence it should pull all the values matching the keyword

 

[bosco_yip]

Should your criteria cell content more than 2 values, then try this array formula

In B4, array formula (Ctrl+Shift+Enter) copied down :

=TEXTJOIN(", ",1,INDEX('Sector Mapping'!$B$2:$B$18,N(IF(1,MATCH(TRIM(MID(SUBSTITUTE(";"&$A4,";",REPT(" ",150)),ROW(INDIRECT("1:"&(LEN(A4)-LEN(SUBSTITUTE(A4,";",""))+1)))*150,150)),'Sector Mapping'!$A$2:$A$18,0)))))



Regards

 

[msharma864512]

Should your criteria cell content more than 2 values, then try this array formula

In B4, array formula (Ctrl+Shift+Enter) copied down :

=TEXTJOIN(", ",1,INDEX('Sector Mapping'!$B$2:$B$18,N(IF(1,MATCH(TRIM(MID(SUBSTITUTE(";"&$A4,";",REPT(" ",150)),ROW(INDIRECT("1:"&(LEN(A4)-LEN(SUBSTITUTE(A4,";",""))+1)))*150,150)),'Sector Mapping'!$A$2:$A$18,0)))))

View attachment 67211

Regards

This really amazing, Thank you very much Bosco and Shrivallabha

 

[msharma864512]

This really amazing, Thank you very much Bosco and Shrivallabha

Hi Bosco and Shrivallabha,

This was really a great formula and it really helped me a lot, now I am looking for more advanced formula, wherein I want the text to be searched which is within the parenthesis, and the sector could be more than two, and it should return the value by matching all the text within the parenthesis.

Details are in the attachment.