How to lookup from different columns based on input

[Angie_Belle]

I am selling several classrooms’ worth of educational materials. Many of the classrooms contain the same items, but from different brands, and therefore, they have different retail values. I have made a table of Materials, organized by which lesson it is, with each column showing the retail price from a different vendor.

On another table, “Inventory List”, I’m identifying each specific item by selecting the generic material name, brand, etc. (see below) I’d like to be able to link to the specific retail price of that brand for that item, but I don’t know how to set it up correctly, as I’m not sure how to say, “Hey, all the prices in this column are linked to this brand only.” Help!

@Andy_Lin1 @W_Vann_Hall

[Andy_Lin1]

I’m not sure there’s an easy way to do this in Airtable. (Although @W_Vann_Hall has some amazing tricks up his sleeve that I won’t even pretend to understand.)
The way I would structure the data would be to make Brand its own field in the Materials table, and only having one price field. It’s not as easy to get a “matrix” of prices, but it would make the lookup in your inventory list only require one field:

1. Add a Brand field to your Materials table.
2. Duplicate the entries as many times as you have brands.
3. Duplicate the Name field, and change the name of the duplicate field to “Item” and change the original to a formula field to be something like {Item}+": "+{Brand}.
4. Reduce the price columns so that there is only one Price column, with the correct price for the item/brand. (You can also delete any rows where the vendor doesn’t sell a particular item.)
5. In your inventory list, add a lookup field to look up the Price column of the Materials table.

Alternatively, you could also do exactly what you want using Excel and INDEX MATCH MATCH, but I suspect that’s not the solution you’re looking for.