help on a vlookup please

[Ditchy]

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($B2>0,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

 

[Mike H]

Hi,

Try this

=IF($B2>0,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.

 

[Matt's Dad]

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]

Hi,

Try this

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

Note a couple of things, I changed " " to "" because it's not a good ideato
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.







- 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($B2>0,VLOOKUP($B2,ADDRESS SHEET!$A$2:$C$200,2,FALSE ),"")

any other help much appreciated
regards
Ditchy

 

[JLatham]

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]

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.









- Show quoted text -

Fantastic, Thank You JLatham
problem is now solved, thank you for the explanation
regards
Ditchy