Not referencing column on excel table

Question

I'm not sure what I am missing, but it's a recurring problem for me. I want to reference a column in a table, usually for an index function, but I also have this issue with vlookups. Its an issue with indicating the column I want to get info from. I'm sure I have the syntax wrong, but from researching, I don't appear to.

I am trying to get the data from the second column in a table in the workbook. It's on a different worksheet. The formula refers to the correct table and the column indicated in the match function returns the correct row number. When I try to reference the column I want by table referencing, I get a reference error. Please tell me what I am missing.

This returns a reference error:

=INDEX(Intake,MATCH(C3,Intake[Helpercolumn2/SID],0),Intake[Begin])

This returns the correct data:

=INDEX(Intake,MATCH(C3,Intake[Helpercolumn2/SID],0),2)

Please note that all of the following have been attempted but also return a reference error:

=INDEX(Intake,MATCH(C3,Intake[Helpercolumn2/SID],0),Intake[@Begin])
=INDEX(Intake,MATCH(C3,Intake[Helpercolumn2/SID],0),Column(Intake[Begin]))
=INDEX(Intake,MATCH(C3,Intake[Helpercolumn2/SID],0),Intake[[#Data],[Begin]])
=INDEX(Intake,MATCH(C3,Intake[Helpercolumn2/SID],0),Intake[[#All],[Begin]])

Scott Craner Scott Craner · Accepted Answer · 2019-02-11T20:12:40

the column you want to return is the first range in the index:

=INDEX(Intake[Begin],MATCH(C3,Intake[Helpercolumn2/SID],0))

Third criterion in INDEX is optional and only needed if the reference in the first criterion is 2 dimensional.

So you can use:

=INDEX(Intake[#Data],MATCH(C3,Intake[Helpercolumn2/SID],0),3)

Where 3 is the relative column number in the table. But if the column order changes then you will need to change the formula. The first is better as it will not matter the order of the columns.

Not referencing column on excel table

1 Answers