RANK using Vlookup or MATCH

[brooksc29]

I am trying to say:

Get the text in cell B6, match that text in named range ALBA, then rank the data in N3:N14 for the line that has the text form B6.

how do I combine the two?

Thanks to all!

 

[brooksc29]

i meant to say "then rank the data in N3:N14 for the row that has the text from B6"

 

[barry houdini]

This isn't very clear to me. What range does ALBA represent, does B6 occur only once in ALBA?

 

[brooksc29]

alba represents TMBA!B3:P16

and yes, the text in B6 only occurs once in that range (in column B)

 

[barry houdini]

OK but what relationship does that have to N3:N14, is that in a different sheet?

Lets assume that B6 matches TMBA!B10 does that mean you want to rank N3:N14 against TMBA!C10:P10? I'm not sure how that would work, normally you would rank a single value against a range......

 

[brooksc29]

cell ORANKINGS!B6 equals the text in TMBA!B8

cell TMBA!N8 is data representing the text in B6 for a certain category

i'd like to rank N8 in all the data from N3:N16

these rankings will be displayed on ORANKINGS! and will be applied down the column, depending on the matching text in ORANKINGS!B6:B19

 

[brooksc29]

does that make sense?

sorry to not be clear

 

[Haseeb Avarakkan]

Not sure what do you mean, perhaps;

ORANKINGS!B6, copy down...

=RANK(INDEX(TMBA!$N:$N,SMALL(IF(ALBA=$B6,ROW(ALBA)),1)),TMBA!$N$3:$N$16)

Confirmed with CTRL+SHIFT+ENTER, not just ENTER.

 

[brooksc29]

U DA MAN!

thanks, that works perfectly.

i'm very jealous of your excel knowledge!

 

[barry houdini]

Perhaps you can just use a VLOOKUP, i.e.

=RANK(VLOOKUP(B6,ALBA,13,0),INDEX(ALBA,0,13))