Read Matt's Dad's posting - it kind of explains the problem pretty well.

In a VLOOKUP() formula, the left-most column in the lookup table must
"match" then type of information you use as the first argument. So by trying
to lookup using the Name, you'd need to look up things in column B and so
your vlookup would be like this-
=VLOOKUP(B2,'address sheet'!$B$1:$O$139,2,FALSE)
and that would return whatever is in column C on 'address sheet' (when a
match is found in column B) because column C is the 2nd column of the B1:O139
table.

If you need the street number from column A of 'address sheet' you can't use
VLOOKUP() but you can use plain old LOOKUP().

BUT: in order for LOOKUP() to work properly, the values in the table to be
matched (the names) must be sorted in ascending order A-z.

So, =LOOKUP($B2,'address sheet'!$B$1:$B$139,'address sheet'!$A$1:$A$139)
would return the street number, and
=LOOKUP($B2,'address sheet'!$B$1:$B$139,'address sheet'!$C$1:$C$139)
would return whatever is in column C on a row on 'address sheet' when a
match to the name is made in column B on it.



"Ditchy" wrote:

On Apr 4, 8:46 pm, Mike H wrote:
Hi,

Try this

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

Note a couple of things, I changed " " to "" because it's not a good idea to
return a space, a null string is better.

You are addressing columns A to O but are only using columns A & B but I
left it like that
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.



"Ditchy" wrote:
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
.- Hide quoted text -

- Show quoted text -
Hi Mike
thanks for the response, tried your answer but I still can't get it to
work.
Headers in row 1
ADDRESS SHEET colmn A has the street number
ADDRESS SHEET column B has their name

in a new sheet called streetnumbers, column B has their name
in column A i would like a vlookup to find the "street number" of the
name in column B of the ADDRESS SHEET

this is what i have in A2 of sheet called streetnumbers
=IF($B20,VLOOKUP($B2,ADDRESS SHEET!$A$2:$C$200,2,FALSE ),"")

any other help much appreciated
regards
Ditchy




.