As others said - you need to create a table instead. Terrible naming but awesome functionality. It's the best hidden secret of excel. It also has the side effect of naming the whole range so you can easily lookup() in just that scope. (Or in general reference values without an offset on the page and with proper column names)

Create Table is a different command than Format as Table. The former creates a data table, the latter format cells to provide the appearance of a table.

Format as Table turn the area into a Table also.

"When you have a data range that is not formatted as a table, Excel will automatically convert it to a table when you select a table style."

https://support.office.com/en-us/article/Format-an-Excel-tab...

Oh. Well that's perfectly clear.