Sorting multiple columns with lookup values

No, they're not lookup formulae, they're simple links to cells. You need to use VLOOKUP with the name as the key (search item) and columns F, G and H as the lookup table.

Regards, TMS

Well, this solution is by no means perfect, but I have to run and my mind is drawing a blank. See the attachment that uses a helper row to achieve the sort. This is limited by names being unique within the first three characters. Let me know what you think. Thanks!

1st, absolute your references in col A ($F$2), then change the formula in col B and col C to

=VLOOKUP(A2,$F$2:$H$5,2,FALSE) and =VLOOKUP(A2,$F$2:$H$5,3,FALSE)

To sort the way you want, use...

data/sort/select col B as 1st sort/add level/select col C as 2nd sort

I think this will give you what you want, play around with it some

another method would be to 'rank' each score and do a look up on that, i did this in the attached by creating a unique value for each person by adding B as a whoel and C/1000 (to use as a tie break) and adding a random fractional number to break final ties.

this will automaticaly 'sort' every time the data is updated.

the example attached should make this clearer, with the added columns in yellow, and modified cells in blue

EDIT - figured it out

i think you may have attached the wrong sheet, there is only data in one sheet.

You attached the same workbook as before, maybe you didnt upload the new sheet?

when using the sort option there is no way to automate it on a change, that is why my method requires the 'Rank' columns.

You can either use the code below, copy it into a module, or use macro record (to the right of the "ready" icon) and make your own in the "real" workbook. Then add a button (Insert shape - pick what you want) and assign the macro to it (right click/assign macro). This way, with 1 click, you can update your sort whenever you want

Hope this helps

oops forgot to add the code...

Please Login or Register  to view this content.

Hi eltharion...in my haste earlier I neglected to add the decimal point in the helper formula. This example is working correctly. This method does not require manually sorting or sorting by vba macro, but automatically sorts the data in columns A, B & C by updating the data in columns F, G & H. Limitations of this process are column G cannot exceed max value of 9 and names must be unique after 3 characters (although this could be increased by adding more CODE/MID. formulas). I'm not suggesting you use this vs. one of the other memebers methods, but I wanted to correct my error and show you how this works. Thanks!