Solved Excel sorting problem with VLOOKUP results

March 9, 2012 at 20:44:52
Specs: Windows 7
I seem to be running into a little (alright, maybe not so little for me) problem with my database in Excel.

I have the following general setup;
Page1 holds a 'master list' of data. Nice little rows of everything i need to have on file for each object in the list.
On Page2 i have a cozy family of VLOOKUP functions, which grab several of the colums of Page1 for every line, giving me sertain cets of attributed i can print into comprehendable lists.

Page1 is sorted, for convenience, by column a, in ascending order.
But I'd like Page2 to sort itself by column b, in descending order.
However, since there are a few dozen 'empty' cells (Holding a VLOOKUP that does not find anything to report.), these cells drift to the top of my descending list, pushing the data I actually need from view.

Evidently, my knowledge of the finer workings of Excel is, -ahem-, somewhat limited, so if there's anyone there who is now going; "Dude, just do *solution*!" I'd rather appreciate him or her speaking up.


See More: Excel sorting problem with VLOOKUP results

Report •

March 10, 2012 at 07:35:57
If your using a =VLOOKUP() I'm guessing your using a null/blank as the return value if the =VLOOKUP() fails, correct?

If yes, then simply give the =VLOOKUP() a different default value,
something like the letter Z if you want it to sort to the bottom,
or an exclamation mark ! if you want it to sort at the top.

See here for how Excel sorts:


Report •

March 10, 2012 at 07:44:27
✔ Best Answer
You could also use a Helper column:

You Sort on column A
Create a column next to your sorting column, new column B,
you can even make it hidden if you want,
then enter a formula in column B, like, =IF(A1="","zzzzzzz",A1)
and then sort on column B.


Report •

March 10, 2012 at 10:18:58
In additon to Mike's fine suggestion about using something other than a "blank" in your VLOOKUP, you could use Conditional Formatting based on that value so that when it sorts to the bottom, it disappears.

For example, if you use something like this, all of your ISNA VLOOKUPs will sort to the bottom when you sort in descending order. Note the use of -1 as my ISNA result.


Now assuming I put this formula in D1 and drag it down, I can use =$D1=-1 as a Conditional Formatting rule for C1 through the bottom of Column D. If I set the Font Color to match the cell fill color (white by default) then everything associated with -1 will not be seen.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

March 13, 2012 at 00:12:42
U can always have a look at few freely available software's which are coming up quite strong in this league, I found a tool which will hit the market very soon its CollateBox looks very neat and interesting.

Report •

Ask Question