+ Reply to Thread
Results 1 to 5 of 5

Need a Lookup that can return a value smaller than lookup--but array is descending

  1. #1
    Registered User
    Join Date
    09-21-2016
    Location
    San Francisco
    MS-Off Ver
    2010
    Posts
    13

    Need a Lookup that can return a value smaller than lookup--but array is descending

    Hi,

    I have an HLookup that is supposed to return a grade based on the lookup value. Think A = 90 (and above), B = 80 (and above), etc.

    I know that if I have these values ascending, I will be able to use a vlookup or hlookup to return the correct grade depending on how the table is oriented, but for very specific reasons that I won't delve into, the values need to be descending.

    Is there any way to return the correct grade using a different function or is there some sort of workaround?

    Thank you !!!!

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Need a Lookup that can return a value smaller than lookup--but array is descending

    Try

    =INDEX($B$1:$B$5,MATCH(TRUE,INDEX($A$1:$A$5<=$D1,0),0))

    A1:A5 = 90 80 70 60 0
    B1:B5 = a b c d f

    D1 = value to check.

  3. #3
    Forum Expert
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    1,219

    Re: Need a Lookup that can return a value smaller than lookup--but array is descending

    Instead of HLOOKUP, try INDEX + MATCH.

  4. #4
    Registered User
    Join Date
    09-21-2016
    Location
    San Francisco
    MS-Off Ver
    2010
    Posts
    13

    Re: Need a Lookup that can return a value smaller than lookup--but array is descending

    That worked like a charm!!!! Hopefully one day I will understand it

    Thank you Jonmo!

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Need a Lookup that can return a value smaller than lookup--but array is descending

    You're 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. [SOLVED] Lookup a value in an array and return the whole row. Not just one cell.
    By luccallens in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-14-2016, 05:21 AM
  2. Replies: 4
    Last Post: 05-19-2015, 08:42 PM
  3. [SOLVED] Lookup to return multiple results - without array
    By ZeDoctor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2012, 07:42 AM
  4. [SOLVED] Lookup a value in one array, and return a corresponding column/row in another array
    By Gunther Maplethorpe in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-15-2012, 08:47 PM
  5. Array Lookup, Return/Sum Multiple Values
    By mre2000 in forum Excel General
    Replies: 3
    Last Post: 01-18-2010, 10:45 PM
  6. Using Vlookup to lookup array, return array
    By jcampbell in forum Excel General
    Replies: 5
    Last Post: 11-16-2007, 01:13 PM
  7. Lookup to return an array
    By evanbb in forum Excel General
    Replies: 3
    Last Post: 10-30-2006, 10:43 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