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

Tags:

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!