How to Stop Array Formula Messing up in Sorting in Google Sheets

Question

I am exporting the data through Array(Vlookup from different sheet by matching Id's but whenever i sort the data A to Z by using any column. ID's array formula keeps the Id as it is. Due to which data cannot be sorted.

Your help will be much appreciated.

Sheet Link

player0 · Accepted Answer · 2021-11-23 14:27:49Z

1

try in A1:

={"IDs"; QUERY('Data Sheet'!A2:B, "select A where A <> 0 and A is not null order by B asc")}

and B2:

=ARRAYFORMULA(IF(A2:A="",,IFERROR(VLOOKUP(A2:A, 'Data Sheet'!$A$2:$I, {2,3,4,5,6,7,8}, 0))))

...if you want to keep vlookup

asc can be changed to desc

answered Nov 23, 2021 at 14:27

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

HSHO Over a year ago

Thank you @player0 for posting an answer. The problem i have been facing is i want to sort the data manually using any column. when i sort the Column A to Z-A all other data gone.

Collectives™ on Stack Overflow

How to Stop Array Formula Messing up in Sorting in Google Sheets

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related