Excel - MATCH from right to left

Asked By Werner Rohrmoser on 26-Apr-08 01:56 AM
Hello,

I have a row, which is populated with a few "X" letters.
Dependent where an "X" is located I'd like to do a calculation.

Example:

A  B  C  D  E  F  G
X       X  X   X

Now I make a calculation in "G2", which uses values out of the last
column
with an "X", in my case out of column "E".
How can I determine the offset of -2 columns from G to E?

Regards
Werner




Bob Phillips replied on 25-Apr-08 09:09 AM
This will give you the column of that cell, but what do you want to do with
it?

MAX(IF(A1:F1="X",COLUMN(A1:F1)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Werner Rohrmoser replied on 26-Apr-08 01:56 AM
Hi Bob,

thank you very much, I'm going to use it
for a formula which calculates the difference between
the production quantities of different fiscal years.
Because I have a few scenarios for one fiscal year I'd like to
be able to select a base scenario with an "X".

Regards
Werner
Bob Phillips replied on 25-Apr-08 10:10 AM
So is what I gave you enough, or do you need it to get something else?

--
HTH

Bob

(there is no email, no snail mail, but somewhere should be gmail in my addy)
Werner Rohrmoser replied on 26-Apr-08 01:56 AM
It's enough, now I calculate the difference between the two columns
and use
the result in the OFFSET function to get the correspondent value,
which I
need for my calculation.

=(G2-OFFSET(G2,0,-COLUMN()+MAX(IF($A$1:F$1="X",COLUMN($A$1:F$1))),
1,1))/
OFFSET(G2,0,-COLUMN()+MAX(IF($A$1:F$1="X",COLUMN($A$1:F$1))),1,1)

When I have an "X" in column "B", then it is equal to "= (G2-B2)/B2".

Or do you have a shorter solution?

Regards
Werner
Bernd P replied on 26-Apr-08 01:56 AM
Hello Werner,

Shorter and non-volatile:
=(G2-LOOKUP(2,1/(A1:F1="X"),A2:F2))/LOOKUP(2,1/(A1:F1="X"),A2:F2)

Regards,
Bernd
Werner Rohrmoser replied on 26-Apr-08 01:57 AM
Hi Bernd,

that is cool!

Thanks.

Werner