Extracting data from a table of values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Hi
try in C1
=INDEX('rates'!A1:J21,MATCH(B1,'rates'!A1:A21,0),MATCH(A1,'rates'!A1:J1
,0))
 
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?
 
Hi
try
=IF(COUNTA(A1:B1)=2,INDEX('rates'!A1:J21,MATCH(B1,'rates'!A1:A21,0),MAT
CH(A1,'rates'!A1:J1
,0)),"")
 
Back
Top