Finding the price of an item with a price code

[XeusB]

I have a list of products with details, each product have different price codes(PC) from 1 to 10, in another table I have the prices defined as per the item number and the price code.


In a column i need to apply a formula which would match the item number and price code, and then look at the corresponding price from the the second table.


Please help me with the formula.


The Table structure for the second table in something like this:


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'Item Code ' PC1 ' PC2 ' PC3 ' PC4 ' PC5 ' PC6 ' PC7 ' PC8 ' PC9 ' PC10'

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'AAAAAAAAA ' 1.1 ' 1.5 ' 2.1 ' 1.1 ' 3.6 ' 1.1 ' 3.6 ' 1.5 ' 2.1 ' 1.5 '

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

'CCCCCCCCC ' 1.1 ' 2.1 ' 1.5 ' 3.6 ' 1.1 ' 1.5 ' 2.1 ' 1.1 ' 4.2 ' 3.6 '

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Hope its clear.

 

[Hui]

XeusB


Firstly, Welcome to the Chandoo.org forums.


You problem will need an Index/Match combination

like: =INDEX(B2:K3,MATCH(M7,A2:A3,0),MATCH(N7,B1:K1,0))


This is taken from this mock up file here: https://www.dropbox.com/s/unm5x55aykmei73/XeusB.xlsx