Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, pleaseVisit the Current Forums.
Formula: Look Up for two value
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?
Comments
-
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")
-
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.
我尝试使用新的公式,收集也不工作
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"))
-
Hi,
It shows invalid column value.
= AVG(收集([日期接收]1:接收日期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
-
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]
-
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
Categories
Thank you so much for your solution!<\/p>
It can work, but more like a compromise way. Editing on a report is less convenient.<\/p>
It would be better for me to set access right to each column.<\/p>
But that can also work for now, thanks a lot.<\/p>
By the way are you a BOT?<\/p>"},{"commentID":383283,"body":"
I agree. Great idea! That would be a great addition to Smartsheet features.<\/p>
