Table Column Header Value Identity Crisis?

[Josh123]

Hi,

I am trying to pull a specific value from inside of a table utilizing an Index & Match formula, but I am encountering an error when I reference the table headers to identify the column number. I attempted to break down my formula to identify where the problem is occurring and found something that has me utterly confused.

Here is a sample of the table I am working with. The table name is TBL_Increases:

	A	B	C	D
1	Industry	2015 ​	2016 ​	2017​
2	Energy	0.5	2.5	3.0
3	Financial	3.0	2.9	3.1
4	Healthcare	3.0	3.0	3.4

<tbody>
</tbody>

Here are my reference values:

	A	B
1	Energy	2016

<tbody>
</tbody>

The formula I am using is:
=INDEX(TBL_Increases,MATCH(A1,TBL_Increases[Industry],0),MATCH(B1,TBL_Increases[#Headers],0))
The formula returns with an #N/A error.

After further examination, the second MATCH formula (for the column header number) is returning with the error. So I tried a simple =B1=C2 to see if the values are the same, but it came back as FALSE.

Can someone help me understand this?

 

[AhoyNC]

Check to see if one may be a number and the other text or if both are text check for leading or trailing spaces.

 

[Josh123]

Please open a new spreadsheet and test this with me:

Test #1 : Number Match

1. Type "1" in cell A1
2. Type "1" in cell C1
3. Run the formula: =A1=C1 (the results should be "TRUE")
4. Click on cell C1 and insert a table (check the box that stats "My table has headers")
5. Run the formula: =A1=C1 (the result says "FALSE")

Test #2 : Letter Match

1. Type "X" in cell A1
2. Type "X" in cell C1
3. Run the formula: =A1=C1 (the results should be "TRUE")
4. Click on cell C1 and insert a table (check the box that stats "My table has headers")
5. Run the formula: =A1=C1 (the result says "TRUE")

Test #3 : Letter,Number Match

1. Type "X1" in cell A1
2. Type "X1" in cell C1
3. Run the formula: =A1=C1 (the results should be "TRUE")
4. Click on cell C1 and insert a table (check the box that stats "My table has headers")
5. Run the formula: =A1=C1 (the result says "TRUE")

Test #4 : Number,Letter Match

1. Type "1X" in cell A1
2. Type "1X" in cell C1
3. Run the formula: =A1=C1 (the results should be "TRUE")
4. Click on cell C1 and insert a table (check the box that stats "My table has headers")
5. Run the formula: =A1=C1 (the result says "TRUE")

Why can't we establishes matches using numbers exclusively?

 

[AhoyNC]

See attached link. Excel tables treat headers as text.
http://www.k2e.com/tech-update/tips/135-working-with-column-headers-in-excel-tables

 

[Josh123]

AhoyNC,

Thank you so much for providing this link - this is exactly what I needed to know.
The only other alteration I had to do was Ctrl+Shift+Enter.

Thanks!