1. INDEX formula:
=INDEX(RANGE,ROWnumber,COLUMNnumber)
So in the example above, to be able see the price for 20 apples, you use:
=INDEX(B2:D4,2,1) ==> Result is $2
2. MATCH formula:
=MATCH(LOOKUPValue,LOOKUPArray,MATCHType)
So in the example above, to know the column for Apple, you use:
=MATCH(“Apple”,B1:D1,0) ==> Result is 1
3. Using names:
Using names makes the formula easy to read, and very flexible. If your data sheet gets more columns or rows, it doesn’t matter if you set up the formula like this:
Define names: (insert, name, define)
Data –> =Sheet1!A1:Sheet1!AD20000
DataRow –> =Sheet1!1:1
DataCol –> =Sheet1!A:A
4. Combine the formulas
=INDEX(Data,MATCH(20,DataCol,0),MATCH(“Apple”,DataRow,0))
Ofcourse you can use input fields for your criteria on another sheet, because of the names you defined on the data sheet, you can put these on any other sheet you want!
Let’s say you put your fruit type in A1, and the number of fruit in A2, and you want A3 to show the price. You put this formula in A3:
=INDEX(Data,MATCH(A2,DataCol,0),MATCH(A1,DataRow,0))