Finding last appearance of given value

[Przemek]

Hi, let's suppose we've got following matrix:

A B C D E
1 N Y Y Y
2 N N Y Y
3 N N N Y
4 N N N N


Is there any way to find the row number with given column and taking
into account the last appearance of given value e.g.

find row for column D, where there is last appearance of "Y" value
(starting from the top), in this case it's second row.

Thanks,

Przemek

 

[Gary''s Student]

=MAX(IF(D1100="Y",ROW(D1100)))
This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the ENTER key.

 

[Rick Rothstein]

You can use this array-entered** formula...

=MAX((D1200="Y")*ROW(D1200))

**Commit formula using Ctrl+Shift+Enter, not just Enter by itself.

Once entered as described, this formula can be copied across to see the
results for other columns. The maximum row of 200 used in both ranges is for
example purposes... set it to the maximum row number you expect to have data
in.

 

[Rick Rothstein]

You beat me by 7 seconds (probably because of all the extra words I
typed).<g>

 

[Przemek]

Thank you very much both of them. It works

Przemek

 

[Przemek]

Well, I have another problem.

In some cells in D1200 I have #number instead of figure. Is there a
way to tell max function to avoid these cells?

Przemek

 

[Rick Rothstein]

I'm afraid I don't understand your problem. The formula we gave you is looking for the letter "Y"... if there is a number in any of the cells, it will not match the "Y" and it the formula will not react to it. One thing to reiterate in case you are attributing a problem to the wrong thing... **every** time you edit (touch) the formula (by placing your cursor in the Formula Bar), you must re-commit it using the Ctrl+Shift+Enter keystroke (unless you press the Esc key instead).

--
Rick (MVP - Excel)


Well, I have another problem.

In some cells in D1200 I have #number instead of figure. Is there a
way to tell max function to avoid these cells?

Przemek