Using MATCH with condition

[Chuck]

I'm attempting to use the MATCH and OFFSET functions (but
certainly open to using others) to return a value based
on a certain condition. An example:

1Q04 2Q04 3Q04E
Revenue 100 110 120
Op. Ex. 90 95 100
Profit 10 15 20

I want to, for example, return 15 (Profit in 2Q04)
subject to the condition that 3Q04 has an E at the end
(thus the need for the OFFSET function) and I am
interested in the Profit row. I have attempted to use
the following formula, but need to pull a value from the
column to the left of 3Q04E. Have tried to use the RIGHT
function in the formula, but no luck so far. Ideas?
Thanks.

=OFFSET(Sheet2!$A$1,MATCH(A1,Sheet2!$A$1:$A$4,0)+1,MATCH
(B1,Sheet2!$A$1:$D$1,0)-2)

 

[duane]

I do not know how to do it directly, but if you create a row where yo
take the right hand character of your top row (1Q04 etc) you can the
use the offset match combo like below. In this case I put the 1Q04 et
in row 4, the right() function in row 3, and I grabbed the data on row
for the last period before the E ending.


=OFFSET(A4,1,MATCH("E",B3:E3,1)-1,1,1

 

[duane]

actually this does work - entered as array (control/shift/enter) - thi
finds you the value 1 row below the row with the 1Q04 etc in it, and
column before the first entry ebding with E

=OFFSET(A4,1,MATCH("E",RIGHT(B44),1)-1,1,1

 

[Ragdyer]

You want to return the row entered in A1 (Profit),
And the column entered in B1 (2Q04),
As long as there is an "E" at the end of 3Q04.

If that's correct, try this:

=IF(RIGHT(Sheet2!D1)<>"E","No
E",INDEX(Sheet2!A14,MATCH(A1,Sheet2!A1:A4,0),MATCH(B1,Sheet2!A11,0)))

 

[Guest]

Thank you very much.

 

[Guest]

Thank you very much.

-----Original Message-----

You want to return the row entered in A1 (Profit),
And the column entered in B1 (2Q04),
As long as there is an "E" at the end of 3Q04.

If that's correct, try this:

=IF(RIGHT(Sheet2!D1)<>"E","No
E",INDEX(Sheet2!A14,MATCH(A1,Sheet2!A1:A4,0),MATCH (B1,Sheet2!A11,0)))
------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
--------------------------------------------------------- ------------------


.