Lookup highest amount with conditions

Adriane Price

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!

Paul Newcome

You would include another range/criteria set inside of the COLLECT function.

Paul Newcome

You would include another range/criteria set inside of the COLLECT function.

Adriane Price

@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.

Paul Newcome

Happy to help. 👍️