Trying to use Multiple Rows for getting rate for different places

[Krishna Tandel]

I have added Items in Rows and Prices in Colums. Now prices differ from Two Different Locations. 

Example 

Item        Place1      Place2
Item1       20             30
Item2       30             50

Now in other excel sheet i am making a Balance Sheet where i can keep regular track of my Balance with the Purchase from the same company but from different locations . 

In other sheet i have format of 


Date ,Place[Selection Dropdown of Sheet1 Place1,Place2],Item[Selection From Sheet1 Items] ,Quantity ,Rate [This should bring the rate depending on the Place and Item Selected from Dropdown], Amount=Quantity*Rate ofcourse 

I have attached a example excel sheet . If anyone can help me out

[#2 · Accepted solution]

Hi Krishna,

Use INDEX/MATCH on range with all your prices. For your example in F6

=INDEX(Prices!$C$6:$D$8,
     MATCH(Balance!D6,Prices!$B$6:$B$8,0),
     MATCH(Balance!C6,Prices!$C$5:$D$5,0)
)