MATCH from right to left

[Werner Rohrmoser]

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]

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]

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]

So is what I gave you enough, or do you need it to get something else?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Werner Rohrmoser]

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]

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]

Hi Bernd,

that's cool!

Thanks.

Werner