Return a value based on two criteria.

Carl Vieira

Hello Smartsheet Community!

First time posting a questions so here goes....

I need some assistance and am stuck with a formula. I have a Master File that looks at a lookup file for data.

I have Customer ID that have a Tier I rate and a Tier II rate based on a location. I already have in my file what Tier is expected but am having trouble bringing in the Max rate. I need to bring in the Tier Max Rate for that customer Tier so I can leverage a column that determines whether the requested rate is in under the Tier Max or not to see approval to proceed.

Each Picture is a separate sheet so the first picture would look at the second picture.

Any assistance would be greatly appreciated!

Carl Vieira

Mark Safran

Hi Carl,

Instead of a VLOOKUP, I think the COLLECT() function will be your best bet here. In your Tier Max column on the Master File (your first image), give this formula a shot:

=MAX(COLLECT({Lookup File - Tier Max Range}, {Lookup File - Customer ID Range}, =[Customer ID]@row, {Lookup File - Tier Range}, =Tier@row))

The ranges defined within braces { } are the respective columns on the Lookup File (your second image). I gave them custom Sheet_Reference_Names, so just make sure you match that up if you're copy/pasting.

The COLLECT() function unfortunately has to be nested inside another formula, which is why you'll see the leading MAX() function call. As long there are no duplicate rows with the same Customer ID and Tier # but a different Tier Max Value, this won't really matter. If for some reason there are duplicate rows (with the same Customer ID and Tier #), this will just end up taking the higher of the two Tier Max's listed.

-MS

Mark Safran

Hi Carl,

Instead of a VLOOKUP, I think the COLLECT() function will be your best bet here. In your Tier Max column on the Master File (your first image), give this formula a shot:

=MAX(COLLECT({Lookup File - Tier Max Range}, {Lookup File - Customer ID Range}, =[Customer ID]@row, {Lookup File - Tier Range}, =Tier@row))

The ranges defined within braces { } are the respective columns on the Lookup File (your second image). I gave them custom Sheet_Reference_Names, so just make sure you match that up if you're copy/pasting.

The COLLECT() function unfortunately has to be nested inside another formula, which is why you'll see the leading MAX() function call. As long there are no duplicate rows with the same Customer ID and Tier # but a different Tier Max Value, this won't really matter. If for some reason there are duplicate rows (with the same Customer ID and Tier #), this will just end up taking the higher of the two Tier Max's listed.

-MS

Carl Vieira

Mark,

That worked, I was discussing with my team and we knew it would have to probably have the Collect function but were stumped.

Thank you very much for your insight.

Best,

Carl Vieira

Becky Wilson

I am trying to get the following using Max(collect and can't seem to make it work:

I want it to return the highest value for each Auction Item:

In this instance, the formula would return $100 for Concealed Carry