+ Reply to Thread
Results 1 to 3 of 3

Index(Match with 2 criteria; 1 exact and 1 approx

  1. 06-09-2016, 01:21 PM #1
    JuJuBe
    JuJuBe is offline
    Registered User
    Join Date
    03-05-2010
    Location
    Oregon
    MS-Off Ver
    Excel 2007
    Posts
    75

    Index(Match with 2 criteria; 1 exact and 1 approx

    I need to return a value based on 2 criteria. One of these criteria must match exactly and the other can be equal to or lesser than.

    See the attached example. columns A-C is my array sheet and columns E-G represent the sheet where I will return the results. The formula will be in column G.

    I need to make an exact match on the Item but find the Date that is equal to or lesser than (closest match).

    Many TIA!

    P.S. I searched old threads and found several similar threads but each had it's own special nuance that made it not applicable to my situation.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-09-2016, 03:09 PM #2
    ben_hensel
    ben_hensel is offline
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Index(Match with 2 criteria; 1 exact and 1 approx

    I had to stick a MIN(IF) array inside the concatenated double-exact-lookup to get it to work since your data is unsorted BUT I think this will do the trick:

    Please Login or Register  to view this content.
    Note the >= in red; that might have to point the other way as <= depending on which quantity you want to be "greater than or equal to", I think the example didn't quite work correctly so I was not sure.

    Also it's an array formula so confirm with CTRL+SHIFT+ENTER instead of just ENTER.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
    Reply With QuoteRegister To Reply
  3. 06-09-2016, 04:27 PM #3
    JuJuBe
    JuJuBe is offline
    Registered User
    Join Date
    03-05-2010
    Location
    Oregon
    MS-Off Ver
    Excel 2007
    Posts
    75

    Re: Index(Match with 2 criteria; 1 exact and 1 approx

    Nice work, thanks Ben!!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index Match with Multiple Criteria (exact for text and greater than value for number)
    By mxlew in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-26-2021, 05:36 AM
  2. Look up or match/index multiple text criteria approx
    By qclady in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-07-2016, 08:50 PM
  3. Using wildcards to get an exact match using vlookup or index and match
    By Martynw2005 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-15-2015, 09:56 PM
  4. Lookup or Index(match) function not in ascending order and not an exact match
    By Tobias1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  5. [SOLVED] Index & Match returning incorrect value. Arrays fixed and exact match used.
    By SDes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-07-2012, 08:29 PM
  6. Double Criteria Search - Exact Match on First but Closest Match on Second
    By SPAMACCOUNT in forum Excel General
    Replies: 6
    Last Post: 01-28-2012, 06:59 PM
  7. Double Criteria Search - Exact Match on First but Closest Match on Second
    By SPAMACCOUNT in forum Excel General
    Replies: 2
    Last Post: 01-28-2012, 05:26 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1