Lookup highest amount with conditions
Hello,
I think I am close but I am not sure how to add one more value to look up one more piece of the puzzle (country data) from the current formula I am using. I am not sure how to reformat the formula so that the formula also looks at the country.
I am currently using MAX and COLLECT to check three columns (Role, Hourly Rate (USD) and Local Currency) in my main sheet then I am cross-referencing another sheet for the max hourly, currency and 'SS Profile Name'.
Formula used: =MAX(COLLECT({Partner Rate Cards_Hourly}, {Partner Rate Cards - Role}, =Role@row, {Partner Rate Cards_Currency}, =[Local Currency]@row))
I noticed that it is pulling in other countries. I need the formula to look at the Role, Hourly Rate (USD) and Local Currency and country in the main sheet where the data is to pulled into. Then from the sheet that I am cross-referencing it needs to check the max hourly, currency and 'SS Profile Name' and country to make sure it is pulling in the highest (hourly) amount for the currency, country and role "SS Profile Name".
Correct highest hourly rate for CAD for a Senior Project Manager in Canada
Correct highest hourly rate for USD for a Senior Project Manager in the United States
Currently an incorrect highest hourly rate for USD for a Senior Project Manager in the United States is pulling data into the main sheet. Technically it should pull in $93.00.
Instead it is pulling in the highest hourly rate for a different Country.
Thank you!
Adriane
Best Answer
-
You would include another range/criteria set inside of the COLLECT function.
Answers
-
You would include another range/criteria set inside of the COLLECT function.
-
@Paul Newcome - thank you, in my attempts to add it in apparently I was putting it in the wrong location. Ugh...total oversight I appreciate the outside perspective it worked. I tried adding it at the beginning of the formula and it threw a #INVALID OPERATION error. Now with the range/criteria at the end of the formula it is pulling in the correct data.
Formula corrected:
=MAX(COLLECT({Partner Rate Cards_Hourly}, {Partner Rate Cards - Role}, =Role@row, {Partner Rate Cards_Currency}, =[Local Currency]@row, {Partner Rate Cards_PRR -Country}, =Country@row))
Now my sheet is displaying the max rate for that role, local currency and country.
Adriane
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!