2-Dimensional Matrix Look-Up

[Paul_Neuroth]

Hello,

i am working on a look-up for a 2 dimensional matrix. A little bit of context for my problem:

I am trying to find a value based on two different (numerical) inputs: mileage of a car and operational time. I have already implemented the matrix and a junction table in the style of this base, which was posted on a quite simmiliar topic ([Probability matrix value driven by two "single-select" fields / nested-ifs / multi-variate lookup]) before:

[[https://airtable.com/shr2THQFtsbPF8Bdz/tbl7Jhxg5UWlfGzgv/viwkkudWdLBdVdPqC?blocks=hide]]

My roll-up formula in my “opportunities” table is using a combination of MID(), LEN() and FIND() formulas, in order to return the assoicated percentage. However, i have encountered several problems:

• The fields which are to be looked have a range of different lengths (e.g. -0.01, 0.14), so that the MID() formula doesnt work as the different values do not have the same length
• The Rollup formula in my “Opportunities” table is not returning a percentage but a string. As i want to use the returned percentage for calculations, in need the lookup to return me a percentage and not a string

I’d be glad for any suggestions!
Thank you very much

[Bill_French · Accepted solution]

Paul_Neuroth:

the MID() formula doesnt work as the different values do not have the same length

Hmmm, sounds like you might be better off building out the predictive logic in code, not formulas.

Paul_Neuroth:

i want to use the returned percentage for calculations

Can’t you simply create another formula field that converts the string to an actual percentage?

[Paul_Neuroth]

Hey Bill,

thanks for your reply. As i posted, there was already a solution for this problem (Seen in the Airtable Base).

I was just wondering, how the user managed to do that, as it would be the perfect solution for my problem.

Bill.French:

Can’t you simply create another formula field that converts the string to an actual percentage?

Yes, thats true, however it would not solve my problem regarding the different lenghts of the strings which have to be returned.