LOOKUP not working, how bizarre

Try this Array Formula. Remember Array Formula requires special keystrokes Ctrl+Shift+Enter instead of just Enter which you normally use in case of a regular excel formula.

In D17

Please Login or Register  to view this content.

and then copy across the row.

Is this what you are trying to achieve?

Originally Posted by CatSqueezer

.....do you know why LOOKUP didn't work, or rather, why it only worked for the highest score, and then stopped for the next and the next etc.?

For LOOKUP to work the lookup range (J2:J11) needs to be sorted ascending.....and yours is not. That could be remedied by using INDEX and MATCH but that still wouldn't cater for duplicates, which is why you need a more complex solution for the layout that you have

To accommodate 40 questions expand all the ranges in the formulae that are now like $J$2:$j$11 to $J$2:$J$42.

I just made the rows for the ranges be 40 the formula in J2 would be

Formula:

Please Login or Register  to view this content.

The formula given by sktneer will work the same way

Formula:

Please Login or Register  to view this content.

I assume that you are not having any issue with the formula in the score col. J. If so, you can see what Ron has suggested in his post#10.
As far as your current formula is concerned, earlier your scores were in the range J2:J11, then the formula suggested by me worked. But now if you increase the data in col. J that means you have increased the data in all the columns which are referenced by the formula in score column i.e. col. J. Therefore in this case you will have to adjust your formula which was earlier for row 17 to any row down below the row 42 (since now you have raw data up to row 42). So say now you want to input this formula in D47, your formula would be like this.....

Please Login or Register  to view this content.

Does that help?

If still not getting the desired output, please upload the workbook with the formula applied.

@sktneer

I think that the OP was also having problems with the calculations with the data for column J not realizing that the formulae there also had to be adjusted to take care of the 40 rows.

Hi,

I've really run into a lot of difficulty altering this spreadsheet (the version you gave me in Post #2 of this thread). I have the following requestions, and l hope you can help me:

# Please could you move W14 to W17 (it doesn't seem possible, l don't know why)
# Also, l need the row to give Player, then Player's Score. Currently, Row 17 gives Player's Score, then Player.
# Also, l need a duplicate set of scores arranged in a table, with the highest score at the top, the lowest at the bottom, and the corresponding player's names to the left of each score (just as in cells i2 to j11, but this time, arranged in descending numerical order, just as in Row 17)
# Also, l need the scores on Row 17 to be shifted out of the way, so that they begin in column I, preferably Row 20, but Row 17 would do, as long as the results begin in Column I instead of Column D as they do at present.
# Also, l need the scope of the spreadsheet extended to 40 questions (see Column A), instead of the present 10 questions.


I understand that you have given me a solution for extending the total no. of questions, but the other alterations l need to make (see the list above) just complicate everything. However, l could take the hints you've already given, to extend the number of quiz questions catered by the spreadsheet myself.

However: the other alterations (see above) still stand and l've tried to do it myself but don't seem to be getting anywhere, l really have tried. Please could you help?

I have included the version of the spreadsheet l am working on right now, as an attachment - it is basically the same as the one you have given me in Post #2 of this thread.

Do you have access to Excel 2007 or later? I don't have Excel 2000 and don't know what functions it doesn't have that are available in the new versions.

I think that you should contact sktneer about this. I have no idea what will or will not work in Excel 2000 as I never had that version and am using Excel 2010. It would probably be better to post another question with the revised requirements. I have revised your workbook to handle 40 questions.

I didn't solve the formula for row 17 because I couldn't be certain that my solution would actually work. I did however "shove" it across to start where you want.

I really think that the horizontal arrangement is difficult to read....why not just have a table that you can sort into any order that you want and it is easy to read?

Originally Posted by newdoverman

I think that you should contact sktneer about this. I have no idea what will or will not work in Excel 2000 as I never had that version and am using Excel 2010. It would probably be better to post another question with the revised requirements. I have revised your workbook to handle 40 questions.

I didn't solve the formula for row 17 because I couldn't be certain that my solution would actually work. I did however "shove" it across to start where you want.

I really think that the horizontal arrangement is difficult to read....why not just have a table that you can sort into any order that you want and it is easy to read?

Hi,

Can l see your version? I don't think it's uploaded.

And re: the horizontal arrangement - it exists so that the Quiz Master can copy and paste the scores directly into the chatroom where the quiz is being held, after every single question and possibly also at the end of the quiz when the final scores are given out. The horizontal arrangement is compact when copy-pasted into the chatroom, hence especially suited for giving scores out at the end of each question.

However, if the max. paragraph size for that chatroom permits it, l wanted there to be available a vertical representation of the scores too.

Finally, the vertical representation can facilitate (slightly more than the horizontal representation) the copy-pasting of the scores player by player, which enables participants to cheer for each player individually and gives an element of suspense at the end of the quiz.


By the way, please don't see this as a "do my homework for me" question, l really did try to shift the fields around the worksheet, but got really weird results every time.

P.S. I have also asked sktneer to help revise the spreadsheet (see a few posts up).

Ok here is the file that somehow didn't get included with my last post. I will also include a file that I created using Excel 2010 in case you can get to view it for ideas. The results of my trying to change the formulas in the horizontal setup were totally bizarre so I "chucked" them.

It's too bad that you can't use a more up to date version. It is easier to use (I think) and has more features. I was able to quickly accomplish what you asked by using a much different approach for handling the data including putting the results in a table that allowed the sorting of the results to give a list ordered by name or score. The horizontal row was created by creating a vertical listing of the table then using TRANSPOSE to make a horizontal listing. That however isn't part of Excel 2000.

If you post another question, perhaps sktneer will see it and participate.

The order of row 17 is directly dependant upon the order of the listing in Sheet1!I1:J6. Sort the table and the order of row 17 changes accordingly.

You are running up against the limitations of the old VS new versions of Excel.

I really don't understand your vertical requirement. You already have it in I1:J11 which can be in any order that you want.

The horizontal arrangement is directly tied to the order in I1:J11. What ever order you arrange I1:J11 is reflected in the order row 17.

To eliminate the possibility of sorting the Quiz Master, I moved it. It will however remain at the end of row 17 with the cells before it being filled from the contents of I1:J11

I deleted all parts that I could find that no longer affected the calculations.

I don't think that there is anything more that I can do.

In your message #26 you said "I2:J11 (it's actually all the way to J11, the quiz master automatically gets 10 points) must never be sorted". I understood that to mean that the quiz master was never to be sorted.

Ok, I moved the quiz master back into a sortable position and gave you a place to enter contestants in the order that you want without any built in sorting (from your original worksheet). There is no automation in the worksheet to fill both lists as this would defeat your wish for an unsorted list.

Good Luck.

I think that I have met all the requirements that you have indicated. Seeing what you required in the end was certainly a help. The "sorted" ranges should auto sort.

Thank you for the feedback. It is appreciated.