Return non blank cell using INDEX and MATCH

[mabbutt]

Hi,

I would like to try and edit this formula to return non blank cells for a dropdown list:

=INDEX(Results!$A$2:$S$183,,MATCH(A55,Results!$A$1:$S$1,0))

The range that I would like to omit the blanks from is Results!$A$2:$S$183.

I also cannot use an array formula as I need to put this formula in the source field for the dropdown.

Thanks

 

[mabbutt]

I have resolved this by using named ranges and tables.

It's a shame that it wasn't possible or I couldn't work out the other way as it seemed so much cleaner.