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

[bimi82]

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:

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

How do I get this done ?

[DimaHijazi]

Try this formula 

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

[bimi82]

@DimaHijazi Pershendetje

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.

[bimi82]

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

[jpmhuls]

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).

[bimi82]

@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?

[jpmhuls]

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.

[bimi82]

@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”) 

[bimi82]

Hi

Is there anybody who can assist?

[jpmhuls]

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.