+ Reply to Thread
Results 1 to 5 of 5

Vlookup with multiple conditions (One condition need to be set to nearest match)

  1. #1
    Registered User
    Join Date
    01-05-2008
    Posts
    51

    Vlookup with multiple conditions (One condition need to be set to nearest match)

    Can I do a lookup that will first search for Item, then date (nearest date) and return qty?

    Item Date Qty
    xyz 3-Jan 452
    xyz 7-Jan 225
    zzy 1-Jan 120
    abc 6-Jan 352
    abc 10-Jan 762

    So if Item = xyz, and date = 4-Jan, then 452 should be displayed Qty.
    if Item = xyz and date = 2-Mar, then 225 should be displayed are Qty.

    Thanks in advance

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup with multiple conditions (One condition need to be set to nearest match)

    Try:

    =INDEX(C2:C6,MATCH(1,(A2:A6=E1)*(ABS(B2:B6-F1)=(MIN(IF(A2:A6=E1,ABS(B2:B6-F1))))),0))

    confirmed with CTRL+SHIFT+ENTER
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Vlookup with multiple conditions (One condition need to be set to nearest match)

    With your posted data in A1:C6

    and
    D1: an item...eg abc
    E1: a date....eg Jan 07, 2011

    This regular formula returns the QTY associated with the largest date that is less than or equal to the E1 date for that item.
    Please Login or Register  to view this content.


    In the above example, the formula returns 352

    Assumption:
    Items are grouped together and dates are in ascending order within Item groups.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Registered User
    Join Date
    01-05-2008
    Posts
    51

    Re: Vlookup with multiple conditions (One condition need to be set to nearest match)

    Dear Ron,

    Thanks for the help.

    Is it possible for you to explain the formula. That would help me learn the trick

    Thank you,

    Ravi.

  5. #5
    Registered User
    Join Date
    06-30-2014
    Location
    NC, USA
    MS-Off Ver
    2010
    Posts
    1

    Re: Vlookup with multiple conditions (One condition need to be set to nearest match)

    Ron, Thank You sir you formula worked great for me too.

    Thank You

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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