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

Natalie, in another documentā¦ I have this formula

Orcamento.lookupOne(id=Orcamento_Concreto.lookupOne(id=\$id).OrcamentoFK).Tabela_de_Precos

Itās returning me the position of the record on the tableā¦ āTabela_Precos[2]ā instead of the value.

Actually, that is part of the formula. I wrote it in a different column to see if I could get the value to be used in another formula.

The whole formula needs to return me a single value.

I am at a record from the Orcamento_Concreto table.

This table has a column referencing a type of concrete (table Concreto, column Tipo_Concreto)

And another column referencing the table Orcamento.

Table Orcamento has a column referencing Tabela_Precos.

Table Orcamento_Concreto has a column referencing Tabela_Precos and a column referencing Concreto and a column for price.

Thus, at Orcamento Concreto, when I select a type of Concreto, I need to get the PRICE value from Orcamento_Concreto table, where Concreto column matches Concreto column at Orcamento_Concreto AND Tabela_Precos column matches the Tabela de Precos value of the Orcamento table record referenced by the Orcamento_Concreto record I am atā¦

Gosh, even explaining this created a knot in my head.

Anyway, at Orcamento_Concreto table I created two fields, each with a formula

=Precos.lookupOne(
Concreto=\$Tipo_Concreto,
Tabela=Orcamento.lookupOne(id=\$OrcamentoFK).Tabela_de_Precos
)

AND

=Orcamento.lookupOne(id=Orcamento_Concreto.lookupOne(id=\$id).OrcamentoFK).Tabela_de_Precos

Both return me the positions in the tables where they found the values, instead of the values.

I guess mixing the two would return me the correct value for the Price of Concrete in the PricingTable (Tabela_de_Preco)??

Okā¦ now I understandā¦ if I donĀ“t specify the column I want to see, it shows me the position. Like Excel Index.

This is the final formula I used and itās working

``````Precos.lookupOne(
Concreto=\$Tipo_Concreto,
Tabela=Orcamento.lookupOne(id=\$OrcamentoFK).Tabela_de_Precos
).Preco
``````