Lookup across multiple columns

[Jake_Naude]

Evening,

This one is a little over my head…

I need to lookup a value in one column and if it exists in any one of 5 other columns, it needs to return the column to the right of where it is found.

I could use LOOKUP for one instance but don’t know how to do it for multiple columns?

LOOKUP( survey_branch_number , survey , branch_A_number , branch_A_assets )

I need to lookup:
[survey_branch_number]

In these columns:
[branch_A_number], [branch_B_number], [branch_C_number], [branch_D_number], [branch_E_number]

And return the corresponding:
[branch_A_assets], [branch_B_assets], [branch_C_assets], [branch_D_assets], [branch_E_assets]

And, as if this wasn’t tricky enough, the expression will also need to account for the following:

SELECT(
survey[branch_x_assets],
AND(
[select_asset_type] = [_THISROW].[select_asset_type],
[select_cabinet_to_survey] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
))

Sample of survey table below:

Thanks in advance…

[LeventK]

IFS(
	ISNOTBLANK(LOOKUP(survey_branch_number , survey , branch_A_number , branch_A_assets)),
	LOOKUP(survey_branch_number , survey , branch_A_number , branch_A_assets),
	TRUE,
	IFS(
		ISNOTBLANK(LOOKUP(survey_branch_number , survey , branch_B_number , branch_B_assets)),
		LOOKUP(survey_branch_number , survey , branch_B_number , branch_B_assets),
		TRUE,
		IFS(
			ISNOTBLANK(LOOKUP(survey_branch_number , survey , branch_C_number , branch_C_assets)),
			LOOKUP(survey_branch_number , survey , branch_C_number , branch_C_assets),
			TRUE,
			IFS(
				ISNOTBLANK(LOOKUP(survey_branch_number , survey , branch_D_number , branch_D_assets)),
				LOOKUP(survey_branch_number , survey , branch_D_number , branch_D_assets),
				TRUE,LOOKUP(survey_branch_number , survey , branch_E_number , branch_E_assets)
			)
		)
	)
)

[Jake_Naude]

Hi @LeventK,

Thank you very much for that…! You make that look easy…!

I’ll give that a go right away and will report back…

Cheers…

[Steve]

This:

reformatted for my clarity

IFS(
  ISNOTBLANK(
    LOOKUP(
      survey_branch_number,
      survey,
      branch_A_number,
      branch_A_assets
    )
  ),
    LOOKUP(
    survey_branch_number,
    survey,
    branch_A_number,
    branch_A_assets
    ),
	TRUE,
    IFS(
      ISNOTBLANK(
        LOOKUP(
          survey_branch_number,
          survey,
          branch_B_number,
          branch_B_assets
        )
      ),
        LOOKUP(
          survey_branch_number,
          survey,
          branch_B_number,
          branch_B_assets
        ),
      TRUE,
        IFS(
          ISNOTBLANK(
            LOOKUP(
              survey_branch_number,
              survey,
              branch_C_number,
              branch_C_assets
            )
          ),
            LOOKUP(
              survey_branch_number,
              survey,
              branch_C_number,
              branch_C_assets
            ),
          TRUE,
            IFS(
              ISNOTBLANK(
                LOOKUP(
                  survey_branch_number,
                  survey,
                  branch_D_number,
                  branch_D_assets
                )
              ),
                LOOKUP(
                  survey_branch_number,
                  survey,
                  branch_D_number,
                  branch_D_assets
                ),
              TRUE,
                LOOKUP(
                  survey_branch_number,
                  survey,
                  branch_E_number,
                  branch_E_assets
                )
            )
        )
    )
)

simplifies to:

easier to read

IFS(
	ISNOTBLANK(
    LOOKUP(
      survey_branch_number,
      survey,
      branch_A_number,
      branch_A_assets
    )
  ),
    LOOKUP(
      survey_branch_number,
      survey,
      branch_A_number,
      branch_A_assets
    ),
  ISNOTBLANK(
    LOOKUP(
      survey_branch_number,
      survey,
      branch_B_number,
      branch_B_assets
    )
  ),
    LOOKUP(
      survey_branch_number,
      survey,
      branch_B_number,
      branch_B_assets
    ),
  ISNOTBLANK(
    LOOKUP(
      survey_branch_number,
      survey,
      branch_C_number,
      branch_C_assets
    )
  ),
    LOOKUP(
      survey_branch_number,
      survey,
      branch_C_number,
      branch_C_assets
    ),
  ISNOTBLANK(
    LOOKUP(
      survey_branch_number,
      survey,
      branch_D_number,
      branch_D_assets
    )
  ),
    LOOKUP(
      survey_branch_number,
      survey,
      branch_D_number,
      branch_D_assets
    ),
  TRUE,
    LOOKUP(
      survey_branch_number,
      survey,
      branch_E_number,
      branch_E_assets
    )
)

simplifies to:

more efficient

ANY(
  LIST(
    LOOKUP(
      survey_branch_number,
      survey,
      branch_A_number,
      branch_A_assets
    ),
    LOOKUP(
      survey_branch_number,
      survey,
      branch_B_number,
      branch_B_assets
    ),
    LOOKUP(
      survey_branch_number,
      survey,
      branch_C_number,
      branch_C_assets
    ),
    LOOKUP(
      survey_branch_number,
      survey,
      branch_D_number,
      branch_D_assets
    ),
    LOOKUP(
      survey_branch_number,
      survey,
      branch_E_number,
      branch_E_assets
    )
  )
  - LIST("")
)

[Jake_Naude]

Hi @Steve,

Thanks for coming in… and for your time spent on this…!

I’m busy trying to figure out where to insert your expression in my Initial Value…

Please could you advise?

ANY(
LIST(
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_A_number”,
“branch_A_assets”
),
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_B_number”,
“branch_B_assets”
),
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_C_number”,
“branch_C_assets”
),
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_D_number”,
“branch_D_assets”
),
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_E_number”,
“branch_E_assets”
)
)
-LIST("")
)

[Steve]

Jake_Naude:

I’m busy trying to figure out where to insert your expression in my Initial Value…

This is a nonsense statement. The Initial value expression is an expression. I provided you with an expression. Put my expression in Initial value…if that’s actually where you want it…

You haven’t explained (clearly) what you want to do with this list you’ve asked us to generate for you. If you want the user to select one of the values from a dropdown menu, use what we’ve provided as a column’s Valid If expression. If you want the user to choose more than one value, make the column type EnumList.

[Jake_Naude]

Sorry, my misunderstanding @Steve… I already have something in InitialValue and I didn’t realise this was to replace that…

IFS(

ISBLANK([survey_branch_number]),

ORDERBY(
SELECT(asset[id],
IN([id],
SORT(
UNIQUE(
SELECT(rf_design[Source Asset ID],
AND(
[_THISROW].[select_asset_type]=[source_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
))
+
SELECT(rf_design[Destination Asset ID],
AND(
[_THISROW-2].[select_asset_type]=[destination_asset_type],
[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
[lcs] = [_THISROW-2].[LCS],
NOT(
[destination_asset_type]=‘Cabinet’
)))
),FALSE
)),TRUE
),[asset_name]
),

AND(
[survey_type] = “Single Asset”,
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) = 1)
)))>0
),

LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) = 1)
))),
“survey”,"_ROWNUMBER",“survey_asset_id”
),

AND(
[survey_type] = “Multiple Assets”,
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) > 1),
OR(
[select_asset_type] = “CAT6A”,
[select_asset_type] = “Fibre”,
[select_asset_type] = “Jumper”,
[select_asset_type] = “Power”
))))>0
),

LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) > 1),
OR(
[select_asset_type] = “CAT6A”,
[select_asset_type] = “Fibre”,
[select_asset_type] = “Jumper”,
[select_asset_type] = “Power”
)))),
“survey”,"_ROWNUMBER",“survey_asset_id”
)
)

-[branch_A_assets]
-[branch_B_assets]
-[branch_C_assets]
-[branch_D_assets]
-[branch_E_assets]

[Jake_Naude]

@Steve & @LeventK

Thank you both for your time… It’s 02:30 in the UK and I can’t think straight any more… I’ll get some sleep and pick this up when I surface… Cheers…

[Jake_Naude]

Hi @Steve and @LeventK,

I have your expression in a [test_column] ValidIf and I’m getting an error:

Column Type ‘Enum’ has an invalid data validation constraint

I tried this ValidIf expression:

ANY(
LIST(
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_A_number”,
“branch_A_assets”
),
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_B_number”,
“branch_B_assets”
),
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_C_number”,
“branch_C_assets”
),
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_D_number”,
“branch_D_assets”
),
LOOKUP(
“survey_branch_number”,
“survey”,
“branch_E_number”,
“branch_E_assets”
)
)

• LIST("")
  )

I also tried this ValidIf but it yeilds the same error:

IFS(
ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,
“branch_A_number”, “branch_A_assets”)),
LOOKUP(“survey_branch_number”, “survey”,
“branch_A_number”, “branch_A_assets”),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,
“branch_B_number”, “branch_B_assets”)),
LOOKUP(“survey_branch_number”, “survey”,
“branch_B_number”, “branch_B_assets”),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,
“branch_C_number”, “branch_C_assets”)),
LOOKUP(“survey_branch_number”, “survey”,
“branch_C_number”, “branch_C_assets”),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,
“branch_D_number”, “branch_D_assets”)),
LOOKUP(“survey_branch_number”, “survey”,
“branch_D_number”, “branch_D_assets”),
TRUE,
LOOKUP(“survey_branch_number”, “survey”,
“branch_E_number”, “branch_E_assets”)
)
)
)
)

I’ve taken a look here:

Invalid Constraint Error Questions

Hi. I am trying to implement some logic based on the value of a column [Data Capture Type]. If the value of this column is “By Dependent Dropdown” it brings the user through a collection of drop down lists in order to set the value of the [Product Code]. If the value of the [Data Capture Type] column is “By Product Code” the [Product Code] column is populated with the entire list of Product Codes from the Product_Master_File table and based on the users selection it automatically sets the values…

and noted this:

Make sure your Enum and/or EnumList columns have all possible values explicitly defined, OR make sure Allow other values is ON .

I’ve gone through all of the columns involved in the lookup and confirmed that that all have “allow other values” set to ON.

I also tried wrapping the lookuo in a SPLIT() but got a NUL result:

Referencing an Enumlist for Validiation Questions

Is it possible to pull an enumlist from a specific record and use that as your data validation? For example, I have a list of buildings that are in a specific change request. The user can then create a change order from this request but I want to give them the ability to select only the buildings if not all buildings were included in the change order. I tried a lookup command but it seems to be yielding an error. lookup([_thisrow].[Related_CR],“Change_Requests_PMO”,“CR_UID”,“Sites”) Error M…

I created a list to help troubleshoot:

UNIQUE(
SELECT(
survey[branch_A_assets],
[branch_A_number] > 0
)
)

And I get a “This entry is invalid” error where an item is a coma separated value:

But I don’t get the “This entry is invalid” error where an item is a not coma separated value:

Could this be related to main issue?

Column Type ‘Enum’ has an invalid data validation constraint

Any advice would be appreciated…

[Steve]

Remove data validation for the spreadsheet column.

[Jake_Naude]

Hi @Steve

Any chance it has something to do with this odd behaviour?

I created a list to help troubleshoot:

UNIQUE(
SELECT(
survey[branch_A_assets],
[branch_A_number] > 0
)
)

And I get a “This entry is invalid” error where an item is a coma separated value:

But I don’t get the “This entry is invalid” error where an item is a not coma separated value:

[Steve]

Try this:

SORT(
  UNIQUE(
    SPLIT(
      (
        LIST(
          LOOKUP(
            survey_branch_number,
            survey,
            branch_A_number,
            branch_A_assets
          ),
          LOOKUP(
            survey_branch_number,
            survey,
            branch_B_number,
            branch_B_assets
          ),
          LOOKUP(
            survey_branch_number,
            survey,
            branch_C_number,
            branch_C_assets
          ),
          LOOKUP(
            survey_branch_number,
            survey,
            branch_D_number,
            branch_D_assets
          ),
          LOOKUP(
            survey_branch_number,
            survey,
            branch_E_number,
            branch_E_assets
          )
        )
        - LIST("")
      ),
      " , "
    )
  )
)

[Jake_Naude]

Hi @Steve,

Thank you… I did as suggested above and not getting the error any more… and moving forward again…

Cheers…

[Jake_Naude]

Hi @LeventK,

Thanks for your time.

I’d be very grateful if you could spare a little more to help me apply your expression?

It feels like it belongs in this ValidIf in [asset_name] EnumList. BAseType Ref, (asset table)

I’ve tried it in a coupe of places but can’t get it to work.

I’ve also tried it on it’s own as an Initial Value

If you do have time, here’s how the current ValidIf flows:

• Produce a full list of asset names for this cabinet from rf_design table if survey branch number is blank i.e. if it’s the first survey on this cabinet

IFS(

ISBLANK([survey_branch_number]),

ORDERBY(
SELECT(asset[id],
IN([id],
SORT(
UNIQUE(
SELECT(rf_design[Source Asset ID],
AND(
[_THISROW].[select_asset_type]=[source_asset_type],
[main_asset_name] = [_THISROW].[select_cabinet_to_survey],
[lcs] = [_THISROW].[LCS]
))
+
SELECT(rf_design[Destination Asset ID],
AND(
[_THISROW-2].[select_asset_type]=[destination_asset_type],
[main_asset_name] = [_THISROW-2].[select_cabinet_to_survey],
[lcs] = [_THISROW-2].[LCS],
NOT(
[destination_asset_type]=‘Cabinet’
)))
),FALSE
)),TRUE
),[asset_name]
),

• if it’s a survey of single asset by this surveyor

AND(
[survey_type] = “Single Asset”,
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) = 1)
)))>0
),

• then return the MAX survey_asset_id (one)

LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) = 1)
))),
“survey”,"_ROWNUMBER",“survey_asset_id”
),

• if it’s a survey of multiple assets - of certain cable types - by this surveyor

AND(
[survey_type] = “Multiple Assets”,
COUNT(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) > 1),
OR(
[select_asset_type] = “CAT6A”,
[select_asset_type] = “Fibre”,
[select_asset_type] = “Jumper”,
[select_asset_type] = “Power”
))))>0
),

• then return MAX survey_asset_id (many)

LOOKUP(
MAX(
SELECT(
survey[_ROWNUMBER],
AND(
[survey_branch_number] = [_THISROW].[survey_branch_number],
[surveyor_email] = USEREMAIL(),
(COUNT([survey_asset_id]) > 1),
OR(
[select_asset_type] = “CAT6A”,
[select_asset_type] = “Fibre”,
[select_asset_type] = “Jumper”,
[select_asset_type] = “Power”
)))),
“survey”,"_ROWNUMBER",“survey_asset_id”
)
)

• this produces 6 asset names to chose from before a branch number is selected

• if branch 1 is selected then the three assets from this surveyor’s last survey are presented

• if branch 2/3/4/5 are selected, then your expression needs to kick into action:
• please could you advise where to insert it?

IFS(
ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,
“branch_A_number”, “branch_A_assets”)),
LOOKUP(“survey_branch_number”, “survey”,
“branch_A_number”, “branch_A_assets”),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,
“branch_B_number”, “branch_B_assets”)),
LOOKUP(“survey_branch_number”, “survey”,
“branch_B_number”, “branch_B_assets”),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,
“branch_C_number”, “branch_C_assets”)),
LOOKUP(“survey_branch_number”, “survey”,
“branch_C_number”, “branch_C_assets”),
TRUE,
IFS(
ISNOTBLANK(LOOKUP(“survey_branch_number”, “survey”,
“branch_D_number”, “branch_D_assets”)),
LOOKUP(“survey_branch_number”, “survey”,
“branch_D_number”, “branch_D_assets”),
TRUE,
LOOKUP(“survey_branch_number”, “survey”,
“branch_E_number”, “branch_E_assets”)
)
)
)
)

• Many thanks…

[Steve]

Try:

UNIQUE(
  SORT(
    SPLIT(
      (
        SELECT(
          survey[branch_A_assets],
          AND(
            ([_THISROW].[survey_branch_number] = [branch_A_number]),
            ([_THISROW].[select_asset_type] = [select_asset_type]),
            ([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
            ([_THISROW].[LCS] = [lcs])
          )
        )
        + SELECT(
          survey[branch_B_assets],
          AND(
            ([_THISROW].[survey_branch_number] = [branch_B_number]),
            ([_THISROW].[select_asset_type] = [select_asset_type]),
            ([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
            ([_THISROW].[LCS] = [lcs])
          )
        )
        ...
        + SELECT(
          survey[branch_E_assets],
          AND(
            ([_THISROW].[survey_branch_number] = [branch_E_number]),
            ([_THISROW].[select_asset_type] = [select_asset_type]),
            ([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
            ([_THISROW].[LCS] = [lcs])
          )
        )
      ),
      " , "
    )
  )
)

[Jake_Naude]

Steve:

UNIQUE(
  SORT(
    SPLIT(
      (
        SELECT(
          survey[branch_A_assets],
          AND(
            ([_THISROW].[survey_branch_number] = [branch_A_number]),
            ([_THISROW].[select_asset_type] = [select_asset_type]),
            ([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
            ([_THISROW].[LCS] = [lcs])
          )
        )
        + SELECT(
          survey[branch_B_assets],
          AND(
            ([_THISROW].[survey_branch_number] = [branch_B_number]),
            ([_THISROW].[select_asset_type] = [select_asset_type]),
            ([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
            ([_THISROW].[LCS] = [lcs])
          )
        )
        ...
        + SELECT(
          survey[branch_E_assets],
          AND(
            ([_THISROW].[survey_branch_number] = [branch_E_number]),
            ([_THISROW].[select_asset_type] = [select_asset_type]),
            ([_THISROW].[select_cabinet_to_survey] = [select_cabinet_to_survey]),
            ([_THISROW].[LCS] = [lcs])
          )
        )
      ),
      " , "
    )
  )
)

Hi @Steve,

I guess once I get the other bit working I’ll apply this… I can see what it’s going to do… Cheers…

[Jake_Naude]

Hi @Steve,

I don’t use any data validation on the excel sheet but I went in anyway and cleared all just in case… Still getting the same error…

I did notice that the number columns in the spreadsheet were formatted to “text” so I’ve changed them to numbers and am busy testing now…

Corrected formatting in sheet and tested… Still getting the same error…