Sort not working correctly when using Index Match Formula

[Sundance_Kid]

Hi,

I am using an index/match formula to return values off a pivot table. I then have added in another column which sums up the total.

However, when I click on sort in the total column, to sort from largest to smallest, it doesnt sort correctly and ends up messing up the index/match formula. It seems to sort the persons name bt the formula then changes where it is looking up a different cell.

Is there something to be aware of when using Sort and index/match?

Attached Xl2bb

Thanks

IndexMatchSort Issue.xlsx
	B	C	D	E	F	G	H
2	Name	Feb-22	Mar-22	Apr-22	May-22	Jun-22	Total
3	Shane	4	9	15	8	9	45
4	Paul	11	3	15	99	18	146
5	Mary	56	12	45	12	19	144
6	Mark	56	5	68	22	28	179
7	Luke	0	1	1	5	6	13
8	John	1	15	16	37	56	125
9	Joan	5	6	3	2	1	17
10	Total	133	51	163	185	137	669
Sheet3

Cell Formulas
Range	Formula
C3:G9	C3	=INDEX(Sheet2!$D$4:$H$10,MATCH(Sheet3!$B3,Sheet2!$C$4:$C$10,0),MATCH(Sheet3!C$2,Sheet2!$D$3:$H$3,0))
H3:H10	H3	=SUM(C3:G3)
C10:G10	C10	=SUM(C3:C9)

 

[Fluff]

Remove all references to Sheet3 in the formula. You should never refer to the sheet the formula is on, just use the cell reference

 

[Sundance_Kid]

Remove all references to Sheet3 in the formula. You should never refer to the sheet the formula is on, just use the cell reference

That worked perfectly. Thank you!!

Out of interest, should you never refer to the sheet the formula is on for all formulas or just when using index/match? As sometimes it is easier when moving between tabs to just click on the cell you may need which may add in the sheet to the formula.

 

[Peter_SSs]

Out of interest, should you never refer to the sheet the formula is on for all formulas or just when using index/match?

Best for all formulas, especially if sorting might be involved.

You can still click the cells to build your formula if you want, but afterwards, edit it to remove the active sheet name(s) from your formula before copying to other cells.