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:
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Industry

[/TD]
[TD]

2015
​

[/TD]
[TD]

2016
​

[/TD]
[TD]

2017​

[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Energy[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]3.0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Financial[/TD]
[TD="align: center"]3.0[/TD]
[TD="align: center"]2.9[/TD]
[TD="align: center"]3.1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Healthcare[/TD]
[TD="align: center"]3.0[/TD]
[TD="align: center"]3.0[/TD]
[TD="align: center"]3.4[/TD]
[/TR]
</tbody>[/TABLE]










Here are my reference values:
[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Energy[/TD]
[TD="align: center"]2016[/TD]
[/TR]
</tbody>[/TABLE]





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!