Match Index, one down two along, different sized lookups!

[nj2406]

Hello! I will try to explain my sheets and formulas I have tried thus far.

I am trying to populate the top row of a table using a lookup sheet of various sites.
Here is the lookup, the reason for the repeated values in column B is another INDEX/MATCH within my table (which is populating just fine). I would like my column C values to appear in a table heading row, when the A column value is selected in a drop-down menu (shall we call this cell N2).

Here is my current formula, where I have added one to the + row value to move down my list.
=INDEX(Lookup!$A$1:$C$23,MATCH($N$2,Lookup!$A$1:$A$23,0)+1,3)

HOWEVER! As you may notice some of my mini-lists in column C are shorter than the other, and I would like my formula to stop populating my header row (at the moment it goes 0 for the space, then continues to the next list).

I have tried using the 0/space in the previous header row cell, in the hope that the rest of the heading row would be zeros, but it has only done this once, so two zeros before starting from the next mini-list.
i.e
=IF(A2=0,"0",INDEX(Lookup!$A$1:$C$23,MATCH($N$2,Lookup!$A$1:$A$23,0)+1,3))

Any help would be greatly appreciated, could this require an INDIRECT to assign the mini-list array?

 

[bewsh1987]

can you paste the values you have in your range as you may be able to simplify your formula

 

[nj2406]

The lookup table is all values, however as my dropdown menu changes, I would like my header row in the new table to be populated by the mini-list in Column C, should Column A have been selected. Hope this makes sense!

 

[bewsh1987]

can you paste you values on here please so I can see what values you have

also am I right in saying that if your dropdown = A23 for example that your list will only have C23 value
if A22 then you list will have C22:C23 values

I need to picture this correctly

 

[nj2406]

The dropdown runs off of a different lookup of just the values/locations in Column A. The values are column C, these are what I'd like to populate a row on another page, when the appropriate location in A is selected.
Eg, in my other-page dropdown, "Aberdeen A UK" is selected, so I would like C1:C6 to populate the table row. When I select "Avonmouth B", I would like C15:C1 to populate it, however as you can see in the last picture, my current formula keeps populating down into the next location, so "Avonmouth A"'s related locations C20:C21 (and, in fact, the space in cell C19 which appears as a zero).

Many thanks

 

[bewsh1987]

can you paste you values on here??

 

[bewsh1987]

try

=IF(MATCH(C1,$C$1:$C$23,0)>MATCH($N$2,$A$1:$A$23,0),"",C1)

drag this down

 

[nj2406]

Not sure which values you mean, all the values to populate my table row are in column C. Essentially these row values will change when the value in column A is selected. So my changing arrays will be C1:C6, C8:C13, C15:C18 and C20:C23 but these arrays are different sizes. These changes occur when my dropdown N2 changes, and equivalent Column A value, changing the array and thus table row values.

 

[bewsh1987]

can you paste the values you have in A1:C23 on here

 

[nj2406]

Does the picture in my intitial post not show?