Allow Numerical Values in the Header Row of Tables
Allow numbers in the header row of tables.
Right now, header rows appear to be forced to be text types.
Allowing headers to be values would allow the use of the XLOOKUP function on the header row using the "exact or next less" and "exact or greater" flags. This would greatly simplify step-wise lookups and interpolations where the data was arranged in a horizontal fashion.
Right now, data tables need to be arranged vertically in order to use these flags on the "header" information if the header information is a number.
It can be done, but in circumstances where data imports or readability naturally lay out the data horizontally, this is less than ideal.
It also seems like a bit of a bug that the xlookup function can be used in the first column of a table (with these flags), but not on the first row.
This could also be useful for doing 2d interpolations where both the header column and header row needed to contain numerical values.
You could even achieve a greater than or less than approach more directly by adapting something like the following to suit where input and data are:
if you consider that they are treated as Text by Excel in the context of a lookup function (for which the difference is often critical, while they are NOT treated as text for a simple " =C6-Table1[[#Headers], ] " formula: Excel is quite prepared to realize most are demanding that formula only consider the "number-ness" of the header value), a way forward presents itself:
in which I've simply wrapped the headers expression (2nd parameter) in the VALUE() function.
And it works, producing an ansswer of "625".
This is a longstanding "thing" and unlike most Excel hassles, on the actual surface of the problem, you can see how it really has to be this way. (Most times, the "why" (for how it ever happened in the first place, or for the compelling reason for it once you realize it) is pretty hidden, but not here.) ANY lookup function MUST be able to distinguish text data from numerical data, or else a tremendous amount of material could never be searchable given Excel's built-in 15 significant character limit for numbers. So this will always have to be worked around when one has "the opposite" problem.
But wrapping the lookup range in VALUE() is quick and easy, natural (sort of) given the siutuation. So I seriously doubt they will change this behavior.
But again, VERY EASY workaround which hopefully ameliorates the pain somewhat, downgrading it to simple obnoxiousness. And speaking of simple obnoxiousness, who couldn't use another little sister?
Seriously, now you're thinking of it, it may explain some difficulties you've has in other lookups over the years, and a solution for them like the following:
especially if receiving data from others or scraping from the internet.
Chris Reichert commented
To add context, since Thomas K has completely misinterpreted what I've described.
Attached file has two small datasets. The top dataset is just plain data. I use an XLOOKUP to find the pressure that corresponds to the temperature less than the input value provided. Works fine.
The lower dataset is the same data, converted to a table. You can see that the headers have been converted from number to text (they are now left justified). Trying to run the same XLOOKUP function now returns an error. This is not optimal.
Thomas K commented
Could you please explain a little more with an example.
The header row can both show letters or numbers.
Tables can have columnnames that are numbers.