Display all companies of a state, when the state is selected

[jawahar_otpl]

Hi,

I need to see the list of companies and associated details when I select a state. Please check the table I have and the desired list.

List I have:
[TABLE="width: 628"]
<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]s1[/TD]
[TD]s2[/TD]
[TD]s3[/TD]
[TD]s4[/TD]
[TD]s5[/TD]
[TD]s6[/TD]
[TD]s7[/TD]
[TD]s8[/TD]
[TD]s9[/TD]
[TD]s10[/TD]
[TD]name [/TD]
[TD]contact person[/TD]
[TD]contact number[/TD]
[TD]website[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]FL[/TD]
[TD]IL[/TD]
[TD]MS[/TD]
[TD]NE[/TD]
[TD]NV[/TD]
[TD]OH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company1 [/TD]
[TD]name1[/TD]
[TD]number1[/TD]
[TD]website1[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD]NV[/TD]
[TD]PA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company2[/TD]
[TD]name2[/TD]
[TD]number2[/TD]
[TD]website2[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]FL[/TD]
[TD]GA[/TD]
[TD]MS[/TD]
[TD]SC[/TD]
[TD]TN[/TD]
[TD]TX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company3[/TD]
[TD]name3[/TD]
[TD]number3[/TD]
[TD]website3[/TD]
[/TR]
[TR]
[TD]GA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company4[/TD]
[TD]name4[/TD]
[TD]number4[/TD]
[TD]website4[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]FL[/TD]
[TD]GA[/TD]
[TD]MA[/TD]
[TD]NC[/TD]
[TD]NY[/TD]
[TD]TX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company5[/TD]
[TD]name5[/TD]
[TD]number5[/TD]
[TD]website5[/TD]
[/TR]
[TR]
[TD]FL[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company6[/TD]
[TD]name6[/TD]
[TD]number6[/TD]
[TD]website6[/TD]
[/TR]
[TR]
[TD]AR[/TD]
[TD]GA[/TD]
[TD]MA[/TD]
[TD]MI[/TD]
[TD]NC[/TD]
[TD]OR[/TD]
[TD]WV[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company7[/TD]
[TD]name7[/TD]
[TD]number7[/TD]
[TD]website7[/TD]
[/TR]
[TR]
[TD]MN[/TD]
[TD]ND[/TD]
[TD]WI[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company8[/TD]
[TD]name8[/TD]
[TD]number8[/TD]
[TD]website8[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]LA[/TD]
[TD]MI[/TD]
[TD]NC[/TD]
[TD]PA[/TD]
[TD]TX[/TD]
[TD]WI[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company9[/TD]
[TD]name9[/TD]
[TD]number9[/TD]
[TD]website9[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]IL[/TD]
[TD]MA[/TD]
[TD]NJ[/TD]
[TD]NY[/TD]
[TD]OH[/TD]
[TD]PA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company10[/TD]
[TD]name10[/TD]
[TD]number10[/TD]
[TD]website10[/TD]
[/TR]
[TR]
[TD]DC[/TD]
[TD]FL[/TD]
[TD]GA[/TD]
[TD]AR[/TD]
[TD]MD[/TD]
[TD]NC[/TD]
[TD]SC[/TD]
[TD]TN[/TD]
[TD]VA[/TD]
[TD]WV[/TD]
[TD]Company11[/TD]
[TD]name11[/TD]
[TD]number11[/TD]
[TD]website11[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company12 [/TD]
[TD]name12[/TD]
[TD]number12[/TD]
[TD]website12[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]DC[/TD]
[TD]MI[/TD]
[TD]NJ[/TD]
[TD]OR[/TD]
[TD]TN[/TD]
[TD]TX[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company13[/TD]
[TD]name13[/TD]
[TD]number13[/TD]
[TD]website13[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]LA[/TD]
[TD]NY[/TD]
[TD]OH[/TD]
[TD]TX[/TD]
[TD]VA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company14[/TD]
[TD]name14[/TD]
[TD]number14[/TD]
[TD]website14[/TD]
[/TR]
[TR]
[TD]GA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company15[/TD]
[TD]name15[/TD]
[TD]number15[/TD]
[TD]website15[/TD]
[/TR]
[TR]
[TD]IL[/TD]
[TD]KS[/TD]
[TD]MO[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company16[/TD]
[TD]name16[/TD]
[TD]number16[/TD]
[TD]website16[/TD]
[/TR]
[TR]
[TD]FL[/TD]
[TD]LA[/TD]
[TD]MI[/TD]
[TD]MS[/TD]
[TD]NJ[/TD]
[TD]NY[/TD]
[TD]WA[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company17[/TD]
[TD]name17[/TD]
[TD]number17[/TD]
[TD]website17[/TD]
[/TR]
[TR]
[TD]AK[/TD]
[TD]AR[/TD]
[TD]GA[/TD]
[TD]MN[/TD]
[TD]OR[/TD]
[TD]PA[/TD]
[TD]TN[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]Company18[/TD]
[TD]name18[/TD]
[TD]number18[/TD]
[TD]website18[/TD]
[/TR]
</tbody>[/TABLE]


I need the output to be:
[TABLE="width: 354"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]State[/TD]
[TD]AR[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD]contact person[/TD]
[TD]contact number[/TD]
[TD]website[/TD]
[/TR]
[TR]
[TD]Company1[/TD]
[TD]name1[/TD]
[TD]number1[/TD]
[TD]website1[/TD]
[/TR]
[TR]
[TD]Company3[/TD]
[TD]name3[/TD]
[TD]number3[/TD]
[TD]website3[/TD]
[/TR]
[TR]
[TD]Company7[/TD]
[TD]name7[/TD]
[TD]number7[/TD]
[TD]website7[/TD]
[/TR]
[TR]
[TD]Company11[/TD]
[TD]name11[/TD]
[TD]number11[/TD]
[TD]website11[/TD]
[/TR]
[TR]
[TD]Company18[/TD]
[TD]name18[/TD]
[TD]number18[/TD]
[TD]website18[/TD]
[/TR]
</tbody>[/TABLE]

Depending upon the state I select from the drop down next to "State", below I need the list of all the companies. Appreciate your help.

Thanks,
Jai

 

[Eric W]

I'd recommend using a column E for the row, to avoid doing the calculation intensive array formula more than once. You can hide the column if you want after you put the formula in.

	A	B	C	D	E
State	AR
name	contact person	contact number	website	Row
Company1	name1	number1	website1
Company3	name3	number3	website3
Company7	name7	number7	website7
Company11	name11	number11	website11
Company18	name18	number18	website18

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]8[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]19[/TD]

[TD="align: center"]8[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A3[/TH]
[TD="align: left"]=IF($E3="","",INDEX(Sheet1!K:K,$E3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]E3[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF(MMULT(IF(Sheet1!$A$2:J$19=$B$1,1,0),{1;1;1;1;1;1;1;1;1;1}),ROW(Sheet1!$A$2:$A$19)),ROWS($E$3:$E3)),"")}[/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]



I put your table on Sheet1, columns A:N. The result sheet looks like that. Put the array formula in E3, change the ranges to match your sheet, confirm with Control+Shift+Enter. Copy down as needed. Then put the INDEX formula in A3, copy down and to the right as needed.

Let us know if this works for you.

 

[jawahar_otpl]

Thanks Eric. But I think, there is a miscommunication. Let me explain again. I have list as of data exactly like given below.

SHEET#1

[TABLE="width: 536"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]States[/TD]
[TD]company name[/TD]
[TD]contact name[/TD]
[TD]contact email [/TD]
[/TR]
[TR]
[TD]AR|FL|IL|MS|NE|NV|OH[/TD]
[TD]company#1[/TD]
[TD]name#1[/TD]
[TD]email#1[/TD]
[/TR]
[TR]
[TD]MA|NV|PA[/TD]
[TD]company#2[/TD]
[TD]name#2[/TD]
[TD]email#2[/TD]
[/TR]
[TR]
[TD]AR|FL|GA|MS|SC|TN|TX[/TD]
[TD]company#3[/TD]
[TD]name#3[/TD]
[TD]email#3[/TD]
[/TR]
[TR]
[TD]GA[/TD]
[TD]company#4[/TD]
[TD]name#4[/TD]
[TD]email#4[/TD]
[/TR]
[TR]
[TD]CA|FL|GA|MA|NC|NY|TX[/TD]
[TD]company#5[/TD]
[TD]name#5[/TD]
[TD]email#5[/TD]
[/TR]
[TR]
[TD]FL[/TD]
[TD]company#6[/TD]
[TD]name#6[/TD]
[TD]email#6[/TD]
[/TR]
[TR]
[TD]AR|GA|MA|MI|NC|OR|WV[/TD]
[TD]company#7[/TD]
[TD]name#7[/TD]
[TD]email#7[/TD]
[/TR]
[TR]
[TD]MN|ND|WI[/TD]
[TD]company#8[/TD]
[TD]name#8[/TD]
[TD]email#8[/TD]
[/TR]
[TR]
[TD]CA|LA|MI|NC|PA|TX|WI[/TD]
[TD]company#9[/TD]
[TD]name#9[/TD]
[TD]email#9[/TD]
[/TR]
[TR]
[TD]CA|IL|MA|NJ|NY|OH|PA[/TD]
[TD]company#10[/TD]
[TD]name#10[/TD]
[TD]email#10[/TD]
[/TR]
[TR]
[TD]DC|FL|GA|KY|MD|NC|SC|TN|VA|WV[/TD]
[TD]company#11[/TD]
[TD]name#11[/TD]
[TD]email#11[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD]company#12[/TD]
[TD]name#12[/TD]
[TD]email#12[/TD]
[/TR]
[TR]
[TD]CA|DC|MI|NJ|OR|TN|TX[/TD]
[TD]company#13[/TD]
[TD]name#13[/TD]
[TD]email#13[/TD]
[/TR]
[TR]
[TD]CA|LA|NY|OH|TX|VA[/TD]
[TD]company#14[/TD]
[TD]name#14[/TD]
[TD]email#14[/TD]
[/TR]
[TR]
[TD]IL|KS|MO[/TD]
[TD]company#15[/TD]
[TD]name#15[/TD]
[TD]email#15[/TD]
[/TR]
[TR]
[TD]FL|LA|MI|MS|NJ|NY|WA[/TD]
[TD]company#16[/TD]
[TD]name#16[/TD]
[TD]email#16[/TD]
[/TR]
[TR]
[TD]AK|AR|GA|MN|OR|PA|TN[/TD]
[TD]company#17[/TD]
[TD]name#17[/TD]
[TD]email#17[/TD]
[/TR]
</tbody>[/TABLE]

In the next sheet (SHEET#2) I have a drop down list for all states in the US in cell A1. Whenever I select a state from the drop down in A1 in sheet 2, the formula should look for the state in the existing table in sheet 1 and display the contact details of all the companies in sheet 2.

For example if I select the state AR, I must see this below given result in sheet 2. All these companies are functioning in the state AR. If select another state, this list should show the companies operating in that selected state. Hope it is clear now.

[TABLE="class: cms_table, width: 354"]
<tbody>[TR]
[TD]AR[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name[/TD]
[TD]contact person[/TD]
[TD]contact number[/TD]
[TD]website[/TD]
[/TR]
[TR]
[TD]Company1[/TD]
[TD]name1[/TD]
[TD]number1[/TD]
[TD]website1[/TD]
[/TR]
[TR]
[TD]Company3[/TD]
[TD]name3[/TD]
[TD]number3[/TD]
[TD]website3[/TD]
[/TR]
[TR]
[TD]Company7[/TD]
[TD]name7[/TD]
[TD]number7[/TD]
[TD]website7[/TD]
[/TR]
[TR]
[TD]Company11[/TD]
[TD]name11[/TD]
[TD]number11[/TD]
[TD]website11[/TD]
[/TR]
[TR]
[TD]Company18[/TD]
[TD]name18[/TD]
[TD]number18[/TD]
[TD]website18[/TD]
[/TR]
</tbody>[/TABLE]

Regards,
Jai

 

[Eric W]

I thought you had the states in different columns. If they are in the same column, delimited by | like this:

	A	B	C	D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]States[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]contact name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]contact email[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AR|FL|IL|MS|NE|NV|OH[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]MA|NV|PA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#2[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AR|FL|GA|MS|SC|TN|TX[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#3[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]GA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#4[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]CA|FL|GA|MA|NC|NY|TX[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#5[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]FL[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#6[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AR|GA|MA|MI|NC|OR|WV[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#7[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]MN|ND|WI[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#8[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]CA|LA|MI|NC|PA|TX|WI[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#9[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]CA|IL|MA|NJ|NY|OH|PA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#10[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]DC|FL|GA|KY|MD|NC|SC|TN|VA|WV[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#11[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]CA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#12[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]CA|DC|MI|NJ|OR|TN|TX[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#13[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]CA|LA|NY|OH|TX|VA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#14[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]IL|KS|MO[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#15[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]FL|LA|MI|MS|NJ|NY|WA[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#16[/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AK|AR|GA|MN|OR|PA|TN[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]company#17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]name#17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]email#17[/TD]

</tbody>

Sheet1​

Then you can use this version:

	A	B	C	D	E
company#1	name#1	email#1
company#3	name#3	email#3
company#7	name#7	email#7
company#17	name#17	email#17

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]State[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]AR[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Company Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Contact Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Contact Email[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Row[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A3[/TH]
[TD="align: left"]=IF($E3="","",INDEX(Sheet1!B:B,$E3))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]{=IFERROR(SMALL(IF(ISNUMBER(SEARCH("|"&$B$1&"|","|"&Sheet1!$A$2:$A$18&"|")),ROW(Sheet1!$A$2:$A$18)),ROWS($E$3:$E3)),"")}[/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]