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

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:

Name: _TrustRates

RefersTo: ='Trust Rates'!$A$1:INDEX('Trust Rates'!$A:$IV,MATCH(REPT("Z",255),'Trust Rates'!$A:$A),MATCH(REPT("Z",255),'Trust Rates'!$1:$1))

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

Formula:

AG3: 

=INDEX(_TrustRates,ROUND(10^4*MOD(AGGREGATE(15,6,((ABS(ROUND(INDEX(_TrustRates,0,7)-$L3,2))*10^7)+(ROW(INDEX(_TrustRates,0,1))/10^4))/(INDEX(_TrustRates,0,2)=$Y3)/(INDEX(_TrustRates,0,3)=W3),1),1),0),1)

copied down

above would return following results:

TrustJob1DPOST
TrustJob1N
Trust2Job1D
Trust2Job1N
Trust3Job2D
Trust4Job5Dbreak
Trust4Job1N
Trust5Job4DPRE
Trust5Job4NPRE
Trust6Job3D

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

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

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)

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.

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 ;-)

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.

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