RANK numerically and then alphabetically

hi guys i have a table that is ranked using this formula =RANK(A2,A2:A5)

the cells that it is ranking is using this formula =Z3+(Y3/100)

which gives me x.xx result now there still seems to be a problem with data clash for vlookup is there a way i can make it rank via that same formula and then check alphabetically on say cell A1.

basically i am trying to stop vlookup from spitting out ## or #N/A, so if there is any other way i would be very grateful if someone could let me know

THANKS

Originally Posted by sweep

Instead of using VLOOKUP, you could use index and match which doesn't require your data to be in order.

=INDEX(column_of_return_values,MATCH(lookup_value,lookup_column,0))

right im a bit of a newbie tbh umm kind of understand um if i attach the document could you see if that would work? because i dont think its quite the right formula or even if there is 1

Originally Posted by sweep

I guess you're talking about worksheet "Tables", starting at cell B2?

Use this formula

=INDEX($T$2:$T$5,MATCH(A2,$S$2:$S$5,0))

in B2, and copy to the remainder of group A. Then modify the formula for groups B through D..

i have just tried this and it does not work as 1 the rest of the table doesnt change withe the team, and i still ahve ## and #N/A basically i am trying to take the top 2 from each group to go into the correct knockout slot for example i tried using this formula which works but when there is ## it doesnt understand it and breaks lol so yea i mean i could put something else in or something rather than the rank being wrong

(if statement i tried on "sheet knockout" cell b4

=IF(Tables!C2+Tables!C3+Tables!C4+Tables!C5=12,Tables!B2,"WINNER GROUP A") workis completely untill there is ##

Originally Posted by sweep

You have a couple of problems.

To make the rest of the table change with the team, you'll have to adapt the formula I gave you.

The match (or lookup for that matter) formula will only work (unless heavily modified) by identifying the 1st occurance in the lookup array.

yeah ok, but is there no way to get rid of the ## as if i can get rid of that it will work fine or a way for the formula i tried to use (in the last message) to see ## as a 0 not a invalid value/no value as that will solve the problem alot easier..... if not i will have to try and figure out the formula u gave me its just i dnt really know how to use that formula lol

Change your ranking formula to be like this.

S2=RANK(AA2,$AA$2:$AA$5)+COUNTIF($AA$2:AA2,AA2)-1

Regards