Lookup and find highest..

[Socom]

I have a weird problem so I have attached the file as I see you usually ask for it.

Basically, I want to have it look up and down the classes, find all the cars as an example in the A Class, then find the one with the highest number and then display the Lane Number associated to it.

I included my sheet and what I think the results should be, just not sure how to get it.

Please help!

And thank you

[Paul]

This array formula will give you the highest and second highest points for each class (after typing this formula, or copying and pasting, you must press CTRL+SHIFT+ENTER, not just ENTER!):
=LARGE(IF($B$2:$B$42=RIGHT(J2,1),$H$2:$H$42),1)
=LARGE(IF($B$2:$B$42=RIGHT(J3,1),$H$2:$H$42),2)

You could replace "RIGHT(J2,1)" with "A", "B", etc. based on your class, too, but then you can't copy the formula down to the other classes.

To get the lane you *could* use:
=INDEX($A$2:$A$42,MATCH(L2,$H$2:$H$42,0))

**BUT, when looking for the max for Class B (70), you have matching points values in column H (for a Class C car) - so it will pull the first Lane instance where 70 points occured, which was the Class C car (H5), not Class B (H12)

Perhaps someone else can come up with a way to get the correct Lane by finding the combination of class and points. I know it can be done, but my brain isn't working very well right now!!

[Socom]

Well that works perfect for getting the numbers for the main lane number results, but as you pointed out, it wont show the actual lane if we have more then two with the same amount. The other thing is, I dont see how to get the wild cards, I would need those to be the next highest numbers not including the 12 we have removed for the lane ones. Hope that makes sense.

And thank you!

[daddylonglegs]

What would happen if you had a 3 way tie in any class, or a 2 way tie for second, how do you pick the top 2?

[Socom]

Honestly, no idea. I am hopping I wont have that happen. Maybe if that is the case, ill move the one that is part of the tie in to one of the Wildcard spots.

[Socom]

Anything else I can do to get this to work right?

[rylo]

Hi

If you use pjoaquin's formulas in L2 and L3 (then copy down), try (array entered)
K2: =INDEX($A$1:$A$42,MAX(($B$2:$B$42=RIGHT(J2,1))*($H$2:$H$42=L2)*ROW($H$2:$H$42)))
Copy down to L18.

Only difference I get from your results is for Class A where the max points are 66 and 63, not 63 and 57.

May have something for the wildcards. Have a look at the attached, and see if it gives you something you can work with. Heap of "helper" columns, but hopefully you can follow the logic.



rylo