Extracting data from a table of values

L

Lee Kelly

Hello Again

Same spreadsheet as before. I now have a sheet titled rates with a table ranging from A1 - J21.

A - J represent house types and 1 - 21 represent operations. ie Cell D5 contains the cost of brickwork on a Ascot type house.

Now, on sheet 2, when I enter a house type in A1 and then a operation in B1 I would like a function that extracts the cost of that operation on that particular type of house from the schedule of rates to appear in Cell C1.

Can anybody tell me the formula to enter into C1 to make this happen?

Is this possible?
 
F

Frank Kabel

Hi
try in C1
=INDEX('rates'!A1:J21,MATCH(B1,'rates'!A1:A21,0),MATCH(A1,'rates'!A1:J1
,0))
 
L

Lee Kelly

Thanks Frank. I have copied the formula down Column C but #N/A appears when no value is in either A or B.

Is there a way to leave the Cell blank when this occurs?
 
F

Frank Kabel

Hi
try
=IF(COUNTA(A1:B1)=2,INDEX('rates'!A1:J21,MATCH(B1,'rates'!A1:A21,0),MAT
CH(A1,'rates'!A1:J1
,0)),"")
 
Top