Match not finding a match in a table when headers are numbers.

I have a table, the column headers are all numbers (pipe sizes), so the headers are whole numbers. My problem is Match will not read the table headers when they are whole numbers.

This returns a #N/A error: match(X,Table[#Headers],0) where X is the lookup number from a referenced cell. It seems the Table using whole numbers as column headers is the problem.

If I convert the Table to a Range this works: match(X,A1:A5,0) where X is a number or cell reference containing a number. Match works when using a Range but not using a Table, but all the numbers are the same! All I'm doing is converting the range to a table.

Any ideas why numbers as Table Headers are a problem and how to fix?

Thank you!

Unless I have misunderstood, you're doing it the wrong way. The thing you are looking for comes first, then the range you are searching, and then zero (0) for an exact match.

https://support.office.com/en-us/art...9-533f4a37673a

Yes, sorry. The match is embedded in an =index(Table,match(X1,Table[#HeaderCol1],0),match(X2,Table[#Headers],0)) type arrangement. The second match will not find the column if the column header is a whole number. It works if I change the headers to text. For example. If X2 = "Ten" and the header column is Ten, no issue. If I change X2 = 10 the change the column header to number "10" (without quotes) it will not work.

sometime numbers are stored as text...check the format.

You cannot do that. If you put "" in X1 you just converted the cell reference X1 to text X1.
Right click on X1 then format cell is it "General"?
Right click on one of the header, is it "General"?

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original. Please start a new thread - See Forum rule #4

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

You can follow a thread by subscribing to it. You don't have to post to it.

No worries. But, if you want people to consider your specific issue, then you should start your own thread.