+ Reply to Thread
Results 1 to 16 of 16

Excel Indexing formula to Match two criteria and find the closest match on a third criteri

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    Essex
    MS-Off Ver
    Excel 2016
    Posts
    5

    Excel Indexing formula to Match two criteria and find the closest match on a third criteri

    Hello,

    i am working on trying to automate one of the tasks that i have to complete at my job,
    this consists of matching the charge rate on our system to a list of charge rates in a database,
    the old way of doing this works but is very manual and takes a long time to complete.
    the new way that i have come up with is one formula that indexes a unique code on another worksheet dependant on the variable listed below.

    firstly needs to only return a direct match for Trust Code (which is a 6 digit code for each NHS Trust)
    secondly the formula needs to find the closest charge rate to the rate on the invoice and return the unique code.

    i have got the above formula to work but have realised the formula still needs to include a match for the description of the shift (E.G Day, Night, Saturday or Sunday)
    all of these codes are on the Trust Rates Tab on the attached spreadsheet where i have created a version of the spreadsheet i am working on but without any of the sensitive data that im not allowed to share.

    i have also included different variations of formulas that i have tried along with annotations to the spreadsheet to try and give as much information as possible in the hope someone can help me solve this problem

    Thank you
    Attached Files Attached Files

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,541

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    Too much stuff!!

    I don't want to see formulae that don't work. I do want to see some expected answers, maually calculated. can you amend the sheet?

    Also, when you say closest match, do you mean closest & greater than, closest & less than, or closest, irrepsective of > and < ??
    Glenn



  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    Hi, not entirely sure I follow but perhaps try following;

    1. created a dynamic named range re: rates (so you avoid overly large range processing)

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. with the above in place, you could try below in Column AG on your Data tab:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    above would return following results:

    TrustJob1DPOST
    TrustJob1N
    Trust2Job1D
    Trust2Job1N
    Trust3Job2D
    Trust4Job5Dbreak
    Trust4Job1N
    Trust5Job4DPRE
    Trust5Job4NPRE
    Trust6Job3D

  4. #4
    Registered User
    Join Date
    10-23-2019
    Location
    Essex
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    Hi Guys,

    Thank you for the speedy response,

    i have added the expected results as requested and attached this,

    as for the closest match query, it needs to return the closest disregarding <>,
    the way i did this in the formula i tried is by using and ABS function to subtract the charge rate table from the charge for that row.

    i have also removed all of the irrelevant information now on the V3 version,

    in basic terms i want the formula to achieve the below:

    all matched with the Trust Rates Table
    match Col F (Trust Code) with Col B on the Trust Rates
    Match Col E (Shift Description) With Col C on the Trust Rates
    and then to find the closest less than, greater than or equal too Col C with Col G on the Trust rates
    Attached Files Attached Files
    Last edited by DS3197; 10-29-2019 at 05:40 AM.

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    OK; so your expected results would appear to tally with the results via proposal in post #3.

  6. #6
    Registered User
    Join Date
    10-23-2019
    Location
    Essex
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    i dont understand what a dynamic named range is from that post?

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    So, via Formulas Tab on the Ribbon click on "Define Name", in the resulting dialog enter the Name & RefersTo values as per post #3.

    Once the name is defined, apply the formula as provided to your Data tab, and you should have your results (per expected values)

  8. #8
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,244

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    As you are new here I will add it for you Please read forum rules
    https://www.mrexcel.com/forum/search...rchid=10529401
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  9. #9
    Registered User
    Join Date
    10-23-2019
    Location
    Essex
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    i have defined the _TrustRates and input the formula but the formula is just returning #NAME? when i input it into the spreadsheet, cant see what is wrong.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,268

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    You have not acknowledged the moderation note - did you read and understand it?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  11. #11
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    The attached is based on your original upload - with post#3 suggestion in place.

    As an Essex resident myself I'll over look the cross post ;-)
    Attached Files Attached Files

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,268

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    As an Essex resident myself I'll over look the cross post ;-)
    We are not waiting for a cross-post link, so fortunately you won't be served an infraction.

    Joking apart, I just want to know that the OP has understood so the HE doesn't do this again. We know you know the rules.

    I'm only just over the border myself ...

  13. #13
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    1,888

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    Quote Originally Posted by AliGW
    We are not waiting for a cross-post link
    Correct. Hence the post.

    Quote Originally Posted by AligW
    so fortunately you won't be served an infraction
    Fortunately? This is all a little OTT.

    Feel free to remove this post; this thread is already a little bloated.

  14. #14
    Registered User
    Join Date
    10-23-2019
    Location
    Essex
    MS-Off Ver
    Excel 2016
    Posts
    5

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    Thank you for your help XLent, i have got the formula working on the actual spreadsheet with the data on now!

    my apologises for not cross-posting i didnt realise this was a rule.

    Thank you

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,268

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    For your edification, the rules to which you agreed upon joining: https://www.excelforum.com/forum-rul...rum-rules.html

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,268

    Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

    Fortunately? This is all a little OTT.
    I was teasing you - have you lost your funny bone?

+ 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. FInd closest/nearest match in excel
    By Ribband in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-20-2018, 09:39 AM
  2. [SOLVED] Find address of closest match/second closest match
    By L.LEE in forum Excel General
    Replies: 1
    Last Post: 11-13-2018, 11:56 PM
  3. Replies: 3
    Last Post: 10-19-2018, 05:45 AM
  4. [SOLVED] Formula to find closest match number in a range
    By c.davidson in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-19-2018, 05:39 AM
  5. Replies: 11
    Last Post: 03-27-2014, 07:55 PM
  6. Replies: 6
    Last Post: 01-28-2012, 06:59 PM
  7. 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1