SORT formula is pulling in null cells

Question

I have a spreadsheet that uses the following formulas

To merge data an importrange and another sheet (column J):

=iferror(vlookup(D2,List_of_Things!A:N,13,false),"")

To sort that data based on the vlookup (=importrange record's key):

=SORT(FILTER(B:J,NOT(ISBLANK(J:J)),NOT(ISBLANK(D:D))),9,FALSE)

Where J has a numeric value, the data is sorted but it's pulling a bunch of null values before it gets to the populated numbers in J.

How do I adjust the =importrange results so that I can have a numerically-sorted (column J) output with the merged data?

can you share a sample sheet with some dummy data - its difficult to visualize what is actually happening in your sheet without seeing an example - you can use pretend data - but it would yield a better solution — Aurielle Perlmann, Commented Jun 26, 2018 at 4:30

Rubén - Volunteer Moderator - · Accepted Answer · 2018-06-26 15:29:15Z

1

Instead of

=iferror(vlookup(D2,List_of_Things!A:N,13,false),"")

use

=iferror(vlookup(D2,List_of_Things!A:N,13,false),)

This because an empty text ("") is not the same as blank.

Alternatively, instead of using ISBLANK use LEN. This because the length of a blank cell and a cell containing an empty text is 0.

46.1k18 gold badges100 silver badges297 bronze badges

Add a comment |

1 Answer 1