Ranking question

[sharki69]

Hi

I'm comparing 2 locations, and would like to show the ranking I gave to each category in a summary table.

In the attached spreadsheet, I have 2 tabs (for each city) where I rank each category 1-5
In the summary tab I have the comparison for each category and want to show the ranking for each city (e.g. city 1 got a rank of 4 and city 2 got a rank of 5 for the first category)

Thanks fir the help
S

[63falcondude]

Try these:

C4 =INDEX('City 1'!C$3:G$3,MATCH("x",'City 1'!C5:G5,0))
D4 =INDEX('City 2'!D$3:H$3,MATCH("x",'City 2'!D5:H5,0))

Drag both formulas down.

These assume that your sample workbook is representative of your actual one.

[Pete_UK]

When you have subsidiary sheets like this, it helps to have each of them using the same data layout. In your example the rankings are in columns C to G for City1, but in column D to H for City2. Can you standardise these?

Pete

[sharki69]

When you have subsidiary sheets like this, it helps to have each of them using the same data layout. In your example the rankings are in columns C to G for City1, but in column D to H for City2. Can you standardise these?

Pete

You are correct. I deleted column C and the data layout is the same now. Thanks for the comment

[PaulM100]

Try: =INDEX('City 1'!$B$3:$L$3,MATCH("x",INDEX('City 1'!$B:$L,MATCH(B4,'City 1'!B:B,0),0),0))
Change the sheet for City2

[sharki69]

Try these:

C4 =INDEX('City 1'!C$3:G$3,MATCH("x",'City 1'!C5:G5,0))
D4 =INDEX('City 2'!D$3:H$3,MATCH("x",'City 2'!D5:H5,0))

Drag both formulas down.

These assume that your sample workbook is representative of your actual one.

Worked! thanks much

[sharki69]

Try: =INDEX('City 1'!$B$3:$L$3,MATCH("x",INDEX('City 1'!$B:$L,MATCH(B4,'City 1'!B:B,0),0),0))
Change the sheet for City2

Also worked! you guys rock. Thanks

[63falcondude]

You're welcome. Happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Pete_UK]

If the list in column B is the same in all sheets, you could use this in C4 of the Summary sheet:

=MATCH("x",'City 1'!C5:G5,0)

and this in D4:

=MATCH("x",'City 2'!C5:G5,0)

Copy down as required.

Hope this helps.

Pete