Formula doesn't move with rows when using sort in a table

Hi everyone,

I have an excel issue with sorting data (with dynamic formulas) that's in a table. I've included a Google Drive link to download the file as the attachment feature here isn't showing up for me ....

https://drive.google.com/open?id=1mn...BLGmlCArhOYtHz

Background info:
- Spreadsheet contains 3 sheets: Sheet A, B, and C
- Sheet A grabs data from sheet B that matches the names in columns B and C
- This is done using index and match (I used array formula CTR+SHIFT Enter)
- The data is inputted into column D

Problem:
The problem occurs when I turn this into a table and sort by spend for example. The index match formula in column D doesn't move with sort and stays in the fixed rows. Please reference Sheet C for an illustration of the problem.
You'll see that the spend for Google / AAG_S_Reverse_Mortgage_SKAG_Tier_3 is $336 instead of $525.

>> How do I make it so that the match portion of the formula moves as the table is sorted? I only locked the columns in place and not the rows, so I don't understand why it isn't moving as the table is sorted...

Looking for help from the Excel ninjas!

Please attach the workbook.

Is this a Google Sheets query?

without looking at your file as i cannot open it, my guess is you have the sheet name included in the formula when referencing a cell that is on the current sheet. So lets say this table is on Sheet22. Inside this formula are there any cell references that say something like Sheet22A1? If so remove Sheet22 so it is just A1 and it should sort correctly.

Please follow the instructions in the yellow banner at the top of the page to attach the workbook directly here.

Your image is invalid, but if it shows that the paper clip icon doesn’t work, we know. However, the instructions at the top of the page do work.

The INDEX range was incorrect. Change it to this:

=INDEX('Sheet B'!$D$2:$D$27,MATCH('Sheet A'!$B5&'Sheet A'!$C5,'Sheet B'!$B$2:$B$27&'Sheet B'!$C$2:$C$27,0))

Use the field designations:

=INDEX('Sheet B'!$D$2:$D$27,MATCH([@Publisher]&[@Campaign],'Sheet B'!$B$2:$B$27&'Sheet B'!$C$2:$C$27,0))

Excel 2016 (Windows) 32 bit

	B	C	D
1	Publisher	Campaign	Spend
2	Misc	RC	$211
3	Misc	CA	$258
4	Google	AAG_S_Reverse_Mortgage_SKAG_Tier_3	$525
5	Google	AAG - S - HECM	$336

Sheet: Sheet A

Thanks, that solved it!

What's the difference between [@Publisher] and simply [Publisher]? I saw that the former only highlights the cell on the same row while the latter highlights the entire column.

You have identified the difference yourself.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.