05-05-2008, 18:25
|
#1
|
|
Vista Ultimate User
Join Date: Dec 2005
Location: Sheffield
Services: 1 V+ Box, 1 V Box, XL TV, L Broadband, L Phone Samsung SGH-U600 Virgin Mobile Contract
Posts: 1,551
|
Excel Formula
Got this league table here and I was just wondering in cell K22 could i use a formula so the cell will show the letter of the team that is currently winning in league 1?
Attached the file so you should be able to see what I mean
Thanks
__________________
Virgin 1 is going to kick Sky 1 in the backside
|
|
|
|
05-05-2008, 18:34
|
#2
|
|
cf.member
Join Date: Jun 2006
Posts: 39
|
Re: Excel Formula
I am sure a formula could be used to get what you want...whick column shows the results of the winning team?
__________________
 Don't ask me....I know nothing..
|
|
|
|
|
07-05-2008, 11:02
|
#3
|
|
cf.member
Join Date: May 2008
Posts: 2
|
Re: Excel Formula
I'd guess your after something like this:
=left(trim(lookup(1,q3:q8,a3:a8)), 1)
I haven't tested it, but it should retrieve the first letter in column a for the the first value in column q that equals the value 1.
Cheers
Tryster
|
|
|
|
|
07-05-2008, 12:44
|
#4
|
|
"Why I oughta..."
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 2meg bb, radio, tel, and a lobster (but the lobster died).
Posts: 3,051
|
Re: Excel Formula
Quote:
Originally Posted by Tryster
...I haven't tested it, ...
|
I don't think vlookup works if you want to return a value to the left of the value you are looking up. But nice try, and welcome! 
This formula looks like a monster, but it does work. Paste it into cell K22:
Quote:
|
=LEFT(INDIRECT(ADDRESS(ROW(Q3:Q8)+MATCH(MIN(Q3:Q8) ,Q3:Q8,0)-1,COLUMN(Q3:Q8)-8)),1)
|
Last edited by TheNorm : 07-05-2008 at 12:49.
Reason: Added a welcome to the new member!
|
|
|
|
|
07-05-2008, 19:04
|
#5
|
|
cf.member
Join Date: Jun 2006
Posts: 39
|
Re: Excel Formula
This one also works.....
=IF(Q3=1,J2,IF(Q4=1,K2,IF(Q5=1,L2,IF(Q6=1,M2,IF(Q7 =1,N2,IF(Q8=1,O2,""))))))
__________________
Don't ask me....I know nothing..
|
|
|
|
|
08-05-2008, 11:31
|
#6
|
|
cf.member
Join Date: May 2008
Posts: 2
|
Re: Excel Formula
Thanks for the welcome.
It's using the lookup function as opposed to a vlookup for that very reason. The lookup function is a legacy function left in for backwards compatability.
I guess I should stop being so lazy and actually test it, but why break the habit of a lifetime 
Cheers
Tryster |
|
|
|
|
08-05-2008, 12:50
|
#7
|
|
"Why I oughta..."
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 2meg bb, radio, tel, and a lobster (but the lobster died).
Posts: 3,051
|
Re: Excel Formula
Quote:
Originally Posted by Tryster
.... The lookup function is a legacy function ...
|
Ahh, legacy functions. Sometimes I think Bill Gates must wonder why he let himself be talked into keeping them!
Well, I tried your untested solution, and guess what - it worked!
There is a lesson in there somewhere... |
|
|
|
|
08-05-2008, 16:10
|
#8
|
|
Vista Ultimate User
Join Date: Dec 2005
Location: Sheffield
Services: 1 V+ Box, 1 V Box, XL TV, L Broadband, L Phone Samsung SGH-U600 Virgin Mobile Contract
Posts: 1,551
|
Re: Excel Formula
Cheers for that guys it now shows me the winning team! Thankyou
__________________
Virgin 1 is going to kick Sky 1 in the backside
|
|
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT +1. The time now is 14:19.
|
Join CF
You are here: 
