Is there anything similar to vlookup (Excel) or AppSheet's Lookup function?

So I have three tables for Risk Analysis.

Probability, Impact and Risk Factor.

There are 5 probabilities in text and number. 5 in impact and number.

Like
Probability
1 - Rare
2 - Occasional
3 - Common
etc

Impact
1- Negligible
2 - Light impact
3 - Medium damage

etc

Now, when the user selects probability and impact, I want to multiply the Probability number vs Impact number.

The resulting number will be X, which will be equal to a number in the Risk Factor that also equals a text.

I don´t want to show the user the number value (like 3x5 = 15). I want to show the user that it’s a “HIGH RATING” Risk, so that the user must act to mitigate the risk, or something.

My initial thought would be for a vlookup in Excel.
How would I do it in Jestor?

Hey @RogerPenna

If I got it right, you can solve it by using two connected tables or even just one.

The logic will be:

  • Create a risks factor table
  • Create a risks analysis table
  • In the risks analysis, create a connected record field to the analysis table
  • In the risks analysis, create a formula field and create if conditions for your probabilities

In the Formula fied, if you click on the “Write your fomula” box and scroll down the list, you see all the possible formulas to use there:

Ok. Not sure I understood. lol

But I will use IFs instead of a Risk Factor table.

If the result of multiplication of impact x probability = 1, then “Very Low”. If >15 then High, if >20 then Extreme, etc.

Now… when using a Single Option Field (drop down list with single select) we can set colors depending on option.

So if I used a Single Option Field I would be able to set “Extreme” as Red, High as Orange, etc, etc

Can I set colors for a text field based on an IF?

Or can I use a Single Select (which has colors) but depend it on a formula (like IF formula with Single Select)???

At the moment only the tag and single selection field have colors

What you can do is create a trick for each result for the formula field, would be something like: “When a field is changed” and then you select the formula field which will have the risks result, and select an action such as “update a record”, then you set to update the single selection field which contains the risks and its colors

1 Like