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?

Find more posts tagged with

Finance

Formulas

Solution Center

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")

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