Mahing prices vs. rate card with criteria

[tis1337]

Hi All,

I need to check invoiced prices vs. contractual agreements. This means that i need to check if price A matches the rate-card in sheet B (lookup based on supplier id as preference). Additionally, rates change over time and hence i need to be able to make the look-up time specific. i have attached my problem (in the below respose).

I am completely stuck - Any help would be greatly appriciated.

The one with the solution will be my hero for the month :-)

Kind Regards

[tis1337]

Attachment

[AliGW]

Where are we looking? What are your expected outcomes and where should they appear?

[tis1337]

Hi AliGw,

In the attachement tab "Delivery Team" - I am looking for a Match vs. No match indication in column M with regards to the hourly rate in column F vs. the hourly rates found in "Rate Overview" column H. So if the hourly rate in "Delivery team" does not match the contractual hourly rates in "Rate overview", then i am charged a incorrect value.

[AliGW]

OK. So:

1. I will need to check if the value in F5 is identical with any of the frameagreement rates for Google in the sheet "Rate Overview"
What do you want to show in that column if there is a match?

2. Rates change over time, and hence I would need to the check rates towards a specific timeperiod.
How would you do that using the tables in your attachment?

[AlKey]

Try this

Please Login or Register  to view this content.

[tis1337]

Thank you very much for looking into it!!

1) Yes - Just an indication of a Match or not. e.g. "Match"; "No Match"
2) For the time period i would check against the start date in column G vs. start-dat or end date in the rate overview sheet.

Hope that helps the slightest

[tis1337]

ALkey - That works like a charm for the Match vs. No Match! Thanks!! Any hints on how i could get the dates to work as well?

[AliGW]

Try this:

=IFERROR(IF(AND(MATCH(F5,'Rate Overview'!$H$5:$H$9,0)>0,INDEX('Rate Overview'!$D$5:$D$9,MATCH(F5,'Rate Overview'!$H$5:$H$9,0),0)<=G5,INDEX('Rate Overview'!$E$5:$E$9,MATCH(F5,'Rate Overview'!$H$5:$H$9,0),0)>=G5),"Match",""),"No Match")

[AlKey]

Here is with the dates.

Please Login or Register  to view this content.

[AliGW]

Glad to have helped - thanks for the rep!