Using a formula to link a number (1 through 5) to multiple text choices

Good afternoon.

My questions regards using a formula to select numerous choices from another set of choices in one cell. For instance, I have a question on my survey that asks people to rate their top 5 priorities out of 17 choices. The formula I want to create will need to organize one cell with 1 through 5 rankings; yet each ranking will be different from one person to the other and will be spread over 17 choices.

Choices Rankings
Cars 3
Train 5
Boat 1
House 2
Business 4
Factory
Store
Church


The above shows an example of what my excel sheet will look like. Notice there will be blanks from the other 12 choices. I want the formula to link with what someone answers with a 1 to the choice they selected and can also be changed from one person to the other for all 17 choices. Can anyone help?

Thank you, Rob! I am not too familiar with INDEX or Match. How do you get it to show the other 4 rankings in the row?

It does, thank you. Though, I have another question. Let's say I add a Cities column with 60 possible Cities to the beginning of the columns going down 60 rows. Also, the choices column is now in one row with the choices (as if it is a heading) spread out over 17 columns with the rankings below each of the 17 columns. How do I get the formula to stay with the Cities column but spread out over a row? Meaning, let's say New York answers the survey and so does Chicago but New York answers first. I will have another (This will be a survey sent out to actual cities through SurveyMonkey but not all cities will respond and not in alphabetical order lol). I will be downloading the excel file given by SurveyMonkey and pasting their excel file table into my excel sheet above my excel table (set around row 110 to account for additional room) but I want to have my table ready with the formulas to depict where New York or Chicago is in the SHORT list of respondents (from their excel table) to input the data for these cities in my list of ALL 60 cities. Hope that doesn't sound too confusing but it is frustrating me like crazy.

If I'm understanding your needs correctly, the attached spreadsheet should get you on your way:

Book1.xls

Yes, that is what I meant! The only difference is that the excel table I made can not go to the left since I have other answers to other questions. I have to put my table below it and I have to separate each choice. I didn't know we could attach the excel file...would have been easier lol. Well, here is the excel file back but I made changes to pretty much how mine looks now. So at the very top, that is what survey monkey will give me back. I will copy that and put it into sheet 1 with my table (that you created) down at 110. to automatically input the data. However, the top table does not have all the cities and is not in order and the rest are blank. The table below will have all the cities i'm expecting to hear back. Yet, I want my table to depict where New York is in the list to put it in my New York in my table and know what rank number it is out of the 5 ranks.

Yeah, it does! What does that formula mean exactly?

Ah, well that is really complicated stuff. Thank you for helping me though! By the way, if I have other questions (which I KNOW I will tomorrow), will it be alright to ask you? I will probably send you the actual excel file to get a complete idea with what I mean. The person I am doing this for is being really picky about what she wants.

By the way, I sent you a private message. However, it will not allow me to send you an attachment that way. I submitted a friend request to see if that will allow me to do it, if not could I email it to you?