+ Reply to Thread
Results 1 to 3 of 3

Lookup value and return max absolute in a range

  1. #1
    Registered User
    Join Date
    04-11-2010
    Location
    Singapore
    MS-Off Ver
    Office 365
    Posts
    44

    Lookup value and return max absolute in a range

    Hi, I have 2 columns of numbers A and B, how do I return the max absolute value in column B when I lookup a value from column A?
    For example, I want to lookup 2 to return the max absolute value is which 100 and 1 to return -10 and 5 to return 40 etc.

    Thanks for your help!

    A B

    1 -10
    1 -5
    1 0
    1 4
    1 6
    2 10
    2 100
    2 -50
    2 -5
    2 10
    5 -2
    5 40
    5 -9
    5 -10
    5 -3

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Lookup value and return max absolute in a range

    D1 is your number

    =LET(List,IF(A1:A15=D1,B1:B15,0),IF(ABS(MIN(List))>MAX(List),MIN(List),MAX(List)))
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    02-09-2020
    Location
    United States
    MS-Off Ver
    Microsoft Office Home and Business 2021
    Posts
    19

    Re: Lookup value and return max absolute in a range

    Hello! Please see text below:

    A B Max Number Result
    1 -10 6
    1 -5 6
    1 0 6
    1 4 6
    1 6 6
    2 10 100
    2 100 100
    2 -50 100
    2 -5 100
    2 10 100
    5 -2 40
    5 40 40
    5 -9 40
    5 -10 40
    5 -3 40

    The first value of "1" is in cell A2, first value =10 is in cell B2, and the first formula is in cell C2 (drag it down). The formula in cell C2 is: {=MAX(($A$2:$A$16=$A2)*List)} - it's an array formula, so you need to press CTRL+SHIFT+ENTER. The reason the max result for value "1" is 6 is because 6 is a positive number and -10 is a negative number. 6 is larger than -10. List is cells B2:B16.

    I hope this is what you were looking for.

+ 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] Value to return by lookup range in between
    By siddiq1212 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-04-2019, 04:46 AM
  2. [SOLVED] Nest lookup Function to lookup name between date range and return value
    By bbeards in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2018, 01:37 PM
  3. [SOLVED] Lookup a value within a range and return value
    By heidithecat in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-14-2016, 09:43 AM
  4. [SOLVED] Lookup the first non zero value of a row in a range and return it.
    By luccallens in forum Excel General
    Replies: 5
    Last Post: 05-12-2015, 11:43 PM
  5. Replies: 4
    Last Post: 05-22-2013, 04:15 AM
  6. Lookup trying to return a value out of range
    By chcwebb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2009, 06:46 PM
  7. [SOLVED] lookup and return range of cells
    By ark in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM

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