+ Reply to Thread
Results 1 to 10 of 10

Return highest value for repeating lookup values

  1. #1
    Registered User
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    68

    Return highest value for repeating lookup values

    hi guys,

    I have a data set (as shown in the attached sample file) in which I am trying to return the highest value or max value from column 2 based on a lookup value in column 1. The values in column 1 will have duplicates as their corresponding values in column 2 will be different.

    I hope the sample file will explain the situation and the goal better if the above statement isn't clear.

    Any help is appreciated

    Thanks

    VJ
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,791

    Re: Return highest value for repeating lookup values

    is your version of excel - still 2010 ?
    If so then
    =MAX(IF(A2:A13=H3,B2:B13))

    if later version then
    =MAXIFS(B2:B13,A2:A13,H3)
    Attached Files Attached Files
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    68

    Re: Return highest value for repeating lookup values

    Hello Wayne,

    Appreciate the help. the formula worked!

    I got a follow up question though. My version of excel is 2016 and I see that in the Account tab. But for some reason, I couldn't use your latter formula. I have had similar issues using XLOOKUP as well. Do you know why this is happening?

    Thanks

    VJ

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,791

    Re: Return highest value for repeating lookup values

    its not in the standalone 2016 version as far as i know https://www.excelforum.com/excel-gen...available.html

    please update profile to show 2016 version

  5. #5
    Registered User
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    68

    Re: Return highest value for repeating lookup values

    Okay. Thanks for your help. Have a great day !

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,791

    Re: Return highest value for repeating lookup values

    you are welcome, thanks for updating, makes it easier in providing a solution for your vesion

  7. #7
    Registered User
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    68

    Re: Return highest value for repeating lookup values

    Hi Wayne,

    An update after using the formula. It doesn't calculate if I change the values in cell H3. I tried it for my actual file and It is returning "0". I've re-attached the sample file after making changes to make it look similar to my actual data.
    The formula only works if the Part numbers are in the same order as the source table.

    thanks

    VJ
    Attached Files Attached Files

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,791

    Re: Return highest value for repeating lookup values

    you don't need the @ in front of the range
    https://support.microsoft.com/en-us/...e-c1c999be2b34

    Also its an Array formula - so needs to be
    entered with Control + Shift + Enter
    and get the {} brackets
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-08-2021
    Location
    Toronto
    MS-Off Ver
    2016
    Posts
    68

    Re: Return highest value for repeating lookup values

    my bad. I didn't use it as an Array formula. It works fine now.

    thank you so much for the help. I truly appreciate it

    VJ

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,791

    Re: Return highest value for repeating lookup values

    you are welcome

+ 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. Replies: 3
    Last Post: 06-18-2019, 03:19 PM
  2. Code for Finding Highest values, Lookup,Offset Values
    By structo in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 09-06-2016, 01:26 PM
  3. [SOLVED] Return row number for highest date with given lookup value (no matrix please)
    By nielsb in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 07-21-2015, 11:06 PM
  4. [SOLVED] Formula to Lookup the highest Value in Col and return Values Cols in same Row
    By hammer2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-04-2015, 04:17 AM
  5. [SOLVED] Can vlookup return highest cell value of in the relation to Lookup Value?
    By wado1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-26-2013, 02:18 PM
  6. Lookup product then return highest value for it help
    By interested in forum Excel General
    Replies: 3
    Last Post: 01-22-2012, 02:02 PM
  7. Lookup with Repeating Values
    By pauldaddyadams in forum Excel General
    Replies: 15
    Last Post: 11-03-2009, 02:42 PM

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