To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Lookup query

A forum to discuss or share knowledge of spreadsheets.
Post Reply
User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Lookup query

Post by barry »

got a big spreadsheet with scores and shite, too big to print off/too complicated to email round, so basically trying to get a summary table done.

to keep it simple lets say I'm using Cells A2, B1 and B2. the rank is in A2, the score in B2 and the name in B1, i want to be able to create a wee table on a separate sheet where column A shows 1-16 (number of players) Column B returns the score based on the rank (HLookup I think) but can't for the life of me think how to get Column C to return the name...


cheers in advance.

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: Lookup query

Post by Surprised »

Why not put the name in c2 to begin with?

Assuming that's not possible and assuming each rank is unique (ie only 1 player can be ranked 5) then I think it can be solved with MATCH+INDEX.
Ie ask it where rank 5 is (A2 in this case) and then the correct formula will go up a row and a column to the right to select the name.
I can't do an example now as I'm too knackered to think straight but I can look tomorrow if no one has solved your problem by then.

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Lookup query

Post by barry »

Surprised wrote:Why not put the name in c2 to begin with?
:lol: that might just work! :oops:

it's an existing spreadsheet where c2 is occupied, but I suppose inserting a row shouldn't fk up the formulae..... will have a look.....

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Lookup query

Post by barry »

nope, can't go in C2, might be able to put it in b3 though....

User avatar
Mystery
FISO Knight
Posts: 13816
Joined: 13 Oct 2005, 18:33
Location: Just about here
FS Record: 116th in TFF 05/06
Contact:

Lookup query

Post by Mystery »

Your problem might be dealing with ties for position in which case I'd add to their score a tiny fraction (1/10,000th) of their row number just to differentiate

User avatar
Surprised
FISO Jedi Knight
Posts: 26528
Joined: 13 Oct 2005, 18:32
Location: Home
FS Record: TFFOSM MotW in 2008 and MotM in 2003. 78th overall in TFFO for 2002/3 and 2003/4

Re: Lookup query

Post by Surprised »

barry wrote:nope, can't go in C2, might be able to put it in b3 though....
A3 is better as it is below the rank and then you can use Hlookup

is the score in the summary sheet the same score as in your first sheet as mentioned above?
or is it a different score based on the rank?

User avatar
barry
FISO Knight
Posts: 18871
Joined: 13 Oct 2005, 18:43
Location: moving into fisodas towers
Contact:

Re: Lookup query

Post by barry »

got it working under b3, but then realised this problem.....

Mystery wrote:Your problem might be dealing with ties for position in which case I'd add to their score a tiny fraction (1/10,000th) of their row number just to differentiate

you're some guy Mystery :shock:

want to break that down a bit for me - don't quite understand :oops:

User avatar
crispybits
Dumbledore
Posts: 5499
Joined: 10 Aug 2010, 09:18
FS Record: FISO Apprentice Champion 2011-2012, Xpert11 FBT winner season 7

Re: Lookup query

Post by crispybits »

Easiest way (not necessarily the most efficient way) to do the coding for that Barry would be to add a column, it has 1,2,3,4,5 all the way down, then add that colum multiplied by 0.0001 to the total score in each row. You'd always have the same ordering on the tie-break that way but you can always easily manually adjust it if needed by swapping the numbers around in the created column for the two tied players.

I'm sure someone will give you a more technical 1 cell way of doing it but that's the way I'd use because I'm lazy, it would work, and it wouldn't require complicated coding.

Example:

Assume column B is total score, and column C is used, column D becomes 1,2,3,4,5,etc all the way down, and in the total score you change it to XXX+(D2*0.0001) then adjust the D column manually to put the tie breaks in the order you want them.

User avatar
Mystery
FISO Knight
Posts: 13816
Joined: 13 Oct 2005, 18:33
Location: Just about here
FS Record: 116th in TFF 05/06
Contact:

Lookup query

Post by Mystery »

Sorry Barry didn't see your reply.

To the score add +(ROW()/10000)

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”