Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Lookup based on two fields?

Topic Labels: Base design
Solved
Jump to Solution
495 10
cancel
Showing results for 
Search instead for 
Did you mean: 

Hi is it possible to tell airtable to look at two fields and look it up/match the fields on a different table and return the corresponding value?

For example:
Looking at the first image, L5 field (Media Engineering) and the size field (s) lookup the value for “s” in another table, second image, AND for Media Engineering and return the corresponding value? In this case it should return 20%.
team

size

Any suggestions welcome! Thank you!

1 Solution

Accepted Solutions

You need to make sure that each of the fields reference in the formula matches up with the actual name of the lookup field in your table. From the original image it looked like none of the % lookup fields had been recognized by Airtable. Did you add them as lookups to your table? If so, check that the name of each one in the formula (inside the { } brackets) matches exactly.

Here’s what it looks like in the demo table I set up:
image

See Solution in Thread

10 Replies 10

In this case, I would include all 3 of the % columns as lookup fields in your first table but keep then hidden. Then I would create a Formula-type field that chooses one of the 3 percentage values based on the value of the L5 column. The formula would look something like this:

SWITCH({L5},
               "Media Engineering",{Media Engineering},
              "Client Engineering", {Client Engineering},
              "Enterprise Portfolio", {Enterprise Portfolio}
)

You’ll need to update the field names in the curly braces { } to reflect the names of the lookup columns you create.

Thankyou for the suggestion! It works!

Any way to upgrade it where I don’t have add 3 of the % columns for each field I create to the right?

For example, I used Nov 22 as the field but if I continue to make Dec 22 and Jan 22 to the right, I would have to add 3 of the % columns as lookup fields each time. Any suggestions?

What would be controlling the values in your Dec and Jan columns? Would you be adding in a new column like {L5} and another new column like {Size}?

I would add “size” as the control column each time.

OK, so I have a solution for you, but it’s kinda funky!

You need to transpose the table that has the percentages like this:
image

Your L5 column is no longer a Single Select field, but is now a “Link to another record” field pointing to the table that has the percentage estimates. You’d then include all 5 of the size columns as lookups.

Each month, you’d create a Size column of type Single Select with the options XS, S, M, L, and XL
Finally, each month you’d create a formula column with this formula:

SWITCH({11/22 Size},
  "XS",{XS (from L5)},
  "S",{S (from L5)},
  "M",{M (from L5)},
  "L",{L (from L5)},
  "XL",{XL (from L5)},
  ""
)

Note that when you copy the formula field each month, you’ll have to update which column the formula is referencing to be the new size

Hi Nathaniel, Thanks so much! I feel like that solution would do the trick! But I’m coming across an error with the formula. Does the {XS {from L5)} part of the formula work? I’m getting this error

at

Whoops, you’re missing the closing parentheses. Just through a ) at the end of the whole thing!

Ah haha~ i added the closing parentheses. Still no luck :frowning:
Reallllly appreciate the help!

att

You need to make sure that each of the fields reference in the formula matches up with the actual name of the lookup field in your table. From the original image it looked like none of the % lookup fields had been recognized by Airtable. Did you add them as lookups to your table? If so, check that the name of each one in the formula (inside the { } brackets) matches exactly.

Here’s what it looks like in the demo table I set up:
image

Oh my goodness! I should have known that, :grinning_face_with_sweat: lol. Thank you! Thank you!

This works great! Thanks so much!!