Getting '#Value!' With Combination of SORT, FILTER Formula

[Arfur1963]

Hi everyone,

I’m having trouble with an Excel formula and could use some help. Here’s what I’m trying to achieve:

1. Filter data from a table named tblStudents using headers specified in the range C4:G4.
2. Sort the filtered data based on a column named ‘New Class’.

Here’s the formula I’m currently using:

=SORT(FILTER(INDEX(tblStudents,SEQUENCE(ROWS(tblStudents)),XMATCH(B4:N4,tblStudents[#Headers])),tblStudents[No.]<>""),XMATCH("New Class",tblStudents[#Headers]),-1)
This formula works when I use the range B4:N4, but it gives a #VALUE! error when I change the range to B4:M4.

Questions:

• Why does changing the range from B4:N4 to B4:M4 cause a #VALUE! error?
• How can I modify the formula to work correctly with the B4:M4 range?

Any insights or suggestions would be greatly appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
If the New Class column is the 13th column of the table, that would explain why.
Also there is a very good chance you will not be sorting the data on the correct column.