Lookup across multiple columns

Evening,

This one is a little over my head… :frowning:

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… :slight_smile:

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)
			)
		)
	)
)
4 Likes

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…

:ok_hand:

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… :slight_smile:

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("")
)
2 Likes

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])
          )
        )
      ),
      " , "
    )
  )
)
2 Likes

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("")
)

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… :slight_smile:

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.

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

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]

@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… :slight_smile:

1 Like

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:

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:

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… :slight_smile:

Remove data validation for the spreadsheet column.

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…

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:

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("")
      ),
      " , "
    )
  )
)
2 Likes

Hi @Steve,

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

Cheers…

:slight_smile:

1 Like