Here is a formula which returns an error message:

=OFFSET((ADDRESS(ROW(O2),MATCH(O2,A2:K2,0),,false) ,1,2)

I am trying to return the value of a cell two columns over from where it
found the MATCH. The MATCH function works, as does the ROW and ADDRESS
functions. But when I add the OFFSET portion, I get the generic error message.

Any help or alternate approaches appreciated.

Sounds liek you might want

=OFFSET((ADDRESS(ROW(O2),MATCH(O2,A2:K2,0),,false) ,0,2)

Else, you're moving one row down...

"John V" wrote:

Here is a formula which returns an error message:

=OFFSET((ADDRESS(ROW(O2),MATCH(O2,A2:K2,0),,false) ,1,2)

I am trying to return the value of a cell two columns over from where it
found the MATCH. The MATCH function works, as does the ROW and ADDRESS
functions. But when I add the OFFSET portion, I get the generic error message.

Any help or alternate approaches appreciated.

Sean, you are correct. I was thrashing around when I specified 1 for Row.

Sadly, that too generates an error message. The function wizard says the
ADDRESS function evaluates to {$F$2}. Perhaps the problem is that ADDRESS
returns an array??

"Sean Timmons" wrote:

Sounds liek you might want

=OFFSET((ADDRESS(ROW(O2),MATCH(O2,A2:K2,0),,false) ,0,2)

Else, you're moving one row down...

"John V" wrote:

Here is a formula which returns an error message:

=OFFSET((ADDRESS(ROW(O2),MATCH(O2,A2:K2,0),,false) ,1,2)

I am trying to return the value of a cell two columns over from where it
found the MATCH. The MATCH function works, as does the ROW and ADDRESS
functions. But when I add the OFFSET portion, I get the generic error message.

Any help or alternate approaches appreciated.

It's not real clear what you want...

two columns over from where it found the MATCH.

So, if a MATCH is found in K2 return the value from M2?

If that's the case try this:

=INDEX(A2:M2,MATCH(O2,A2:K2,0)+2)


--
Biff
Microsoft Excel MVP


"John V" wrote in message
...
Here is a formula which returns an error message:

=OFFSET((ADDRESS(ROW(O2),MATCH(O2,A2:K2,0),,false) ,1,2)

I am trying to return the value of a cell two columns over from where it
found the MATCH. The MATCH function works, as does the ROW and ADDRESS
functions. But when I add the OFFSET portion, I get the generic error
message.

Any help or alternate approaches appreciated.