Look up problem

[Guest]

I am doing a game for use in lessons .
Kids have to type in a cell an item to buy i.e apple. the price is entered
in an adjacent cell from a vlookup table:

Range 1 Range 2
Apple 6
Pear 4
Cherries 2

Problem is when the cell has no entry it displays #N/A. I need it to be
blANK but can't find out how to suppress the error mark
Also is you enter a few letters instead of a word it returns a number value
from the table. How can I force to only respond to a word in the left column?
BTW the values in the left column are sorted alpha betically.
Or....have I chosen the wrong approach to the task!
Any help greatly appreciated

 

[Anne Troy]

Hi, Kim. First part: =if(isna(vlookupformula),"",vlookupformula)

Second part: No, Excel doesn't have these feature. However, if you create
rows above and hide them, and they have the data in them and you don't leave
any spaces, the autocomplete will kick in.
************
Anne Troy
www.OfficeArticles.com

 

[Guest]

one way
=IF(ISNA(VLOOKUP(A1,table,2,FALSE)),"",VLOOKUP(A1,table,2,FALSE))
HTH

 

[Ian]

Hi, Kim. First part: =if(isna(vlookupformula),"",vlookupformula)

Second part: No, Excel doesn't have these feature. However, if you create
rows above and hide them, and they have the data in them and you don't
leave any spaces, the autocomplete will kick in.
************
Anne Troy
www.OfficeArticles.com

Anne is right with the first part, but the VLOOKUP syntax is
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
If you enter FALSE as range_lookup, only exact matches will be returned.
eg =IF(ISNA(VLOOKUP(E1,A1:B6,2,FALSE)),"",VLOOKUP(E1,A1:B6,2,FALSE)) where
the lookup data is in A1:B6 and the entered value is in E1