#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 93
Default Vlookup?

have xl worksheet with 30000 rows w/25 cols all containing static demographic
data. i want to add another col to add the county seat to each record. i
have another worksheet that contains the 6000 counties and county seats. i
want to use a lookup to fill in the county seat on first w/s.

there are duplicate county seat names in several states, e.g. Adams appears
in 7 instances, i want to ensure the Texas seat goes with the Texas counties
on the first w/s.

if the states are two letters you could use vlookup(statename,
country,3)...could you use concatenation say for TX to add LU on the end such
that it becomes TXLU that is then substituted into the vlookup function?
thanks
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Vlookup?


Is this to ask if you can use vlookup with multiple criteria? Sorry if
I misunderstood you. If this is the case, yes. Just use "&" in the
first argument of the function. If you're asking whether you can
concatenate TX and LU. Then, yes. You'd also use the "&" and make
sure you use double quotes with any string thereafter.

--
Regards,

Isaac Maycotte


--
I Maycotte
------------------------------------------------------------------------
I Maycotte's Profile: http://www.excelforum.com/member.php...o&userid=35604
View this thread: http://www.excelforum.com/showthread...hreadid=562590

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 93
Default Vlookup?

probably didn't ask the right question. Let's say col 6 is the ST code, IL
and TX for example..i want the lookup to go to the correct state (named
range?) then match the county name and return the county seat.

my thought was us the state code TX as a named range on the table. say you
had this in col 6
TX then vlookup(countyname, TX namedrange,3)
IL then (countyname, IL namedrange,3)
that's one way if it possible
"I Maycotte" wrote:


Is this to ask if you can use vlookup with multiple criteria? Sorry if
I misunderstood you. If this is the case, yes. Just use "&" in the
first argument of the function. If you're asking whether you can
concatenate TX and LU. Then, yes. You'd also use the "&" and make
sure you use double quotes with any string thereafter.

--
Regards,

Isaac Maycotte


--
I Maycotte
------------------------------------------------------------------------
I Maycotte's Profile: http://www.excelforum.com/member.php...o&userid=35604
View this thread: http://www.excelforum.com/showthread...hreadid=562590


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default Vlookup?

On the 2nd sheet where there are 6000 records, insert a column to the very
left to concatenate both the state and county info. In the first sheet with
30000 record, there's no need to create an extra column to concatenate the
info. In the VLOOKUP formula, can simply use StateCell&CountyCell for the
first argument. Also feel free to add a ISNA formula to check for error.

-Simon

"reno" wrote:

have xl worksheet with 30000 rows w/25 cols all containing static demographic
data. i want to add another col to add the county seat to each record. i
have another worksheet that contains the 6000 counties and county seats. i
want to use a lookup to fill in the county seat on first w/s.

there are duplicate county seat names in several states, e.g. Adams appears
in 7 instances, i want to ensure the Texas seat goes with the Texas counties
on the first w/s.

if the states are two letters you could use vlookup(statename,
country,3)...could you use concatenation say for TX to add LU on the end such
that it becomes TXLU that is then substituted into the vlookup function?
thanks

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 93
Default Vlookup?

sounds like what i want to do...i'll give it a try
thx

"SimonCC" wrote:

On the 2nd sheet where there are 6000 records, insert a column to the very
left to concatenate both the state and county info. In the first sheet with
30000 record, there's no need to create an extra column to concatenate the
info. In the VLOOKUP formula, can simply use StateCell&CountyCell for the
first argument. Also feel free to add a ISNA formula to check for error.

-Simon

"reno" wrote:

have xl worksheet with 30000 rows w/25 cols all containing static demographic
data. i want to add another col to add the county seat to each record. i
have another worksheet that contains the 6000 counties and county seats. i
want to use a lookup to fill in the county seat on first w/s.

there are duplicate county seat names in several states, e.g. Adams appears
in 7 instances, i want to ensure the Texas seat goes with the Texas counties
on the first w/s.

if the states are two letters you could use vlookup(statename,
country,3)...could you use concatenation say for TX to add LU on the end such
that it becomes TXLU that is then substituted into the vlookup function?
thanks



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 93
Default Vlookup?

I tried this and it is returning the row above the correct one. the rows a
re sorted and if you add "True" it returns the same error, "False" returns
N/A. can't understand this anomoly?

any ideas?

"SimonCC" wrote:

On the 2nd sheet where there are 6000 records, insert a column to the very
left to concatenate both the state and county info. In the first sheet with
30000 record, there's no need to create an extra column to concatenate the
info. In the VLOOKUP formula, can simply use StateCell&CountyCell for the
first argument. Also feel free to add a ISNA formula to check for error.

-Simon

"reno" wrote:

have xl worksheet with 30000 rows w/25 cols all containing static demographic
data. i want to add another col to add the county seat to each record. i
have another worksheet that contains the 6000 counties and county seats. i
want to use a lookup to fill in the county seat on first w/s.

there are duplicate county seat names in several states, e.g. Adams appears
in 7 instances, i want to ensure the Texas seat goes with the Texas counties
on the first w/s.

if the states are two letters you could use vlookup(statename,
country,3)...could you use concatenation say for TX to add LU on the end such
that it becomes TXLU that is then substituted into the vlookup function?
thanks

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 79
Default Vlookup?

If you set it to False, it will only return a value when there's an exact
match. If you get N/A, that means there's no exact match. When it's set to
True, it will return the closest value when there's no exact match. You
should take a look at the help file regarding VLOOKUP, which will explain in
more detail.

As far as why it's not matching up exactly, maybe you have an extra space
somewhere in the data where it's not supposed to?

-Simon

"reno" wrote:

I tried this and it is returning the row above the correct one. the rows a
re sorted and if you add "True" it returns the same error, "False" returns
N/A. can't understand this anomoly?

any ideas?

"SimonCC" wrote:

On the 2nd sheet where there are 6000 records, insert a column to the very
left to concatenate both the state and county info. In the first sheet with
30000 record, there's no need to create an extra column to concatenate the
info. In the VLOOKUP formula, can simply use StateCell&CountyCell for the
first argument. Also feel free to add a ISNA formula to check for error.

-Simon

"reno" wrote:

have xl worksheet with 30000 rows w/25 cols all containing static demographic
data. i want to add another col to add the county seat to each record. i
have another worksheet that contains the 6000 counties and county seats. i
want to use a lookup to fill in the county seat on first w/s.

there are duplicate county seat names in several states, e.g. Adams appears
in 7 instances, i want to ensure the Texas seat goes with the Texas counties
on the first w/s.

if the states are two letters you could use vlookup(statename,
country,3)...could you use concatenation say for TX to add LU on the end such
that it becomes TXLU that is then substituted into the vlookup function?
thanks

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 93
Default Vlookup?

Using an add-in from C Pearson, I found a trailing character, did a
find/replace and all's well. Thx

"SimonCC" wrote:

If you set it to False, it will only return a value when there's an exact
match. If you get N/A, that means there's no exact match. When it's set to
True, it will return the closest value when there's no exact match. You
should take a look at the help file regarding VLOOKUP, which will explain in
more detail.

As far as why it's not matching up exactly, maybe you have an extra space
somewhere in the data where it's not supposed to?

-Simon

"reno" wrote:

I tried this and it is returning the row above the correct one. the rows a
re sorted and if you add "True" it returns the same error, "False" returns
N/A. can't understand this anomoly?

any ideas?

"SimonCC" wrote:

On the 2nd sheet where there are 6000 records, insert a column to the very
left to concatenate both the state and county info. In the first sheet with
30000 record, there's no need to create an extra column to concatenate the
info. In the VLOOKUP formula, can simply use StateCell&CountyCell for the
first argument. Also feel free to add a ISNA formula to check for error.

-Simon

"reno" wrote:

have xl worksheet with 30000 rows w/25 cols all containing static demographic
data. i want to add another col to add the county seat to each record. i
have another worksheet that contains the 6000 counties and county seats. i
want to use a lookup to fill in the county seat on first w/s.

there are duplicate county seat names in several states, e.g. Adams appears
in 7 instances, i want to ensure the Texas seat goes with the Texas counties
on the first w/s.

if the states are two letters you could use vlookup(statename,
country,3)...could you use concatenation say for TX to add LU on the end such
that it becomes TXLU that is then substituted into the vlookup function?
thanks

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VLookup a Vlookup adamb2000 Excel Worksheet Functions 4 June 28th 06 10:54 PM
VLOOKUP Problem Ian Excel Discussion (Misc queries) 3 April 6th 06 06:47 PM
VLOOKUP Limitations chris_manning Excel Worksheet Functions 2 August 9th 05 06:23 PM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 11:43 AM


All times are GMT +1. The time now is 08:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"