Getting correct results for lookup?
 

I have this formula:

=LOOKUP("YES",D9:D32,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

Getting correct results for lookup?
 

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...
--
HTH...

Jim Thomlinson


"tgcali" wrote:

I have this formula:

=LOOKUP("YES",D9:D32,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

Getting correct results for lookup?
 

Try this:

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

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

--
Biff
Microsoft Excel MVP


"tgcali" wrote in message
...
I have this formula:

=LOOKUP("YES",D9:D32,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

Getting correct results for lookup?
 

Biff,

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

tgcali

"T. Valko" wrote:

Try this:

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

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

--
Biff
Microsoft Excel MVP


"tgcali" wrote in message
...
I have this formula:

=LOOKUP("YES",D9:D32,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

Getting correct results for lookup?
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"tgcali" wrote in message
...
Biff,

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

tgcali

"T. Valko" wrote:

Try this:

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

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

--
Biff
Microsoft Excel MVP


"tgcali" wrote in message
...
I have this formula:

=LOOKUP("YES",D9:D32,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