+ Reply to Thread
Results 1 to 4 of 4

Multiple criteria lookup - First match :exact" and second match "Closest"

  1. #1
    Registered User
    Join Date
    09-23-2011
    Location
    Goshen, In
    MS-Off Ver
    Excel 2010
    Posts
    19

    Multiple criteria lookup - First match :exact" and second match "Closest"

    Pricing.xlsx
    I need to return the "MULT" in cell H5 based on what is in cells J2 and L2 using the Table in range a5:d39. I will then need to return the "Per Box" the same way.

    Problem :
    1. J2 match in column A has to be exact
    2. L2 match has to be less than or equal to the height in column B.

    {=INDEX($C$5:$C$39,MATCH(J2,A5:A39,0),MATCH(L2,B5:B39,1))} The current formula is not yielding the correct results.

    Thank you

  2. #2
    Valued Forum Contributor
    Join Date
    10-10-2012
    Location
    New York
    MS-Off Ver
    Excel 2007/2010
    Posts
    337

    Re: Multiple criteria lookup - First match :exact" and second match "Closest"

    Try using this formula instead
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Don't just use the answers provided for you. Try to understand how it works by reverse engineering or asking about it.

    Please mark the thread as [SOLVED] (Thread Tools->Mark thread as Solved) when answered.
    If you're happy with an answer given, please click the * under the person's name to boost their reputation.

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,547

    Re: Multiple criteria lookup - First match :exact" and second match "Closest"

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    09-23-2011
    Location
    Goshen, In
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Multiple criteria lookup - First match :exact" and second match "Closest"

    Thank you Leah. Your solution worked. Much appreciated.

+ 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