Skip to main content

Newbie Help: Array Formula

  • July 25, 2021
  • 4 replies
  • 51 views
J

I have a Google Sheet currently that uses an Array formula and I want to move to Airtable for
a more stable and long term build.

Let’s say I have a type of product (user chooses crayons for example) and how they use it (they choose stick figures) the combination of those two has a certain % rate.

How do I get Airtable to lookup and return that rate to my client table based on those two inputs?

Thanks in advance!!

4 replies

S
  • SMP
  • Participating Frequently
  • July 25, 2021

If you have the rate stored on the table with the inputs, then you can do a straight lookup. If you’re looking up each of the two inputs, then add a third formula field where you divide and format the results as a percentage.

J

If you have the rate stored on the table with the inputs, then you can do a straight lookup. If you’re looking up each of the two inputs, then add a third formula field where you divide and format the results as a percentage.


Thanks!

Can I look up by both inputs? Example: (column A) Crayons and (column B) Stick Figures is a different rate than Crayons and (column B) Portraits.

S
  • SMP
  • Participating Frequently
  • July 26, 2021

Thanks!

Can I look up by both inputs? Example: (column A) Crayons and (column B) Stick Figures is a different rate than Crayons and (column B) Portraits.


No. You can only lookup one thing at a time. Your goal here is to lookup the rate. Your table infrastructure and calculation fields determine where you’re going to get the rate from.

Where you want to put the calculation fields is determined by how and where you store the inputs.

So let’s say I want to see the percentage of works in my db that are assignments vs. treats, then each work record marks which is which, the participants table records are linked to all works they’ve written, then I added several count fields that were conditional to whether treats or assignments or total.

Now, I’ve a separate table which tracks the percentages. It basically rolls up these counts into total treats, total assignments, and total works across all users then adds a separate formula field to calculate the percentage. Alternatively, I could link and rollup total number of participants into my “rate table” and compare how many of them wrote treats vs. how many total participants (to be more comparable to your clients).

Which is to say, you can definitely 100% look up both inputs… in separate fields. Then you must use a formula field to calculate the rate. Where you do it depends on your table structure.

My example may not be a 1 to 1 to your situation, but it was my most recent work with using lookups for this in particular, so I hope it sort of helps show what’s possible and a path you can explore toward organizing things.

J

Could I have a table for Crayons and in that table have Stick Figures, Portraits, Etc…

Then look up the table “Crayons” based on user input and then look up the rate for “Stick figures” based on the second input?

Terms & ConditionsAccessibility statement