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

1. ## 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

2. ## 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 < ??

3. ## 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:
`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:
`Please Login or Register  to view this content.`

above would return following results:

TrustJob1DPOST
TrustJob1N
Trust2Job1D
Trust2Job1N
Trust3Job2D
Trust4Job5Dbreak
Trust4Job1N
Trust5Job4DPRE
Trust5Job4NPRE
Trust6Job3D

4. ## 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

5. ## 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. ## 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. ## 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. ## Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

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.

https://www.mrexcel.com/forum/search...rchid=10529401

9. ## 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. ## 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?

11. ## 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 ;-)

12. ## 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. ## Re: Excel Indexing formula to Match two criteria and find the closest match on a third cri

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

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. ## 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. ## 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

16. ## 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?

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

#### 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