Help with cross sheet lookup

Nick0000

Hello,

In my source sheet I have a multiselect column called "Problem Code to be Updated". I am trying to use the reference sheet in the screenshot to identify if the multiselect cell in the source sheet has any of the problem codes where the "Technician Assigned Problem Code" is "Yes". The dropdown values are all taken from the "Problem Description" column from the reference sheet.

I have tried the below formula, but it only returns a 1 when there is a single option selected in the multiselect column. It returns a 0 if there is a problem code where "Technician Assigned Problem Code" is "Yes", but there are multiple values selected.

=IF(COUNTIFS({Problem Codes | Tech Code}, "Yes", {Problem Codes | Description}, HAS(@cell , [Problem Code to be Updated]@row )) > 0, 1, 0)

jmyzk_cloudsmart_jp

Hi @Nick0000

In your HAS criteria in the COUNTIFS formula, you put the criterion first, and then the search_range.

HAS(@cell  , [Problem Code to be Updated]@row  )) > 0

But the correct syntax is HAS(search_range, criterion).

If you use the HAS function with a multi-select dropdown, it checks whether the criterion value is included in the selected options.

In your case, the [Problem Code to be Updated] column is a multi-select dropdown column, so the column's value should be the search_range; @cell is each cell in the {Problem Codes | Description}, a Text_Number column range. So, @cell should be the criterion.

HAS([Problem Code to be Updated]@row, @cell)

https://help.smartsheet.com/function/has

Syntax

HAS(search_range, criterion)

search_range— The cell or cell range to search within

criterion— The value that you want to find

https://app.smartsheet.com/b/publish?EQBCT=9dbf42fc56c94d71a77381cfd08ab7d9

[Flag] formula

=COUNTIFS(
  {Problem Codes | Tech Code}, "Yes", 
  {Problem Codes | Description}, HAS([Problem Code to be Updated]@row, @cell)
) > 0

If you apply the formula to a checkbox column, which expects a boolean, true or false value, you can remove the IF(criterion, 1,0).

https://app.smartsheet.com/b/publish?EQBCT=149831290fd24df5973ee5e25d1907f2