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

@liz123395 

Try this.

=INDEX(3:3,MATCH("x",3:3,0)+3)

 

@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?

@liz123395 

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.