Here's what I did to make this work in Excel 2000:

On 'Address Sheet' the first column of your lookup range must match the
field you are looking for in 'Sheet 1' to create the link between the
dataset and the value you are searching. Based on my experience with
VLOOKUP the only way to get a valid response is to make sure the link field
is the first column of the dataset. There may be a different way to do it,
but this has always worked for me.

Based on your information you have the street number as the first column,
therefore Excel is comparing "joe blow" in B2 to street number in 'Address
Sheet' and will not find a match because name < number. If you put the
name column to the left of the address column on 'Address Sheet' you should
get the answer you're seeking.

Best regards,
Joe
Long Beach, CA USA


"Ditchy" wrote in message
...
Hi there
i need some help on a vlookup, I just can't seem to get it to work.
in sheet 1, B2 I have a name joe blow
in sheet 1, A2 I would like their street number (on address sheet)
this is what I have tried and keep getting #N/A or blank

=IF($B20,VLOOKUP($B2,address sheet!$A$1:$O$139,1,FALSE )," ")

address sheet colmn A has the street number
address sheet column B has their name
street numbers are formated as numbers
any ideas?
regards
Ditchy
Ballarat, Australia