Find last row containing a given value

[rez13]

Hi Excel world. Help please!

I want to scan a range of rows for any cell which contain a given value. What I need returned is the maximum row number (i.e. last row) which contains the given value.

Thanks!

 

[Legacy 51064]

This should do the trick for you:

=INDEX(A:A,MATCH(9.99999999999999E307,A:A))
=LOOKUP(9.99999999999999E+307,A:A)
=MATCH(9.99999999999999E+307,A:A)

 

[rez13]

Thanks 98illini. But I'm not sure how to apply what you're saying - where in the formulae do I enter my given value?
Perhaps I should explain my specific problem:


I have 3 projects - let's call them PR1,PR2,PR3. Each project has a row in a Project Summary sheet.

In another sheet of the workbook I have:
In A2:A101. a series of ascending dates (i.e. representing 100 days).
Columns B-I representing the 8 working hours in each day. So each cell in B2-I101 represents a working hour on a certain date.

In B2:I101 the project codes (PR1, PR2) etc. are entered, indicating on which dates and in which hour work on each project is scheduled to be done.
Any cell B2:I101 could contain any of the 3 project codes, and each code could appear in more than one "hour cell".


In my Project Summary sheet, what I need to look up is the latest date in A2:A101 on which work for each particular project is scheduled.
If I move the project codes about to re-schedule work, I want the "latest" date in the summary shet to automatically update.

Hope you can solve this one & Thanx!

 

[barry houdini]

Try this formula

=MAX(IF($B$2:$I$101="PR1",A$2:$A$101))

confirmed with CTRL+SHIFT+ENTER

 

[rez13]

Brilliant!

Thanks Barry. The solution didn't escape you - ha ha!

 

[barry houdini]

Hey! houdini gags, I like it

 

[Yogi Anand]

Hi rez13:

If the dates in column A are always in ascending order, Barry's formula will give you the correct result, however, if it can not be guaranteed that the dates in column A will always be in ascending order, I suggest you use the following array formula ...

=INDEX(A:A,MAX(IF($B$2:$I$109="PR1",ROW($B$2:$I$109))))