I get a "Invalid operation" when trying to get info using a "IF" and "AND" expressions

joaovanogueira

Hi there! I would appreciate your help with this case, I assume it is easy but cant get it through.

I have three different dimensions that will determine the maximum allowed fee to pay consultant services (fair market value):

1. Country of the service; e.g. USA, Canada, Mexico
2. Tier (I, II, III) of expertise (Junior to Senior)
3. Field of expertise; e.g. IT; Legal; HR; Business

So I need to retrieve the max. hourly fee I can pay a certain professional based on these elements.

I have tried using "=IF(AND(VLOOKUP)," and it works with few parameters, not all list of countries tierings.

Can anybody shed some light on this?

Thanks a lot!

MarceHolzhauzen

Hi @joaovanogueira

Welcome tot he community!

What does your sheet look like? Could you share a screenshot of where the rates would come from?

There are other formulas (like Max and collect) that might get you to the place you need to go, but i'd only be able to help once we see your data and how you expect the formula to get the info

Paul Newcome

Try a MAX/COLLECT along the lines of

=MAX(COLLECT({Fee}, {Country}, @cell = Country@row, {Tier}, @cell = Tier@row, {Field}, @cell = Field@row))