+ Reply to Thread
Results 1 to 5 of 5

Lookup nth instance and return offset cell

  1. #1
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122

    Lookup nth instance and return offset cell

    I have a spreadsheet that I made a sheet that shows the top transactions by dollar value.
    To do that I put numbers in the first column (BA) and used the LARGE formula referencing the first column to get the nth highest amount ie

    Column BA
    =LARGE(IF(Transactions!$G:$G="Gold",Transactions!$J:$J),AZ6)
    =LARGE(IF(Transactions!$G:$G="Gold",Transactions!$J:$J),AZ7)
    =LARGE(IF(Transactions!$G:$G="Gold",Transactions!$J:$J),AZ8)

    To get the name and the date of the transaction I was trying and INDEX LOOKUP function ie

    Column BB
    =INDEX(Transactions!$D:$J,MATCH($BA6,Transactions!$J:$J,0),1)
    =INDEX(Transactions!$D:$J,MATCH($BA7,Transactions!$J:$J,0),1)
    =INDEX(Transactions!$D:$J,MATCH($BA8,Transactions!$J:$J,0),1)

    Column BC (basically the same but referencing the 2nd column after $D)
    =INDEX(Transactions!$D:$J,MATCH(BA6,Transactions!$J:$J,0),2)
    =INDEX(Transactions!$D:$J,MATCH(BA7,Transactions!$J:$J,0),2)
    =INDEX(Transactions!$D:$J,MATCH(BA8,Transactions!$J:$J,0),2)

    This only finds the first instance where the amounts in column $BA are the same, see picture below.
    How can I adjust it so I find the cell that is offset from the nth occurrence instead. In the actual datasheet the name is in column D and the date in column E.
    EX1.png

    Thanks
    Attached Images Attached Images

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Lookup nth instance and return offset cell

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122

    Re: Lookup nth instance and return offset cell

    Ok here is an example file
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Lookup nth instance and return offset cell

    If you have a reasonably up-to-date version of O365, do it all in ONE cell using:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If you need to adjust the range... change the bit in red. To adjust the number of values returned, change the bit in blue.

    If your Excel version is not up-to-date enough, please report which VERSION of O365 you are using.
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    04-21-2006
    Location
    Australia
    MS-Off Ver
    O365 PC Version 2301
    Posts
    122

    Re: Lookup nth instance and return offset cell

    That works a treat.
    Thankyou

+ 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] Lookup first & last instance of text in range & return column heading data
    By Runner101 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 01-31-2022, 02:10 PM
  2. Lookup most recent date and return 1 cell offset
    By Kozbot in forum Excel General
    Replies: 1
    Last Post: 04-28-2020, 01:49 PM
  3. Using VBA, want to return 2nd instance of a lookup value
    By thecdnmole in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2015, 01:42 PM
  4. Replies: 3
    Last Post: 06-01-2014, 10:26 AM
  5. Return the instance of the value in the column in the next cell
    By witcomm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-21-2012, 04:58 AM
  6. Return Offset Value from Lookup
    By opg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2008, 04:18 PM
  7. Return offset data/values with lookup...?
    By dugong in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-07-2007, 07:41 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