Lookup based on two fields?

[Ann_Yeom]

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%.

Any suggestions welcome! Thank you!

[Nathaniel_Grano]

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.

[Ann_Yeom]

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?

[Nathaniel_Grano]

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}?

[Ann_Yeom]

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

[Nathaniel_Grano]

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:

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

[Ann_Yeom]

Nathaniel_Granor:

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

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

[Nathaniel_Grano]

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

[Ann_Yeom]

Nathaniel_Granor:

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

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

[Nathaniel_Grano · Accepted solution]

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: