Matching criteria between smartsheets

Paula Cosentino

Not sure this is possible, but here goes! I have multiple smartsheets (let's say 3). Two of the smartsheets collect information from forms that we use to send on to credentialing or contracting so we can load them in our systems. A third smartsheet is a list of line items each with a gap in our network for a provider with a particular specialty and in a specific county. We would love to have some sort of process where the smartsheets could search for open gaps on the 3rd smartsheet that match a line and then check a box or something.

match up open gap "Reporting Category" plus "County" with Specialty in the other two smartsheets and return match or check box if there is a match. (I know we'd have to sync up our column headings)

pif specialty fields.PNG

nag open gaps.PNG

Paula Cosentino

That's amazing Austin. I'll try that. Thank you for taking the time!

Austin Smith

Not 100% sure this is what you're looking for, but try this in your checkbox field (you may need to rename columns, but more likely will have to reorder them to make vlookup work well, or just switch around the references that I entered so that vlookup works correctly. Only did a very rudimentary test against data.

=IFERROR(IF(VLOOKUP(County@row, [County 2]@row:[Reporting 2]@row, 2, false) = Specialty@row, IF(VLOOKUP(County@row, [County 2]@row:[Reporting 2]@row, 3, false) = Reporting@row, IF(gap@row = 0, 1, 0), false), false), false)

This matches the county, then makes sure the specialty matches, then finds the county and makes sure the reporting cat matches, if both are true, it determines whether there is a gap and if so, checks a box.

Paula Cosentino

That's amazing Austin. I'll try that. Thank you for taking the time!