Search across a row and return value 3 columns over

Copper Contributor

Hi there, 


I am wanting to search for a value across a row and return a value 3 columns over from the value. In this example, I am looking for the first 'x' in the row. So lets say the row has a 'x' in column AJ, I want to return the value that's in column AM. The problem is I only want to return the value 3 columns over from the first 'x' in the row. 


Does anyone know a way to about this?


3 Replies


Try this.



@Detlef Lewin Hi there, this seems to work except when I put it in a row that doesn't have a 'x' - then I get this message

'There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrectly. '


Is there a way to add into the existing formula 'if can't find 'x'' leave it blank?


You can't put this formula in the same row as it refers to. It creates a circular reference.

You have to change the range from 3:3 to your specific range, e.g. B3:L3.

And the formula must placed outside this range or you get the same problem.