Getting correct results for lookup?

[tgcali]

I have this formula:

=LOOKUP("YES",D932,A9:A32)

that returns the number in the appropriate row in column A. This is not
working on every sheet in the workbook. I suspect this is because the entries
in column A can go like this: 1,2,3,3,4,5,RESTART,1,2,3,4,4,5,6, etc.

I get the correct result now, wether it's a number or the word RESTART, but
when I try to add an entry the results of the formula do not update. Let me
clarify, it does update on some sheets, but not all.

What I need is the entry listed in column A for the last row in column D
that contains the word "YES". When the last instance of "YES" is in, say, D15
and I add the word "YES" to D16, I need the formula to return the entry in
A16, but it stays with what's in A15. Again, this is not happening on every
sheet. Most of them will update with the entry in A16, just a few aren't.

I've tried checking for extra spaces, formatting the cell, any ideas?

Thanks in advance,

tgcali

 

[Jim Thomlinson]

Right from the help file for the lookup funciton...

"Important The values in lookup_vector must be placed in ascending order:
....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value."

Functions like match, vlookup and hlookup allow for exact match searches
through unsorted ranges, but not Lookup...

 

[T. Valko]

Try this:

=LOOKUP(2,1/(D932="yes"),A9:A32)

If there are no instances of "yes" the formula will return #N/A.

 

[tgcali]

Biff,

Seems to have worked like a dream. Thank you very much!

tgcali

 

[T. Valko]

You're welcome. Thanks for the feedback!