Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP?
I have a list of customers sites where each site has a unique number.
Against each of the numbers is the name of the site for example; 001 London 002 Oxford .. .. 1340 Birmingham 1341 Manchester etc. etc. These are listed in 2 columns on a hidden worksheet in a multi-page spreadsheet. What I would like to do is when the site number is input say in cell A1 on a different worksheet I need to automagically insert the site name that matches that particular site number. So in my example if I input 002 in cell A1 then Oxford appears in B1 I am guessing I need to use VLOOKUP or something but can some kind soul point me in the right direction please? -- Thanks & regards, -pp- |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP?
Let's say you are entering your data in cell A1 and you want B1 to display
the location name =VLOOKUP(A1,Sheet4!$A$1:$B$100,2,false) where your "hidden" sheet is Sheet4 and the data is in A1:B100. Just make sure that column A is sorted in ascending order. "Pheasant PluckerĀ®" wrote: I have a list of customers sites where each site has a unique number. Against each of the numbers is the name of the site for example; 001 London 002 Oxford .. .. 1340 Birmingham 1341 Manchester etc. etc. These are listed in 2 columns on a hidden worksheet in a multi-page spreadsheet. What I would like to do is when the site number is input say in cell A1 on a different worksheet I need to automagically insert the site name that matches that particular site number. So in my example if I input 002 in cell A1 then Oxford appears in B1 I am guessing I need to use VLOOKUP or something but can some kind soul point me in the right direction please? -- Thanks & regards, -pp- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP?
Why do you say that Column A *must* be sorted in ascending order?
You are using "false" as the 4th argument, aren't you? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Barb Reinhardt" wrote in message ... Let's say you are entering your data in cell A1 and you want B1 to display the location name =VLOOKUP(A1,Sheet4!$A$1:$B$100,2,false) where your "hidden" sheet is Sheet4 and the data is in A1:B100. Just make sure that column A is sorted in ascending order. "Pheasant Plucker®" wrote: I have a list of customers sites where each site has a unique number. Against each of the numbers is the name of the site for example; 001 London 002 Oxford .. .. 1340 Birmingham 1341 Manchester etc. etc. These are listed in 2 columns on a hidden worksheet in a multi-page spreadsheet. What I would like to do is when the site number is input say in cell A1 on a different worksheet I need to automagically insert the site name that matches that particular site number. So in my example if I input 002 in cell A1 then Oxford appears in B1 I am guessing I need to use VLOOKUP or something but can some kind soul point me in the right direction please? -- Thanks & regards, -pp- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP?
The false refers to an "exact" match or not. See comments from help for
VLOOKUP for the Range_lookup portion of the function. Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. "Ragdyer" wrote: Why do you say that Column A *must* be sorted in ascending order? You are using "false" as the 4th argument, aren't you? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Barb Reinhardt" wrote in message ... Let's say you are entering your data in cell A1 and you want B1 to display the location name =VLOOKUP(A1,Sheet4!$A$1:$B$100,2,false) where your "hidden" sheet is Sheet4 and the data is in A1:B100. Just make sure that column A is sorted in ascending order. "Pheasant PluckerĀ®" wrote: I have a list of customers sites where each site has a unique number. Against each of the numbers is the name of the site for example; 001 London 002 Oxford .. .. 1340 Birmingham 1341 Manchester etc. etc. These are listed in 2 columns on a hidden worksheet in a multi-page spreadsheet. What I would like to do is when the site number is input say in cell A1 on a different worksheet I need to automagically insert the site name that matches that particular site number. So in my example if I input 002 in cell A1 then Oxford appears in B1 I am guessing I need to use VLOOKUP or something but can some kind soul point me in the right direction please? -- Thanks & regards, -pp- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP?
That's *exactly* the point!
When an exact match is called for *NO* sorting is necessary! Try it ... you'll like it.<bg -- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "Barb Reinhardt" wrote in message ... The false refers to an "exact" match or not. See comments from help for VLOOKUP for the Range_lookup portion of the function. Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. "Ragdyer" wrote: Why do you say that Column A *must* be sorted in ascending order? You are using "false" as the 4th argument, aren't you? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Barb Reinhardt" wrote in message ... Let's say you are entering your data in cell A1 and you want B1 to display the location name =VLOOKUP(A1,Sheet4!$A$1:$B$100,2,false) where your "hidden" sheet is Sheet4 and the data is in A1:B100. Just make sure that column A is sorted in ascending order. "Pheasant Plucker®" wrote: I have a list of customers sites where each site has a unique number. Against each of the numbers is the name of the site for example; 001 London 002 Oxford .. .. 1340 Birmingham 1341 Manchester etc. etc. These are listed in 2 columns on a hidden worksheet in a multi-page spreadsheet. What I would like to do is when the site number is input say in cell A1 on a different worksheet I need to automagically insert the site name that matches that particular site number. So in my example if I input 002 in cell A1 then Oxford appears in B1 I am guessing I need to use VLOOKUP or something but can some kind soul point me in the right direction please? -- Thanks & regards, -pp- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP?
Perfect Barb,
Thanks very much for that... Seems like we both have learnt something ;^) One last point if I may - when I enter the formula I get the text #N/A displayed in the cell. How do I hide this or prevent it from showing so the spreadsheet looks 'clean' please? Thanks & kind regards, -=Glyn=- "Barb Reinhardt" wrote in message ... Let's say you are entering your data in cell A1 and you want B1 to display the location name =VLOOKUP(A1,Sheet4!$A$1:$B$100,2,false) where your "hidden" sheet is Sheet4 and the data is in A1:B100. Just make sure that column A is sorted in ascending order. "Pheasant Plucker®" wrote: I have a list of customers sites where each site has a unique number. Against each of the numbers is the name of the site for example; 001 London 002 Oxford .. .. 1340 Birmingham 1341 Manchester etc. etc. These are listed in 2 columns on a hidden worksheet in a multi-page spreadsheet. What I would like to do is when the site number is input say in cell A1 on a different worksheet I need to automagically insert the site name that matches that particular site number. So in my example if I input 002 in cell A1 then Oxford appears in B1 I am guessing I need to use VLOOKUP or something but can some kind soul point me in the right direction please? -- Thanks & regards, -pp- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP?
Add an IF() function containing an error trap to display a zero length
string ( "" ). =IF(ISNA(VLOOKUP(A1,Sheet4!$A$1:$B$100,2,0)),"",VL OOKUP(A1,Sheet4!$A$1:$B$100,2,0)) BTW, 0 and False are equal, as far as the 4th argument are concerned. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Pheasant Plucker®" wrote in message ... Perfect Barb, Thanks very much for that... Seems like we both have learnt something ;^) One last point if I may - when I enter the formula I get the text #N/A displayed in the cell. How do I hide this or prevent it from showing so the spreadsheet looks 'clean' please? Thanks & kind regards, -=Glyn=- "Barb Reinhardt" wrote in message ... Let's say you are entering your data in cell A1 and you want B1 to display the location name =VLOOKUP(A1,Sheet4!$A$1:$B$100,2,false) where your "hidden" sheet is Sheet4 and the data is in A1:B100. Just make sure that column A is sorted in ascending order. "Pheasant Plucker®" wrote: I have a list of customers sites where each site has a unique number. Against each of the numbers is the name of the site for example; 001 London 002 Oxford .. .. 1340 Birmingham 1341 Manchester etc. etc. These are listed in 2 columns on a hidden worksheet in a multi-page spreadsheet. What I would like to do is when the site number is input say in cell A1 on a different worksheet I need to automagically insert the site name that matches that particular site number. So in my example if I input 002 in cell A1 then Oxford appears in B1 I am guessing I need to use VLOOKUP or something but can some kind soul point me in the right direction please? -- Thanks & regards, -pp- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP?
Thanks for the quick reply RagDyeR,
How on earth are us ordinary mortals expected to work this out? Thank God for this newsgroup...;^) It does exactly what it says on the tin but then you knew that anyway didn't you? :-) Kind regards, -=Glyn=- "RagDyeR" wrote in message ... Add an IF() function containing an error trap to display a zero length string ( "" ). =IF(ISNA(VLOOKUP(A1,Sheet4!$A$1:$B$100,2,0)),"",VL OOKUP(A1,Sheet4!$A$1:$B$10 0,2,0)) BTW, 0 and False are equal, as far as the 4th argument are concerned. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Pheasant Plucker®" wrote in message ... Perfect Barb, Thanks very much for that... Seems like we both have learnt something ;^) One last point if I may - when I enter the formula I get the text #N/A displayed in the cell. How do I hide this or prevent it from showing so the spreadsheet looks 'clean' please? Thanks & kind regards, -=Glyn=- "Barb Reinhardt" wrote in message ... Let's say you are entering your data in cell A1 and you want B1 to display the location name =VLOOKUP(A1,Sheet4!$A$1:$B$100,2,false) where your "hidden" sheet is Sheet4 and the data is in A1:B100. Just make sure that column A is sorted in ascending order. "Pheasant Plucker®" wrote: I have a list of customers sites where each site has a unique number. Against each of the numbers is the name of the site for example; 001 London 002 Oxford .. .. 1340 Birmingham 1341 Manchester etc. etc. These are listed in 2 columns on a hidden worksheet in a multi-page spreadsheet. What I would like to do is when the site number is input say in cell A1 on a different worksheet I need to automagically insert the site name that matches that particular site number. So in my example if I input 002 in cell A1 then Oxford appears in B1 I am guessing I need to use VLOOKUP or something but can some kind soul point me in the right direction please? -- Thanks & regards, -pp- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(a1="x",(vlookup 18K rows,2,false),(vlookup 18K,3,false)) RAM? | Excel Worksheet Functions | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |