#### 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, please Visit the Current Forums.

# Formula: Look Up for two value

edited 12/09/19

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?

• ✭✭✭✭✭✭

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.

I try to use new formula, collect also not work

Thanks

• ✭✭✭✭✭✭

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.

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)

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

• ✭✭✭✭✭✭

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

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

This discussion has been closed.