Index Match keeps returning #REF!

The formula that I am using looks very similar to this:

Formula:

Please Login or Register  to view this content.

I have a master document that is pulling data from several other documents. I have a table with a column labeled adjustments on both documents and trying to find the value that is listed under that header.

Master Document:

Agent	Adjustments	Sessions	IBC	OBC
David	10	5	16	3
Ashley	5	7	18	5

Ashley's Document

Ashley
Adjustment	Sessions	IBC	OBC
5	7	18	5

David's Document:

David
Adjustment	Sessions	OBC	IBC
10	5	16	3

This is the general formula that I am using to bring in the results:

Formula:

Please Login or Register  to view this content.

For some reason when the document asks if I want to update and I click 'update' every cell that has the formula returns !REF#. But I have a different document at work that includes the same formula and different documents but pulls in the information just fine without having to have each individual workbook open at the same time. Typically there will be a total of 16 documents, one per person and the one master.

Essentially what I am trying to do is keep track of each person. Each person has a separate excel file and then I use the master file to have a general overview of everything. I've included a copy of each of the files to help understand it a little bit better. If further clarification is needed please let me know and I will update accordingly.

Master.xlsb
david.xlsb
ashley.xlsb

Edit: Updated attachment formatting within post. Also removed original formula and replaced with correct formula.

What is table48? I can't find any reference to it.

Every reference to that table should have the full filepath.


=INDEX(DocName.xlsb!Table7[adjustments],MATCH(Table48[[#Headers],[adjustments]],DocName.xlsb!Table7[[#Headers],[adjustments]],0)

more something along the lines of

=INDEX(DocName.xlsb!Table7[adjustments],MATCH(DocName.xlsb!Table48[[#Headers],[adjustments]],DocName.xlsb!Table7[[#Headers],[adjustments]],0)

It doesn't actually.

=INDEX('C:\Users\Kevin\Desktop\david.xlsb'!Table1[Adjustments],MATCH('C:\Users\Kevin\Desktop\david.xlsb'!Table1[[#Headers],[Adjustments]],C:\MISSING\FILEPATH_TO_TABLE_Table48[[#Headers],[Adjustments]],0))

The comma between the MATCH parameters means you need to redeclare the full filepath.

When you create the formula using the mouse, excel should fill in whatever path it needs automatically.
What *may* make things simpler here (especially if you dont understand structured table referenicing) is to convert your table back into a range, just until you have your INDEX/MATCH sorted out, then re-make it a Table again

Tables most certainly do have their place, and not just for formatting and sorting. if you have formulas, and add new data, tables will copy your formulas down for you automatically, but, as you have seen, the terminology can leave you wondering what happened.

I have often manually typed in a range or cell ref, so I can see what it is, rather than use the structured formatting of tables

Not so fast, you can still have the best of both worlds.

From the Home tab, go to Styles, Format as Table dropdown menu.

All the pretty, none of the work.



Lazy is the driving force between good process improvement.