Formula: Look Up for two value

Peter Smith

Hi, 

This one is pretty easy in Excel but I have no idea how to do that in SmartSheet. 

I have 3 columns in SS: Fruit, Brand and Price

[Fruit]1  is Apple , [Brand]1 is ABC Ltd. [Price]1 is 100

[Fruit]2 is Apple , [Brand]1 is XYZ Ltd. [Price]1 is 50

[Fruit]3 is Pear, [Brand]1 is ABC Ltd. [Price]1 is 100

 

I want to have formula to find price of XYZ Ltd Apple, the answer should be 50. 

How can I do it in SS?

ricki

If you only will have one row that matches you could try to use sumifs

 

=SUMIFS([Price]:[Price], Fruit:Fruit, "Apple", Brand:Brand, "XYZ")

Peter Smith

Sorry we need to add one more column, date arrive .  Now, if we need to look for date instead of price for two critera, which formula we can use

E.g., look for date arrive  for apple and ABC Ltd. 

 

I try to use new formula, collect also not work 

 

Thanks 

 

ricki

Looks like you can do something with the new collect function

https://help.smartsheet.com/function/collect

=AVG(COLLECT([Date Arrive]:[Date Arrive], [Fruit]:[Fruit], "Apple", [Brand]:[Brand], "XYZ"))

Peter Smith

Hi, 

It shows invalid column value. 

=AVG(COLLECT([Date to receive]1:[Date to receive]2, Fruit1:Fruit2, "Apple", Company1:Company2, "XYZ 123 Ltd"))

May be because I set the date to receive column as date.  If I put number instead of date, its work. But this one should be set up as date

 

I do not need to have any calculation. I just need to show the date to receive. 

If the formula is right, it should shows Sep 18, 2017. I just want to get answer if I look up for "Apple" and "XYZ 123 Td)

Fruit    Company          Date to receive

Apple   ABC123 Ltd      06/06/17

Apple   XYZ 123 Ltd       09/18/17

Thanks 

 

Peter Smith

sorry , fat finger. 

I just want to get answer if I look up for "Apple" and "XYZ 123 Ltd"

Thanks 

 

ricki

I got a similar error when there was no match in the range of data I was looking at. I think the issue here is that you want to be searching the whole column so you need to remove the numbers

ie you have ([Date to receive]1:[Date to receive]2 but it should really be ([Date to receive]:[Date to receive]

You need the avg function because the collect just returns the collection

I used the following in my test smartsheet and it returned the expected date (column2 and column3 were my actual column names)

=AVG(COLLECT([Due Date]:[Due Date], [Column2]:[Column2], "Sprint 1", [Column3]:[Column3], "2"))

The field I put this formula into was a date field (called TestDate). What column did you put your formula into?

I was also sometimes getting an error because I didn't have brackets around the field names (even though they were only one word). So you may want to try [Fruit]1:[Fruit]2 or [Fruit]:[Fruit]

 

Peter Smith

Ricki: 

The formula is work now. Tricky thing is I forget to set column property as Date (I type formula in column and I find out the column property default as text/number)

 

Thanks