Ranking Spreadsheet (multi variable Hlookup)

i have this spreadsheet for a football pool. The place is determined as follows:

1. The most amount of wins
2. if two or more people tie the # of wins then the person with the lowest tiebreaker guess wins and the other person becomes the next place.
3. If two people tie the number of wins and the tiebreaker for say 1st place the next highest win and lowest tiebreaker should place 3rd since there are two people above him.

Attached is a spreadsheet of the problem. The solution must only use the info in the "Given" section. You may reorder the data (rows moving up or down, not columns). Solution required is in Yellow.

I could figure out the logic IF there is a way to do a multi conditional hlookup. So if you know of a way to use an "And" in the condition and pull up one row as a result, i can probably figure out the rest.

TIA

My guess is if it can be done the solution will be provided by DaddyLongLegs or Shg, but answers from any are welcome!!!!!!

You can do this without adding any helpers etc...

Firstly you should alter your formulae for B13:B22 as presently these are not correct

B13: =LARGE($C$7:$V$7,COUNTIF($C$7:$V$7,"<="&N(B12))+1)
copy down

Next formula to enter is for Pts -- enter as follows:

E28: =LARGE($C$7:$V$7+(($C$8:$V$8)/10000000),$A28)
Note this is an array so you MUST commit this using CTRL + SHIFT + ENTER
Once the array is set the formula will be encased within { }
Once set copy E28 down for E29:E37
(ensure E28:E37 is formatted to 2 decimals)

Next formula to enter is for NAME -- enter as follows:
C28: =INDEX($A$9:$V$9,1,MAX(IF($C$7:$V$7+(($C$8:$V$8)/10000000)=$E28,COLUMN($C$7:$V$7))))
Again this is an array and so must be committed using CTRL + SHIFT + ENTER

Next formula to enter is for TieBreaker -- enter as follows:
D28:=INDEX($A$8:$V$8,1,MAX(IF($C$7:$V$7+(($C$8:$V$8)/10000000)=$E28,COLUMN($C$7:$V$7))))
Again this is an array and so must be committed using CTRL + SHIFT + ENTER

You could avoid using the array's if you're prepared to have a helper cell -- this would hold a unique ranking based on the above approach ie add the tiebreak amount / 10,000,000 to the Win -- this would in turn give each person a totally unique value which you can then use within a SMALL / LARGE approach... assuming of course the TIEBREAK is always unique where WinPts are the same -- this I'm assuming to be the case.

In truth I should have added that I regarded B13:B22 as incorrect given the fact that you were counting occurrences of the score in C I in turn presumed that B should be returning the top 10 unique Wins score... in hindsight I think I've misinterpreted so apologies.

In addition I have just proved myself to be a complete imbecile as I was working on the assumption that the higher tie break score was better which is categorically not the case so my last post but one is entire garbage.

A great start to 2009 :-)

Revised formulae based on the fact that LOWER tie break is BETTER...

E28: =LARGE($C$7:$V$7+((1000-($C$8:$V$8))/10000000),$A28)
Commit with CTRL + SHIFT + ENTER and then apply to E29:E37

C28: =INDEX($A$9:$V$9,1,MAX(IF($C$7:$V$7+(((1000-$C$8:$V$8))/10000000)=$E28,COLUMN($C$7:$V$7))))
Commit with CTRL + SHIFT + ENTER and then apply to C29:C37

D28: =INDEX($A$8:$V$8,1,MAX(IF($C$7:$V$7+(((1000-$C$8:$V$8))/10000000)=$E28,COLUMN($C$7:$V$7))))
Commit with CTRL + SHIFT + ENTER and then apply to D29:D37

In essence the same as before but only now when adding the tie break score to the Win Pts we subtract it from 1000 prior to dividing by 10,000,000
End result: the lower the tie break score the greater the increment and thus the higher the resulting value making it easy to use with LARGE.
(note assumes tie break score is always >= 0 and <= 999 -- if the score sits outside of those boundaries the above will need adjusting)

If you mean

"Can I use classic VLOOKUP function with multiple conditions"

Answer is No
(not withstanding the possibility that you create a Concatenation key)

If on the other hand you mean:

"Can I replicate the functionality* of a VLOOKUP using multiple conditions ?"
(*ie return value from table)

Answer is Yes -- see the solutions provided.