+ Reply to Thread
Results 1 to 7 of 7

Finding last column of match value in range

  1. 01-06-2020, 04:37 PM #1
    yuvalmoshe
    yuvalmoshe is offline
    Registered User
    Join Date
    01-06-2020
    Location
    israel
    MS-Off Ver
    excel 16.30 for MAC
    Posts
    5

    Finding last column of match value in range

    Hi,
    I have a range of values spreading across multiple rows & columns.
    A cell value within this range is not unique (it could appear in various rows/columns.
    I am trying to extract the last column in which the specific value appeared in the range.

    Example:
    each row is a person.(assume 3 rows)
    each column is a month. (assume 3 columns)
    a person eats one fruit in a month.

    I need to extract per fruit what is the last month in which it is eaten (doesn't matter if it appears in more than 1 row or not).
    i.e - Mango should return "mar". Apple should return "jan", watermelon should return "feb".

    jan feb mar
    Mike Apple Mango Mango
    Sandy Watermelon Mango Mango
    John Apple watermelon Mango

    I've tried using all kind of index/match functions, but didn't succeed.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-06-2020, 05:07 PM #2
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Finding last column of match value in range

    Try in B7

    =INDEX($B$1:$D$1,MAX(INDEX(($B$2:$D$4=$A7)*(COLUMN($B$1:$D$1)),0))-COLUMN($A$1))

    Copy down
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  3. 01-06-2020, 05:08 PM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Finding last column of match value in range

    Hello yuvalmoshe. Welcome to the forum.

    Try this in cell B7 and fill down.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  4. 01-06-2020, 05:11 PM #4
    PeteABC123
    PeteABC123 is offline
    Valued Forum Contributor PeteABC123's Avatar
    Join Date
    09-21-2012
    Location
    Chicago, IL
    MS-Off Ver
    MS Office 365 ver 2202
    Posts
    1,104

    Re: Finding last column of match value in range

    Ya'll are brilliant. FlameRetired and Ace_XL.

    Pete
    Reply With QuoteRegister To Reply
  5. 01-06-2020, 05:26 PM #5
    yuvalmoshe
    yuvalmoshe is offline
    Registered User
    Join Date
    01-06-2020
    Location
    israel
    MS-Off Ver
    excel 16.30 for MAC
    Posts
    5

    Re: Finding last column of match value in range

    Wow ! amazing. works like a charm.
    Thanks Ace_XL & FlameRetired. happy to join the forum
    Reply With QuoteRegister To Reply
  6. 01-06-2020, 06:43 PM #6
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Finding last column of match value in range

    You are welcome. Glad to help. Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  7. 01-06-2020, 06:44 PM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Finding last column of match value in range

    @ PeteABC123

    Thank you for the kind words and encouragement.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Problem with finding a MATCH in a Range
    By j_Southern in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2019, 09:25 AM
  2. Finding partial match in a range
    By miucus in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-24-2019, 10:14 PM
  3. Finding value from a range and if not match then find next
    By annux3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-22-2015, 08:44 AM
  4. Excel 2013, finding match in cell from range (column)
    By Rob de QUartel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2014, 05:58 AM
  5. Finding exact number match in column. If no match, remove one number and check next column
    By v0idern in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-19-2013, 02:19 PM
  6. [SOLVED] Finding MIN value within range and returning MATCH
    By MaddyG in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-16-2012, 03:50 PM
  7. Range match finding
    By calsexceladel in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2008, 08:42 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1