Formula: Look Up for two value

Peter Smith · 09/14/17

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 · 09/14/17

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 · 09/18/17

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 · 09/18/17

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 · 09/18/17

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

Peter Smith · 09/18/17

sorry , fat finger.

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

Thanks

ricki · 09/18/17

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?

我有时也会得到一个错误,因为我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 · 09/19/17

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

Welcome to the Smartsheet Forum Archives

Formula: Look Up for two value

Comments

Categories