vlookup when correct

M

mas

I have a lookup table and have put the formula into another worksheet where it works. I am chaning an old number to a new number. However if a value is entered which is neither on the lookup table or the new number the lookup selects the last number in my lookup table. Does anyone know how i can make it stay the same if its not there or if its the new number to simply keep it the same.
Thanks
 
R

Ron de Bruin

Hi Mas

Look at the last argument in Vlookup(True or False)

If you don't like the error you can use this

=IF(ISNA(VLOOKUP(.............)),0,VLOOKUP(...............))

This will return a zero if the Vlookup returns a #N/A error.

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




mas said:
I have a lookup table and have put the formula into another worksheet where it works. I am chaning an old number to a new
Click to expand...
number. However if a value is entered which is neither on the lookup table or the new number the lookup selects the last number
in my lookup table. Does anyone know how i can make it stay the same if its not there or if its the new number to simply keep it
the same.
 
F

Fable

You may want to try

VLOOKUP(VALUE,RANGE,ROW,FALSE)

The key part that I think will solve your problem is placing a "FALSE
at the end of your formula, by place "FALSE" it tell the formula tha
not necessarily your result is in certain order, thus returing the las
value in your record set. Hope this helps
 
J

JulieD

Hi Fable

sorry to butt in (and i don't even know the original question) but the
syntax of VLOOKUP is

=VLOOKUP(Value, Range - or Table array, COLUMN NUMBER, false)

where false requires the VLOOKUP to return an EXACT match as opposed to an
approximate match. If no exact match is found (when False is specified)
then the VLOOKUP will return #NA.

Cheers
JulieD
 
Top