OFFSET Problems
 

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.

OFFSET Problems
 

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.

OFFSET Problems
 

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.

OFFSET Problems
 

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.