Lookup with offset

[Ron McC]

On the sheet 'Matrix' in column A the cells are merged in pairs and a functionality description is given. In column B we have a profile which is composed of a case and a condition.

On sheet 'Results list' in column B we have the functionalities listed. In column C we want to lookup the case and in column D we want the condition. Getting the case is straightforward enough, but how can I get the condition? Is there some way of getting the contents of a cell offset by one row by the one that is the source of the result of the lookup formula for the case in column C

TI
Ron

 

[humejap]

Would this formula do what you want?

=INDIRECT(CONCATENATE("Matrix!B",MATCH(B1,Matrix!$A$1:$A$8,0)+1))

Change the range Matrix!$A$1:$A$8 to include all the rows of data on
the Mactrix sheet.

 

[Ron McC]

Thanks for your response. The Concatenate bit of the formula works OK, i.e. it returns the correct cell reference on the matrix sheet, but with the result of the INDIRECT function gives a #Ref! error.

Any further thoughts

Incidentally is there any difference between using Cocatenate and simply ="Matrix!B"&MATCH(B1,Matrix!$A$1:$A$8,0)+1) which gives the same result

Thanks agai
Ro

----- humejap > wrote: ----

Would this formula do what you want

=INDIRECT(CONCATENATE("Matrix!B",MATCH(B1,Matrix!$A$1:$A$8,0)+1)

Change the range Matrix!$A$1:$A$8 to include all the rows of data o
the Mactrix sheet