Excel 2002 : Why VLOOKUP formula does not show blanks or N/A ?

[Mr. Low]

Dear Sir,

I have a problem using Vlookup formula .

The formula does not show blanks for items that are not found in the look up
table.

Lets take the following workheet example :

A B
Item Price
1 5110 25
2 5111 25
3 5112 25
4 5113 30
5 5114 30
6 5115 36
7 5116 36


Look up table
Item Price
20 5110 25
21 5113 30
22 5115 36
23 5120 40

When I enter =VLOOKUP (A1,A$20:B$23, 2) at B1 and copy down to B7, I always
get the answer as illustrated.

The items that are not found in the look up table has the value of the last
item that is found in the in the table.

The formula does not show blank or N/A (not available).

Is there any other way or other formula for getting what I want ?


Thanks

Low

 

[bpeltzer]

Use the final argument of the vlookup function to ensure an exact match:
=VLOOKUP (A1,A$20:B$23, 2, FALSE)

 

[Mr. Low]

Thanks for your quick response.
--
A36B58K641

Use the final argument of the vlookup function to ensure an exact match:
=VLOOKUP (A1,A$20:B$23, 2, FALSE)

 

[Teethless mama]

=IF(ISNA(VLOOKUP (A1,A$20:B$23, 2,0)),"",VLOOKUP (A1,A$20:B$23, 2,0))