cancel
Showing results for 
Search instead for 
Did you mean: 
bimi82
Nintex Newbie

LookUp to a list based on 2 columns and then return the value

Jump to solution

Hello

I am strugling with my current task!

I am using a Calculated Value (on the form) with the formular:

lookup("OptionsGuidance", "Options",Q1_Option,"Guidance")

but it does not work well because it has no reference to which question your option is relying to.

The Q1_Option is a [List Lookup] on the form.

 

Below you see the list, where it is looking at:

Questions.jpg

Hint: the above list uses a vlookup for the QUESTION from a different list.

 

How do I get this done ?

Labels: (1)
Tags (1)
0 Kudos
Reply
17 Replies
DimaHijazi
Nintex Newbie

Re: LookUp to a list based on 2 columns and then return the value

Jump to solution

Try this formula 

lookup("OptionsGuidance", "Options",parselookup(Q1_Option),"Guidance")

 

0 Kudos
Reply
bimi82
Nintex Newbie

Re: LookUp to a list based on 2 columns and then return the value

Jump to solution

@DimaHijazi Pershendetje Smiley Happy

 

The formula will always fetch the results from the first row, however; in my case, I need to show the options for each question.

 

In other words, the lookup needs to compare the Question and also the options with the list.

 

 

0 Kudos
Reply
bimi82
Nintex Newbie

Re: LookUp to a list based on 2 columns and then return the value

Jump to solution

Can anybody assist me with this, further please?

The list, you see above is a combination of another list using lookup such as the Questions.

 

The idea is that I can show the relevant "Guidance" meaning for each option when selected.

As such, I thought I can use the lookup but this only works with first five sections ...

 

lookup("OptionsGuidance", "Options", Q1_Option, "Guidance")

With the above, I cannot get all the relevant guidance info pulled up for each seperate option.
Not sure how to do this ... or maybe I need to use IF statements ??

 

Please advise ...

 

Cheers

0 Kudos
Reply
jpmhuls
Nintex Newbie

Re: LookUp to a list based on 2 columns and then return the value

Jump to solution

Lookup normally does return 1 result. Unless you add a 5th parameter with value true to allow it to return multiple results (see also the Help page on this function, and yes there's is also a 6th paramater you could use). If Q1_Option holds the value of a selected dropdown lookup value, please follow @DimaHijazi's advice and use parselookup(Q1_Opton) which returns the display value of selected lookup value (adding false as a 2nd parameter will return the list item ID of the selected lookup value). I've not used it often myself, but it should be possible to nest lookups (see I cannot get nested lookups working in forms).

0 Kudos
Reply
bimi82
Nintex Newbie

Re: LookUp to a list based on 2 columns and then return the value

Jump to solution

@jpmhuls 

 

Thanks for the feedback.

I am trying to follow the logic of the suggested formula...

 

lookup(“Guidance”, “Options”, parseLookup(lookup("Guidance","Options",Q1_Option,"Guidance",true), “Guidance”) 

The calculated value shows #Value! which indicates that something is not right.

@emha any ideas?

0 Kudos
Reply
jpmhuls
Nintex Newbie

Re: LookUp to a list based on 2 columns and then return the value

Jump to solution

For one, there is 1 closing bracket missing (for the parselookup). In case of nesting, the inner lookup should return only 1 result, which then is used to filter the outer lookup which may return multiple results.

0 Kudos
Reply
bimi82
Nintex Newbie

Re: LookUp to a list based on 2 columns and then return the value

Jump to solution

@jpmhuls 

 

Adding another ) to close the parseLookup leads to the form being blank, not load.
As per your inner lookup etc. you lost me there ... looking at the picture in first topic, what is the right way of nesting if you would do that ?

 

lookup(“Guidance”, “Options”, parseLookup(lookup('Guidance','Options',Q1_Option,'Guidance',true)), “Guidance”) 
0 Kudos
Reply
bimi82
Nintex Newbie

Re: Nested LookUps ? How to ...?

Jump to solution

Hi

 

Is there anybody who can assist?

0 Kudos
Reply
jpmhuls
Nintex Newbie

Re: LookUp to a list based on 2 columns and then return the value

Jump to solution

Hi @bimi82,

If I'm not mistaken, a lookup can only filter on 1 value. The lookup in your parseLookup is set to return multiple values, which then are to be used (assuming parseLookup can handle multiple values) in the outer lookup. So, I would switch the lookups in order to retrieve only 1 result.

 

Also, it may help if you could provide an example of what you try to achieve.

0 Kudos
Reply