Excel formula to get first and last non-empty value in a row and return column header

Solution 1:

To find the min value people have voted on in the poll (the column header of the first non-empty cell), I used the following formula (in cell H2):

=INDEX(B$1:G$1, MATCH(TRUE, INDEX(B2:G2<>"", 0), 0))

To find the max value (in cell I2):

=LOOKUP(2, 1/(B2:G2<>""),B$1:G$1)

Under the bonnet

Lookup function has the following signature: LOOKUP( value, lookup_range, [result_range] )

What the second formula does is the following:

Divide 1 by an array of boolean (true/false) values (B2:G2<>""); that's the 1/(B2:G2<>"") part.
Above relies on a fact that Excel converts boolean TRUE to 1 and FALSE to 0, thus the result of this step is a list of:
- 1's, where CELL_VALUE<>"" returned TRUE, and 1/TRUE => 1)
- and #DIV/0! errors, where CELL_VALUE<>"" returned FALSE and 1/FALSE => 1/0 => #DIV/0!
Now, because I used 2 (you can use an number starting from 1) as the lookup value, the formula matches the last numeric value in the range (as you remember, the range is something along the lines of [DIV/0!, 1, 1, 1, DIV/0!, DIV/0!], that's why lookup will match the fourth item). BTW: Note that if the formula didn't match anything you'd still get a #N/A error.
At this stage we know what column we're interested in (fourth in the range). So the last bit that's missing is telling Excel that the result range is the first row of the spreadsheet: B$1:G$1, and that's where Excel will look for the said fourth item.