Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell

[MJD5241]

**XCEL FILE ATTACHED**

Question: If I have a workbook with 2 tables; 1 has the data that I want to search and 1 is empty and where I would like the "searched" values output, what lookup formula would I need to use?.

• The attached file is a list of companies in one column and the states in which they have locations in the adjacent column

• Each company has multiple states where they have locations (For each company, these are separated by semi-colons For Ex: TX; CA; MA; NY)

• I want to search, for ex, MA and have it output all the different companies that have MA locations

THIS IS THE TABLE I WANT TO SEARCH:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Companies[/TD]
[TD]State Locations[/TD]
[/TR]
[TR]
[TD]Company 1[/TD]
[TD][TABLE="width: 313"]
<tbody>[TR]
[TD="class: xl67, width: 313"]TX; CA; AZ; IL; VA; OH; MI; MX; Canada[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Company 2[/TD]
[TD][TABLE="width: 313"]
<tbody>[TR]
[TD="class: xl67, width: 313"]IL; MA; PR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Company 3[/TD]
[TD][TABLE="width: 313"]
<tbody>[TR]
[TD="class: xl67, width: 313"]AL; AK ; AZ; AR; CA; CO; CT; DC; DE; FL; GA; HI; ID; IL; IN; IA; KS; KY; LA; ME; MD; MA; MI; MN; MS; MO; MT; NE; NV; NH; NJ; NM; NY; NC; ND; OH; OK; OR; PA; RI; SC; SD; TN; TX; UT; VT; VA; WA; WV; WI; WY[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]Company 4[/TD]
[TD][TABLE="width: 313"]
<tbody>[TR]
[TD="class: xl67, width: 313"]CA; TX; PA; NJ; Italy[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


THIS IS THE TABLE I WANT TO OUTPUT TO:

[TABLE="width: 500"]
<tbody>[TR]
[TD]MA[/TD]
[TD]CA[/TD]
[TD]NY[/TD]
[TD]NJ[/TD]
[/TR]
[TR]
[TD]company name with with MA site would go here[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]company name with with CA site would go here[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]...and so on[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]...and so on[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]company name with with MA site would go here[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]company name with with CA site would go here[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]company name with with MA site would go here[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]company name with with CA site would go here[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]company name with with MA site would go here[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]company name with with CA site would go here[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...and so on[/TD]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]...and so on[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[63falcondude]

Try something along the lines of this:

Excel 2010
	A	B	C	D	E	F	G
1	Companies	State Locations		MA	CA	NY	NJ
2	Company 1	TX; CA; AZ; IL; VA; OH; MI; MX; Canada		Company 2	Company 1	Company 3	Company 3
3	Company 2	IL; MA; PR		Company 3	Company 3		Company 4
4	Company 3	AL; AK ; AZ; AR; CA; CO; CT; DC; DE; FL; GA; HI; ID; IL; IN; IA; KS; KY; LA; ME; MD; MA; MI; MN; MS; MO; MT; NE; NV; NH; NJ; NM; NY; NC; ND; OH; OK; OR; PA; RI; SC; SD; TN; TX; UT; VT; VA; WA; WV; WI; WY			Company 4
5	Company 4	CA; TX; PA; NJ; Italy
6
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($A$2:$A$5,SMALL(IF(ISNUMBER(FIND(D$1,$B$2:$B$5)),ROW($A$2:$A$5)-1),ROWS($A$1:$A1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MJD5241]

[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$5,SMALL(IF(ISNUMBER(FIND(D$1,$B$2:$B$5)),ROW($A$2:$A$5)-1),ROWS($A$1:$A1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

[/QUOTE]



When I used this formula on the actual sheet (formatted EXACTLY the same, only it has 1,996 companies versus 5 in the ex) it misses quite a few. It only identifies 8 where there are at least 50.

Any guidance?

 

[63falcondude]

First off, make sure that you adjust the ranges in the formula to fit the data in the actual sheet (i.e. $A$5 will become $A$1997 or whatever you last row of data is).

The formula should grab all exact matches from row 1. How it is right now, it is case sensitive due to the FIND function. This can be changed to SEARCH to make it not case sensitive (i.e. NY and ny will be seen as matches).

Make sure that you confirm the formula using Ctrl Shift Enter.