Greetings folks,

My formula in P7 is:
=VLOOKUP(O7,$Q$7:$S$21,3)
The answer in P7 appears to be wrong because 20140 is not found in my range.

But the answer in P8 appears to be correct since 20148 is in my range and
the 3rd column in the range (cell S21) says yes.




O P Q R S
7 20140 Yes 4990 WSJO No
8 20148 Yes 5001 WSJO No
9 20576 Yes 5107 WSJO Yes
10 20732 Yes 5144 WSJO No
11 20909 Yes 5350 WSJO No
12 21277 No 5468 WSJO No
13 21295 Yes 5479 WSJO No
14 21361 Yes 5488 WSJO Yes
15 21478 Yes 5745 WSJO No
16 21539 Yes 5821 WSJO Yes
17 21596 Yes 20000 WSJO No
18 21657 No 20111 WSJO Yes
19 21797 Yes 20114 WSJO Yes
20 21883 Yes 20128 WSJO Yes
21 22024 Yes 20148 WSJO Yes

Can anyone tell me what's wrong here?
Thanks a lot!

--
Mike
Jacksonville, Florida

Try this:

=VLOOKUP(O7,$Q$7:$S$21,3,FALSE)

Without the FALSE portion, VLookUp will look for an approximate match rather
than an exact match. With FALSE, VLookUp will return an error if not found.

You may also want to consider something like this:

=IF(ISERROR(VLOOKUP(O7,$Q$7:$S$21,3,FALSE)),"Not
Found",VLOOKUP(O7,$Q$7:$S$21,3,FALSE))

This gives a more informative result rather than just an error.

HTH,
Elkar


"Mike Saffer" wrote:

Greetings folks,

My formula in P7 is:
=VLOOKUP(O7,$Q$7:$S$21,3)
The answer in P7 appears to be wrong because 20140 is not found in my range.

But the answer in P8 appears to be correct since 20148 is in my range and
the 3rd column in the range (cell S21) says yes.




O P Q R S
7 20140 Yes 4990 WSJO No
8 20148 Yes 5001 WSJO No
9 20576 Yes 5107 WSJO Yes
10 20732 Yes 5144 WSJO No
11 20909 Yes 5350 WSJO No
12 21277 No 5468 WSJO No
13 21295 Yes 5479 WSJO No
14 21361 Yes 5488 WSJO Yes
15 21478 Yes 5745 WSJO No
16 21539 Yes 5821 WSJO Yes
17 21596 Yes 20000 WSJO No
18 21657 No 20111 WSJO Yes
19 21797 Yes 20114 WSJO Yes
20 21883 Yes 20128 WSJO Yes
21 22024 Yes 20148 WSJO Yes

Can anyone tell me what's wrong here?
Thanks a lot!

--
Mike
Jacksonville, Florida

Hi Mike

change the formula to =VLOOKUP(O7,$Q$7:$S$21,3,0)

or to do not have a #value try to use

=if(iserror(VLOOKUP(O7,$Q$7:$S$21,3,0)),"",VLOOKUP (O7,$Q$7:$S$21,3,0))


hope this helps
Regards from Brazil
Marcelo


"Mike Saffer" escreveu:

Greetings folks,

My formula in P7 is:
=VLOOKUP(O7,$Q$7:$S$21,3)
The answer in P7 appears to be wrong because 20140 is not found in my range.

But the answer in P8 appears to be correct since 20148 is in my range and
the 3rd column in the range (cell S21) says yes.




O P Q R S
7 20140 Yes 4990 WSJO No
8 20148 Yes 5001 WSJO No
9 20576 Yes 5107 WSJO Yes
10 20732 Yes 5144 WSJO No
11 20909 Yes 5350 WSJO No
12 21277 No 5468 WSJO No
13 21295 Yes 5479 WSJO No
14 21361 Yes 5488 WSJO Yes
15 21478 Yes 5745 WSJO No
16 21539 Yes 5821 WSJO Yes
17 21596 Yes 20000 WSJO No
18 21657 No 20111 WSJO Yes
19 21797 Yes 20114 WSJO Yes
20 21883 Yes 20128 WSJO Yes
21 22024 Yes 20148 WSJO Yes

Can anyone tell me what's wrong here?
Thanks a lot!

--
Mike
Jacksonville, Florida

You have left off the False argument and are getting a "closest match"

Use this instead. =VLOOKUP(O7,$Q$7:$S$21,3,FALSE)

Better yet, trap for the N/A error.

=IF(ISNA(VLOOKUP(O7,$Q$7:$S$21,3,FALSE)),"",VLOOKU P(O7,$Q$7:$S$21,3,FALSE))

I prefer the ISNA to ISERROR .

ISERROR will pick up all errors and may make it hard to troubleshoot.


Gord Dibben MS Excel MVP

On Tue, 20 Jun 2006 13:11:02 -0700, Mike Saffer
wrote:

Greetings folks,

My formula in P7 is:
=VLOOKUP(O7,$Q$7:$S$21,3)
The answer in P7 appears to be wrong because 20140 is not found in my range.

But the answer in P8 appears to be correct since 20148 is in my range and
the 3rd column in the range (cell S21) says yes.




O P Q R S
7 20140 Yes 4990 WSJO No
8 20148 Yes 5001 WSJO No
9 20576 Yes 5107 WSJO Yes
10 20732 Yes 5144 WSJO No
11 20909 Yes 5350 WSJO No
12 21277 No 5468 WSJO No
13 21295 Yes 5479 WSJO No
14 21361 Yes 5488 WSJO Yes
15 21478 Yes 5745 WSJO No
16 21539 Yes 5821 WSJO Yes
17 21596 Yes 20000 WSJO No
18 21657 No 20111 WSJO Yes
19 21797 Yes 20114 WSJO Yes
20 21883 Yes 20128 WSJO Yes
21 22024 Yes 20148 WSJO Yes

Can anyone tell me what's wrong here?
Thanks a lot!

Thank you all.
That helps a lot in understanding the False argument.
Much obliged.


--
Mike
Jacksonville, Florida


"Gord Dibben" wrote:

You have left off the False argument and are getting a "closest match"

Use this instead. =VLOOKUP(O7,$Q$7:$S$21,3,FALSE)

Better yet, trap for the N/A error.

=IF(ISNA(VLOOKUP(O7,$Q$7:$S$21,3,FALSE)),"",VLOOKU P(O7,$Q$7:$S$21,3,FALSE))

I prefer the ISNA to ISERROR .

ISERROR will pick up all errors and may make it hard to troubleshoot.


Gord Dibben MS Excel MVP

On Tue, 20 Jun 2006 13:11:02 -0700, Mike Saffer
wrote:

Greetings folks,

My formula in P7 is:
=VLOOKUP(O7,$Q$7:$S$21,3)
The answer in P7 appears to be wrong because 20140 is not found in my range.

But the answer in P8 appears to be correct since 20148 is in my range and
the 3rd column in the range (cell S21) says yes.




O P Q R S
7 20140 Yes 4990 WSJO No
8 20148 Yes 5001 WSJO No
9 20576 Yes 5107 WSJO Yes
10 20732 Yes 5144 WSJO No
11 20909 Yes 5350 WSJO No
12 21277 No 5468 WSJO No
13 21295 Yes 5479 WSJO No
14 21361 Yes 5488 WSJO Yes
15 21478 Yes 5745 WSJO No
16 21539 Yes 5821 WSJO Yes
17 21596 Yes 20000 WSJO No
18 21657 No 20111 WSJO Yes
19 21797 Yes 20114 WSJO Yes
20 21883 Yes 20128 WSJO Yes
21 22024 Yes 20148 WSJO Yes

Can anyone tell me what's wrong here?
Thanks a lot!