Have a formula resulting in a number. Must search the number in another table and return word related to that number

I would do that easily in Excel, but I am failing miserably at Grist.

Basically, I have a column where person choose Risk Probability and another column where you choose Risk Impact. I have another column that multiplies one by the other.

Then I have another table (RiskDegree)where first column is Numbers and second column is Risk Degree.

So if for example, person choose High Impact and High probability, the result is 25.

Then I must searc for 25 in the RiskDegree table first column (Number) and get the corresponding value of the second column, which for 25 is “Dangerous Risk”.

Hi there!

In Grist, you can use lookup functions to find values in other tables. Grist has lookupOne which finds a single record that matches the parameters given and lookupRecords which finds a list of records that match the parameters given.

Our References and Lookups article goes over these two functions in depth.

Because you are looking for a single record where the RiskDegree (Number) match, we will use lookupOne.

I created an example (based on your explanation) for you: Community #1545 - Grist

The ‘Risk Degree - Text’ column of the ‘Risk Probability’ table contains the following lookupOne function:

Risk_Degree.lookupOne(Number=$Risk_Degree_Num).Value

The general format for a lookupOne formula is:

[Table_Name].lookupOne([A]=$[B])

[Table_Name] is the name of the table you want to lookup data in. In the example, the table we are looking up data in is the ‘Risk Degree’ table.

[A]=$[B] is what two columns are we trying to match in order to find the correct record.

[A] is the column in the table being looked up (named at the beginning of the formula). In our example, we are looking up the ‘Number’ column in the ‘Risk Degree’ table

[B] is the column in the current table / the table you are entering the formula in. In our example, that’s the ‘Risk Degree - Num’ column. We want to match the value in the ‘Risk Degree - Num’ column to a value in the ‘Number’ column of the ‘Risk Degree’ table.

LookupOne function finds a record. So in this case, it finds the entire record in the ‘Risk Degree’ table where the value in the ‘Number’ column matches the value in the ‘Risk Degree - Num’ column of the ‘Risk Probability’ table.

At the end of the formula, we have .Value. We use dot notation to pull the value from the Value column for the record we found with our lookupOne function.

Let me know if you have any questions.

Thanks,
Natalie

1 Like

Thanks a lot Nathalie. It seems the only reason I failed is because the last .value doesn´t show options, so I wrote by hand AND missed a capital letter… it should be GraudeRisco and I wrote Grauderisco… bummer. Anyway, your explanation is great (better than the help file which I had already read but found it less it lacking)