+ Reply to Thread
Results 1 to 3 of 3

Returning cell reference of value obtained from array obtained using Large() formula

  1. #1
    Registered User
    Join Date
    12-09-2010
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    21

    Returning cell reference of value obtained from array obtained using Large() formula

    Hi,

    I'm trying to return the cell reference of a value gotten from an array with the Large() formula. I'm talking about the reference of that number within the array. So for example, say the 3rd largest value within the range is 45 - I want to be able to pull back the fact that that value is in cell B4 within that array.

    The Large() Formula in question is:

    =LARGE(Sheet2!$B$3:$H$24, $H21)
    Can anybody think of a way to do this? I've tried fiddling around with Address(), but can't think of an intelligent way to get the row or column numbers.

    Any help much appreciated.

    Best,
    S

  2. #2
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: Returning cell reference of value obtained from array obtained using Large() formula

    I came up with this that can do it in one column.

    =CELL("address",INDEX(Sheet2!B3:B24,MATCH(LARGE(Sheet2!B3:B24,1),Sheet2!B3:B24,0)))

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Returning cell reference of value obtained from array obtained using Large() formula

    If the numbers are all unique in Sheet2, then try:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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