+ Reply to Thread
Results 1 to 4 of 4

Match by multiple criteria and closest value

  1. #1
    Registered User
    Join Date
    10-07-2019
    Location
    ISRAEL
    MS-Off Ver
    2016
    Posts
    2

    Match by multiple criteria and closest value

    Hi

    I'm trying to fill the PRICE to Col G, from the table in Col A-C,
    by PART# and closest QUANTITY.
    Example:
    Row3 - X1 700 PRICE in Col G Should be 2.6 --> ABS(500-700) = 200 | ABS(1000-700) = 300

    1111.JPG

    I tried to use the INDEX and MATCH functions
    Please Login or Register  to view this content.
    Tried to take into account the PART# in several ways but couldn't get the right results.

    I'll be glad to receive your help.
    Last edited by Benta1; 10-16-2019 at 02:38 AM. Reason: Attached a sample workbook

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Match by multiple criteria and closest value

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Match by multiple criteria and closest value

    Please try array entering this formula in cell G2 and fill down.

    If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  4. #4
    Registered User
    Join Date
    10-07-2019
    Location
    ISRAEL
    MS-Off Ver
    2016
    Posts
    2

    Re: Match by multiple criteria and closest value

    Thanks, that seems to be the solution.
    I was very close.

+ 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. Replies: 11
    Last Post: 06-05-2023, 01:36 AM
  2. Closest match based on multiple criteria
    By jeffs24 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-30-2017, 04:11 PM
  3. [SOLVED] Finding the closest match that meet multiple criteria
    By n_kerai in forum Excel General
    Replies: 9
    Last Post: 01-26-2017, 12:15 PM
  4. Replies: 2
    Last Post: 12-16-2016, 07:58 AM
  5. [SOLVED] Closest match on Multiple Criteria
    By rbetts in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-20-2014, 08:33 AM
  6. [SOLVED] Multiple criteria lookup - First match :exact" and second match "Closest"
    By mweichorn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 12:14 PM
  7. closest match from multiple criteria
    By wongja in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2010, 01:37 AM

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