Multiple Column Confirmation Criteria to return date from "Order Date" Column

SidOrgane

I need a formula for a date formatted column that must return the "Order Date" from the ODate column if the Site Name (Site) is Oak Village, Block Name (Block) is Block 102 and the Floor Level (FlrLvlUnt) is Basement Level. I managed this multiple criteria lookup with SUMIF for number values, but have been unable to do so with DATE values.

Robert S.

Hello,

 

A multiple criteria LOOKUP function like this is possible, however it takes a bit of a workaround to get it to work. The LOOKUP function can only look for a single criteria, so you will have to turn the three columns of information into one. This can be done a few ways, but the simplest would be using the plus symbol to concatenate information from multiple cells into one.

 

To do this, first create a new column to the left of the "Site" column, we'll call that "Combine" for now. This can be hidden later once this is set up. In the first row for your lookup table in that column, place the following formula.

 

=Site# + Block# + FlrLvlUnt#

 

Replace all of the # symbols with the row number for this row. This will combine the text from these three cells into one block of text like this "OAK VILLAGEBLOCK 102Basement Level". Now you can use drag-fill to fill the rest of that column for your lookup table. More on drag-fill here (https://help.smartsheet.com/articles/518318#dragfill).

 

Now you have a column with unique identifiers for each of your lookup table rows. You can now enter the following formula into the cell you wish the date to be auto-populated into.

 

=LOOKUP("OAK VILLAGEBLOCK 102Basement Level", Combine:ODate, 5, false)

 

This will search this newly created column for the unique identifier "OAK VILLAGEBLOCK 102Basement Level", and return the information in the 5th column for that row which in this case is your date column.