I am working on a data where I need to do a custom sort using google sheet formula, like as shown below,

i.e my expected output, is first the category needs to show IN STOCK, then the TRANSIT & NOT BUILT, this is because IN STOCK is one category while TRANIST & NOT BUILT is another category, also the MDL will be in ascending order. Basically all the IN STOCK always needs to be on top, and then the TRANSIT followed by NOT BUILT in ascending order, just like the first image.

This is what I have tried using the following formula, but it doesn't works

=SORT(ARRAYFORMULA(Raw_Data!X2:AG1000),27,TRUE,MATCH(Raw_Data!Y2:Y,{"IN STOCK";"TRANSIT";"NOT BUILT"},0),TRUE)

Not that i have not searched here, but i am not able to locate the exact resolution which i am looking for may be i am not able to understand, I have been trying for so long, even searched on google, tried using the QUERY function but still i am not able work that out, any suggestions or advices is welcomed and highly appreciated.

CodePudding user response：

try:

=INDEX(SORT(A2:B, IFNA(MATCH(A2:A, "in stock", 0), 2), 1, 2, 1))