Data entry simplification

Attached is an example.

Essentially I created a price table. I then named the lenses and materials ranges to be used as a data validation list. I also named the whole prices table for shorter formulas.

Then I created the data sheet and threw in the formula

=INDEX(Prices,MATCH(A2,Lenses,0)+1,MATCH(B2,Materials,0)+1)

Index takes a row number and a column number and returns the corresponding value from a table. So, to get the row and column number, i used match to search down and accross using the named ranges. I added 1 to each result because the ranges did not start in row or column # 1.

Thank you very much for your response!

I think my example may have been a little too simple though. Would there be a way to use this method given if the data had 3-4 details that determined its price?

I should phrase that a little better. Each lense has a different base cost depending on whether it has been mounted or not. And then it has an additional price depending on which material is used. So I really need to have two different tabs for the method Whizbang suggested, or for the data validation method I would need to reference two different lists. How could I go about setting that up? Thank you!