+ Reply to Thread
Results 1 to 8 of 8

Find value in range and return data from another column

  1. #1
    Registered User
    Join Date
    09-09-2013
    Location
    St. John's
    MS-Off Ver
    Excel 2010
    Posts
    13

    Find value in range and return data from another column

    Hi everyone

    I have attached a file to make this easier. Basically I would like to search "Points Scored" (B2:W9) for the highest score (which I already have done) and when it is found return the team's name that is associated with it. Then do this for the second, third, etc. as it can be seen in the "Main" sheet. I'm thinking an index/find approach, but I just keep getting "N/A". I must be missing something. Any help would be greatly appreciated.


    Thanks
    McG.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Find value in range and return data from another column

    You mean highest scoring team per week? Or total?

    This would be per week:

    Place on sheet 'Points Scored'
    =INDEX(A2:A9,MATCH(MAX(B2:B9),B2:B9))
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  3. #3
    Registered User
    Join Date
    09-09-2013
    Location
    St. John's
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Find value in range and return data from another column

    Thanks for your incredibly fast reply! I am looking to continuously have the top 10 scores (and bottom 10) for the entire year. So the list on the "Main" sheet would be like:
    1. Team 5 - 419 (week #12)
    2. Team 7 - 416.25 (week #1)
    3. Team 5 - 408.25 (week #9)
    etc.

    Your response did give me an idea for more data to display, so thank you

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Find value in range and return data from another column

    Since you use excel 2010 you can also use a pivot table to make a top 10 (or top 15).
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Find value in range and return data from another column

    Use Control + Shift + Enter to place this formula.

    NOTE! This wasn't originally written by me, I had a similar issue a while back and got help from these forums. I did modify the original formula to fit your sample and desired result though...

    To get different scores, change the highlighted number with the rank you want.

    I posted this;

    =INDEX($A$2:$A$9, LOOKUP(SUM((($B$2:$W$9=LARGE(B2:W9,1)) * ROW($B$2:$W$9))), {1,2,3,4,5,6,7,8})-1) & " - " &LARGE(B2:W9,1)&", ("&INDEX($B$1:$W$1, LOOKUP(SUM((($B$2:$W$9=LARGE(B2:W9,1)) * COLUMN($B$2:$W$9))), {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22})-1)&")"
    (Original formula by JBeaucaire in this thread)

    but use this one instead

    = INDEX(A2:A9,MATCH(TRUE,MMULT(--(B2:W9=LARGE(B2:W9,1)),TRANSPOSE(COLUMN(B2:W9)^0))>0),0)
    &" - "&LARGE(B2:W9,1)&", ("&INDEX(B1:W1,0,MATCH(TRUE,MMULT(TRANSPOSE(--(B2:W9=LARGE(B2:W9,1))),(ROW(B2:W9)^0))>0))&")"

    Original formula by Bob Phillips in this thread
    Last edited by Speshul; 07-10-2014 at 10:57 AM.

  6. #6
    Registered User
    Join Date
    09-09-2013
    Location
    St. John's
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Find value in range and return data from another column

    This works perfectly! Thank you so much. Both options. You're both great.

  7. #7
    Registered User
    Join Date
    09-09-2013
    Location
    St. John's
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Find value in range and return data from another column

    OK. Another challenge. How would this work to get the 5 lowest scoring teams?

  8. #8
    Registered User
    Join Date
    09-09-2013
    Location
    St. John's
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Find value in range and return data from another column

    Sorry. Never mind. I just cleared any "0" instances and I got it working. Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find first date of the month in range and return column number
    By jben86 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-06-2013, 08:30 PM
  2. [SOLVED] Find specific cell and return column number from a dynamic range
    By nivoe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2012, 12:39 PM
  3. [SOLVED] Find text in a column or range and return value of adjacent cell
    By chemoul in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2012, 04:30 AM
  4. Replies: 5
    Last Post: 09-02-2011, 09:17 AM
  5. Find last numerical value in row range and return the column header
    By Fidd$ in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-04-2011, 10:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1