Forum Discussion
Search across a row and return value 3 columns over
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?
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.
