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…
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)
)
)
)
)
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…
This:
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:
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:
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,
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("")
)
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…
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]
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…
Remove data validation for the spreadsheet column.
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("")
),
" , "
)
)
)
Hi @Steve,
Thank you… I did as suggested above and not getting the error any more… and moving forward again…
Cheers…
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:
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”
)
)
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”)
)
)
)
)
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])
)
)
),
" , "
)
)
)
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…
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…
User | Count |
---|---|
41 | |
31 | |
29 | |
16 | |
14 |