- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Question: Pulling Information from colums
Posted on 8/11/14 at 10:41 am
Posted on 8/11/14 at 10:41 am
Can anyone tell me the formula for this and if it can be done. I have the following for an example:
John 5 7 9 4
Jane 5 4 3 6
Joes 4 2 2 2
I am looking to find the highest number in the column (obviously 6 for jane since I will only use the last column as it is continuously update). I can easily use the MAX function but I also need to pull the name with the highest number. What is the formula to pull jane and the 6? Next week the highest number might be 9 and Joes
John 5 7 9 4
Jane 5 4 3 6
Joes 4 2 2 2
I am looking to find the highest number in the column (obviously 6 for jane since I will only use the last column as it is continuously update). I can easily use the MAX function but I also need to pull the name with the highest number. What is the formula to pull jane and the 6? Next week the highest number might be 9 and Joes
This post was edited on 8/11/14 at 10:42 am
2
Posted on 8/11/14 at 10:54 am to Hogkiller10
=A1&" "&MAX(B1:E1)
Posted on 8/11/14 at 11:28 am to boobs&bacon
thanks for the help. Semi worked. Still tinkering with it as it isnt pulling up the name
Posted on 8/11/14 at 11:52 am to Hogkiller10
Something like this should work:
=INDEX(A1:A3,MATCH(MAX(E1:E3),E1:E3,0))
MAX gets the value, MATCH gets the row number, INDEX gets the name.
It doesn't account for duplicate maximum values; it will return the name corresponding to the first one.
I don't know how to eliminate the MATCH call.
=INDEX(A1:A3,MATCH(MAX(E1:E3),E1:E3,0))
MAX gets the value, MATCH gets the row number, INDEX gets the name.
It doesn't account for duplicate maximum values; it will return the name corresponding to the first one.
I don't know how to eliminate the MATCH call.
This post was edited on 8/11/14 at 11:54 am
Posted on 8/11/14 at 1:23 pm to Spock's Eyebrow
=INDEX($A$2:$A$4,MATCH(MAX(B2:B4),B2:B4,0))&" "&MAX(B2:B4)
It won't do ties like you have in week 1 though. Sorry I had misunderstood in the first post.
It won't do ties like you have in week 1 though. Sorry I had misunderstood in the first post.
Posted on 8/11/14 at 1:27 pm to boobs&bacon
This is what mine looks like
Posted on 8/11/14 at 3:57 pm to Spock's Eyebrow
quote:
I don't know how to eliminate the MATCH call.
Don't think you can unless you establish a new "ID" column that stores =ROW().
Posted on 8/11/14 at 4:23 pm to boobs&bacon
Thanks a million for everyones help. Just what I needed and actually learned some of the functions once I broke them down.
Posted on 8/11/14 at 4:52 pm to foshizzle
quote:
Don't think you can unless you establish a new "ID" column that stores =ROW().
It's frustrating when you need a reference but can only get a value. Using both MATCH and MAX means searching twice, which is usually more inelegant than meaningfully inefficient, but it still bugs me when I have to do it.
Posted on 8/13/14 at 12:32 pm to Spock's Eyebrow
You could duplicate the names in a column in the far right, then use a vlookup(max()) formula.
Posted on 8/13/14 at 1:05 pm to CubsFanBudMan
quote:
ou could duplicate the names in a column in the far right, then use a vlookup(max()) formula.
Still two searches, though, plus the redundancy of duplicating names.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News