+ Reply to Thread
Results 1 to 2 of 2

Return Highest & Lowest Value from Vlookup

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    Sunshine Coast
    MS-Off Ver
    Excel for Mac 2011
    Posts
    83

    Exclamation Return Highest & Lowest Value from Vlookup

    So I am needing to return the highest or lowest value from a vlookup when the lookup_value is outside of the table

    Here is my basic vlookup forumla: =VLOOKUP(E11,$J$10:$K$40,2,FALSE)

    Here is my J10 to K40
    40 200
    39 194
    38 188
    37 182
    36 176
    35 170
    34 164
    33 158
    32 152
    31 146
    30 140
    29 134
    28 128
    27 122
    26 116
    25 110
    24 104
    23 98
    22 92
    21 86
    20 80
    19 74
    18 68
    17 62
    16 56
    15 50
    14 44
    13 38
    12 32
    11 26
    10 15
    9 0

    So what I am trying to do is if a lookup_value is 9 or less / 40 or more is to return the lowest (being 0) or highest (being 200)

    Just not sure if it is possible and if it is, how to do it!

    Thanks in advance!!

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Return Highest & Lowest Value from Vlookup

    maybe something like this:
    =IF({source}>=MAX($J$10:$J$40),MAX($K$10:$K$40),IF({source}<=MIN($J$10:$J$40),MIN($K$10:$K$40),VLOOKUP({source},$J$10:$K$40,2,FALSE)))
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

+ 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