How to pull a value based on two conditions from table

Question

How do I create a formula where two data points in Sheet1 are compared to a table in Sheet2 to output the intersecting data.

Thank you very much for any help :)

Answer 1

You use the MATCH function to determine which relative row in sheet2!A2:A4 corresponds to the name in column A of sheet1. Then you use the MATCH again to determine which relative column in sheet2!B1:D1 corresponds to the dessert in column B of sheet 1. Finally you use these two values with the INDEX function to pull the value from sheet2.

For your sample date, the end result in C3 looks like

=INDEX(sheet2!B$2:D$4,MATCH(A3,sheet2!A$2:A$4,0),MATCH(B3,B$1:D$1,0))

and this can be copied to each row in column C