HELP! Index match, VLOOKUP, Not sure what to do

[MikeD33]

hello!

This is my first post so please bare with me. I am trying to make a configurator for work. Here is what I'm trying to do:

B22 is a Drop down list made from Data validation. This list contains different types of equipment that is placed on different Chassis. I want to be able to click the drop down list, choose an aerial device and have a list of options be populated in B23--> B56. I have over 50 types of aerial devices so I would have 50 different option lists that data would be pulled from. I feel VLOOKUP would not work in this matter because the information is pulled from another worksheet and the data is in 1 column and several rows and I do not want to reinvent the wheel here. I feel that Match index MIGHT be what I need to do but I don't know how I need to go about using it. Any help would be greatly appreciated!

 

[kweaver]

For each aerial device chosen from the drop down in B22, are there a varying amount of other pieces of equipment that would populate B23 and on?

 

[kweaver]

If I read you correctly, I did the following:

1. Create a drop down sheet. On it I listed the drop down options for B22 in column A.
Then, from B1 to whatever column you need, relist the options (use copy and paste special | transpose). I only tested to column K.
2. Below each of the aerials in B2 to Kn, enter the names (assume they're all text) of the respective options.
3. In B23 (fill down), I used this formula: =IFERROR(HLOOKUP($B$22,dropdowns!$B$1:$K$20,ROW()-21,0),"")
Change the B1 to K20 range accordingly.
And, I also change the formatting for those cells to a custom format: ;;;@

<colgroup><col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="width:48pt" width="64" span="10"> </colgroup><tbody>
[TD="class: xl63, width: 75"]Equipment[/TD]
[TD="class: xl63, width: 64"]Aerial1[/TD]
[TD="class: xl63, width: 64"]Aerial2[/TD]
[TD="class: xl63, width: 64"]Aerial3[/TD]
[TD="class: xl63, width: 64"]Aerial4[/TD]
[TD="class: xl63, width: 64"]Aerial5[/TD]
[TD="class: xl63, width: 64"]Aerial6[/TD]
[TD="class: xl63, width: 64"]Aerial7[/TD]
[TD="class: xl63, width: 64"]Aerial8[/TD]
[TD="class: xl63, width: 64"]Aerial9[/TD]
[TD="class: xl63, width: 64"]Aerial10[/TD]

[TD="class: xl63"]Aerial1[/TD]
[TD="class: xl63"]AAA[/TD]
[TD="class: xl63"]OO[/TD]
[TD="class: xl63"]WWWW[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial2[/TD]
[TD="class: xl63"]BBB[/TD]
[TD="class: xl63"]PP[/TD]
[TD="class: xl63"]XXXX[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial3[/TD]
[TD="class: xl63"]CCC[/TD]
[TD="class: xl63"]QQ[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial4[/TD]
[TD="class: xl63"]DDD[/TD]
[TD="class: xl63"]RR[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial5[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]SS[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial6[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"]TT[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial7[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial8[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial9[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

[TD="class: xl63"]Aerial10[/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]
[TD="class: xl63"] [/TD]

</tbody>

<colgroup><col style="mso-width-source:userset;mso-width-alt:3766;width:77pt" width="103"> </colgroup><tbody>
[TD="class: xl65, width: 103"]Aerial2[/TD]

[TD="class: xl66"]OO[/TD]

[TD="class: xl66"]PP[/TD]

[TD="class: xl66"]QQ[/TD]

[TD="class: xl66"]RR[/TD]

[TD="class: xl66"]SS[/TD]

[TD="class: xl66"]TT[/TD]

[TD="class: xl66"] [/TD]

[TD="class: xl66"] [/TD]

[TD="class: xl66"] [/TD]

</tbody>