Mahing prices vs. rate card with criteria
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
Last edited by tis1337; 10-18-2016 at 04:42 AM.
Attachment
Where are we looking? What are your expected outcomes and where should they appear?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
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.
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?
Try this
Formula:
Please Login or Register to view this content.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
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
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?
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")
Here is with the dates.
Formula:Please Login or Register to view this content.
Glad to have helped - thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks