vlookup when correct

[Guest]

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

 

[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)

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.

 

[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

 

[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