Sorting issue with Index+Match function

[@Gopigk]

Hi All

Help required in sorting data in Index+Match formula. Formula working correctly, but facing issue in sorting data. Unlike V-Look up, it's giving wrong sort result. Herewith i attached sample file, can anybody help on this. I need same as v-lookup sorting , which is also specified on this file.

 

[AliGW]

Which version of Excel are you using?

 

[AliGW]

In B3 copied across and down:

=IFERROR(INDEX(Data!$A:$C,MATCH(Result!$A3,Data!$B:$B,0),MATCH(Result!B$2,Data!$A$1:$C$1,0)),"Fail")

You had not correctly defined all of your ranges.

 

[@Gopigk]

Hi Ali, Thanks for reply

It's O-365. My formula is working fine. But when I tried to sort the data based on B or C column with the intention of all Fail's are at down, but I'm unable. Pls suggest

 

[AliGW]

OK - sorry, I misunderstood.

 

[p45cal]

Office 365:
Single cell formula in C2:

=SORT(HSTACK(A2:A11,IFERROR(INDEX(Data!A1:C7,MATCH(A2:A11,Data!B1:B7,0),{3,1}),"fail")),2)

 

[AliGW]

Note that HSTACK is currently only available to Office Insider Beta subscribers.

 

[p45cal]

Note that HSTACK is currently only available to Office Insider Beta subscribers.

I'm not an insider…

 

[GraH - Guido]

It's now in the current Channel (previously Insider slow).

 

[AliGW]

Thanks Guido - MS have not yet updated their support pages!