Matching values from different datasets

Options
John Jebamani
John Jebamani ✭✭
edited 12/09/19 in Formulas and Functions

Having difficulty trying to match 2 distinct values from one sheet to another.

We have one dataset that tracks submissions on a daily basis from multiple locations. I been trying to find a way to match 2 distinct values from one sheet to cross reference the master sheet to verify if a form submission was conducted from the location on a specific day.

 

The location submission picture showcases which location has filed a report for what day. 

The location tracking picture showcases that location for the previous 3 days.

I been trying to use vlookup along with index/match function to get this to work but doesn't seem to be working.

From the location tracking, I'm trying to verify if the location has submitted a form for the dates listed above, and if so would like to indicate "Yes" it has been submitted, and "No" that there isn't a record for that specific date.

I been trying to lookup the location, and the date and match it to the master data set but it doesn't seem to be working.

Location Submission.PNG

Location Tracking.PNG

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Try something like this...

     

    =IF(COUNTIFS({Master Sheet Location Column}, $[Airport Master List:]@row, {Master Sheet Date Column}, [Previous Day:]$1) > 0, "Yes", "No")

    .

    If you put that in the cell of [Previous Day:]2 (top left corner of your table data), then you can dragfill over and down to auto-update the formula with the correct cell references. Just be sure to leave the $ symbols where they are as they lock in row or column refrences as needed.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!