Please forgive what may appear to be a stupid question, but is there a way to
tell Excel to search in reverse (i.e., right to left) in a formula such as:

=OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10,1)-1)

In other words, have cell N10 examined first, followed by cell M10, followed
by cell L10, etc.?
Thanks,
Bob

Hi Bob

Do you mean that you want to return the first instance of the Max
value (rather than the last)? In which case you could simply use:

=OFFSET(C10,0,MATCH(MAX(C10:N10),C10:N10,0)-1)

Have I misunderstood your requirement?

Best regards

Richard

On 5 Feb, 11:52, Bob wrote:
Please forgive what may appear to be a stupid question, but is there a way to
tell Excel to search in reverse (i.e., right to left) in a formula such as:

=OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10,1)-1)

In other words, have cell N10 examined first, followed by cell M10, followed
by cell L10, etc.?
Thanks,
Bob

Hi Richard,
You did not misunderstand my requirement. Thanks for your help!
Bob

"RichardSchollar" wrote:

Hi Bob

Do you mean that you want to return the first instance of the Max
value (rather than the last)? In which case you could simply use:

=OFFSET(C10,0,MATCH(MAX(C10:N10),C10:N10,0)-1)

Have I misunderstood your requirement?

Best regards

Richard

On 5 Feb, 11:52, Bob wrote:
Please forgive what may appear to be a stupid question, but is there a way to
tell Excel to search in reverse (i.e., right to left) in a formula such as:

=OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10,1)-1)

In other words, have cell N10 examined first, followed by cell M10, followed
by cell L10, etc.?
Thanks,
Bob

In short no.

You will need to go to vba to meet your needs.

The otherway is to have a helper row put the column number as a number (use
column() then copy and paste special values) in there.
sort them on the column number in descending order find the value and then
sort back.

But I would use a vba function.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Bob" wrote:

Please forgive what may appear to be a stupid question, but is there a way to
tell Excel to search in reverse (i.e., right to left) in a formula such as:

=OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10,1)-1)

In other words, have cell N10 examined first, followed by cell M10, followed
by cell L10, etc.?
Thanks,
Bob

Martin,
Thanks for the suggestion. For security reasons, I cannot use VBA, so I
need to go with the helper row solution.
Bob


"Martin Fishlock" wrote:

In short no.

You will need to go to vba to meet your needs.

The otherway is to have a helper row put the column number as a number (use
column() then copy and paste special values) in there.
sort them on the column number in descending order find the value and then
sort back.

But I would use a vba function.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Bob" wrote:

Please forgive what may appear to be a stupid question, but is there a way to
tell Excel to search in reverse (i.e., right to left) in a formula such as:

=OFFSET(C10,0,MATCH(MAX(C10:N10)+1,C10:N10,1)-1)

In other words, have cell N10 examined first, followed by cell M10, followed
by cell L10, etc.?
Thanks,
Bob