If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
help on a vlookup please
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 |
#2
|
|||
|
|||
help on a vlookup please
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 . |
#3
|
|||
|
|||
help on a vlookup please
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 |
#4
|
|||
|
|||
help on a vlookup please
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 |
#5
|
|||
|
|||
help on a vlookup please
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 . |
#6
|
|||
|
|||
help on a vlookup please
On Apr 5, 2:00*pm, JLatham wrote:
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 .- Hide quoted text - - Show quoted text - Fantastic, Thank You JLatham problem is now solved, thank you for the explanation regards Ditchy |
Thread Tools | |
Display Modes | |
|
|