Dynamically Reference and Select Column

[Sythius]

I am having a crazy amount of difficulty getting a formula to work for my needs.

I am making a multiple dependent dropdown list in excel, and that part is not a problem anymore.

The difficult part I ran into was when I decided to make the data populate automatically based off of a single table. As such, I have no named ranges currently, and would like to avoid using any if at all possible.

The extra tricky part is that I am trying to eliminate any blanks in the dropdown menu, which I have done successfully, and I have literally one last problem.

The formula I have pasted below works, but is based off of a specific column, and as such, only provides the amount of lines that column has, not the column that is identified in the earlier part of the formula from the match. I am looking to alter that "Year!$G:$G" part of the COUNTIF section of the formula to cause it to reference the specific entire column that is determined during the match process.

Any assistance would be GREATLY appreciated, as I, and all those I know are unable to crack the case on this one.

Thank you kindly in advance.

=OFFSET(Year!$A$1,1,MATCH('Special Vehicle Lookup'!$B$21,Year!$1:$1,0)-1,COUNTIF(Year!$G:$G,"*?*"),1)

 

[Sythius]

I just wanted to say I managed to figure this out. It was this.

=OFFSET(Year!$A$1,1,MATCH('Special Vehicle Lookup'!$B$21,Year!1:1,0)*1,COUNTIF(INDEX(Year!$B$1:$AZ$500,0,MATCH('Special Vehicle Lookup'!$B$21,Year!1:1,0)),"*?*")-1,1)