Excel 2010 - Can't sort correctly with VLOOKUP

I've been working on a huge spreadsheet in Excel to help our Principal work out the setting and banding for Year 7.

It's a series of marksheets and timetable and other data which I've exported from SIMS .net, and pasted into a number of sheets on an Excel workbook (having converted all the text to number, natch).

Because some of the data was live, because SIMS .net doesn't populate assessment marksheets with our timetable and because I needed to present the front sheet in a specific order, I didn't want to create a single marksheet within SIMS to pull off the rest of the data, so the front page, which I've presented to the Principal, is basically a series of lookups from each of these other sheets with the odd column running calculations for good measure- eg FSM, SEN, Band, Maths Class, KS2 Result, Exam Result, Difference between KS2 and Exam, English Class...

Each of the "marksheets" has been exported unformatted and in alphabetical order of surname. I've ensured that the name is formatted identically on each sheet.

So far, so good, and when the front page is sorted alphabetically by the first column - name, everything is Hunky Dory, the results are next to the correct names and everything.

However, if I try to sort by any other column (ie changing the order of names on the front sheet from those on the Lookup arrays), then the results in all the subsequent columns do not sort correctly.

I've worked around the problem by copying the sheet with the lookups and pasting to another blank sheet values only, but I'm hoping that I can make the system a bit more automated and avoid having to do this.

Does anyone know what I have to do to make these values sort correctly?

I've no experience of your version of Excel, but in Excel 2003 you must select all the data in every row to be sorted before actually doing the sort. Otherwise, the result is as you describe.

Aha! Turns out that it wasn't the VLOOKUP that was completely at fault. More my hamfisted construction of the arguments.

Only a few columns were faulty, so I thought I'd delve a bit deeper.

Turns out that I'd put the name of the worksheet in front of the cell reference of the lookup value, when it was looking for a value on the same cell. Consequently this was seen as invariable and didn't update automagically as the rest of the sheet did when applying the filters.

I took the worksheet name off and Robert is now my mother's brother!

OK, this has come back to bite me on the bum.

The column that I want to sort contains Finely Differentiated scores calculated using a simple formula bassed on the results of the previous two columns.

If I select the whole sheet, then sort on the column I wish to sort, it sorts the column into a random order. If I then undo and sort again, it sorts the column into a totally different order.

The results are correct if I look along the row, but I cannot for the life of me get it to sort the set of results in order according to this one specific column.

Again, copy the results and paste the values into a new sheet - worksheet works fine.

I cannot google anywhere anyone who's had anything similar.

We've had a couple of us look at it and replicated the error elsewhere.

Column 1 is Names, Column 2 is numbers based on a lookup, matching the lookup value to the name in column 1.
Column 3 is numbers based on a different lookup table, matching the lookup value to the name in column 1.
Column 4 Adds the two columns together, multiplies by 6 and divides by 2.

Sort on Column 1 - OK
Sort on Column 2 - OK
Sort on Column 3 - OK
Sort on Column 4 - randomness.

Copy the values of Column 4 and paste elsewhere - sorts fine.

Anyone got a work-around that doesn't require copy/pasting, so I can enter data in one place and have everything update?

Thing is, I can think lof many occasions when this would be useful...

I have tried to replicate this in Excel 2003 and my columns seem to sort ok...can you post a sample sheet so I can try that?

This is a bit of a stab in the dark, but there are two things that might be worth checking in your data if everything works ok when you copy values to a second worksheet:

• If the fourth column is a calculation on the other columns, check your use of relative and absolute cell references in the formulas.
• If the formula in the fourth column uses named ranges (e.g. the other cells in that row are named and the formula is based on that) then when you sort the formula will refer to the wrong cells. (In other words, the names won't move with the sort.)

Apologies if you've already looked at those possibilities.

HTH.

I'm having the same problem, and I've checked my relative / absolute cell references and I'm not using named ranges. Don't suppose anyone's found a solution?

Aha! Found the solution! The problem was that my VLOOOKUP was referring to its own worksheet by the worksheet name, and when I took that out, it was fine.

An example might help:
On worksheet DATA, I had this formula for a cell:
=VLOOKUP(DATA!A22,CODES!$A$2:$G$664,1,FALSE)

Note that this is in worksheet DATA, and it's using cell A22 in that same DATA worksheet to look up a value from another worksheet (CODES). This caused problems when I tried to sort on the VLOOKUP column.

But you don't need to say DATA!A22 because we're on the DATA worksheet. You can just say A22, and this means everything works! Just change the formula to:
=VLOOKUP(A22,CODES!$A$2:$G$664,1,FALSE)

...and we're laughing! Woo hoo!

Hope this works for you too.

Quote:

---

Originally Posted by tigger

I'm having the same problem, and I've checked my relative / absolute cell references and I'm not using named ranges. Don't suppose anyone's found a solution?

---

Hi I have started to have a similar problem but I am not using vlookup and I was able to sort the sheet previously but it seems once I had added a couple of extra lines of data it would no longer sort properly it drops values and does not put the remaining ones in order. I only want to sort one column and I dont want the rest of the colmns to move. It has worked fine for nearly four years in all different versions of excel until about a month ago cant remember if there has been any updates to excel in that time there was one in september specifically for excel but thats all cant say if the problem coincided with then as I dont perform this function every week.