using vlookup
 

I'm a novice at this function.

We send money to the department of revenue for each state. Each state has
an assigned "account" number. My spreadsheet has a column in which a state
abbreviation should be typed. If I enter the state abbreviation "MO", I
would like to have the corresponding "account" number appear in the adjacent
cell.

Is this the best function to use? And, if so, how do I get it to work? I
keep getting error messages with this.

using vlookup
 

Yes, it seems like VLOOKUP will do what you want.

Try this:

On a separate sheet, create a 2-column list of state abbreviations and TaxIDs
Since it's good practice to use range names in these situations:
select the 2-col list
<Insert<Name<Define
Names in workbook: LU_TaxID
Refers to: (your already selected list range)
Click the [OK] button

Next, on your input sheet...
Assuming you input a State Abbreviation in A1
B1: =VLOOKUP(A1,LU_TaxID,2,0)

That formula will search the first column of the LU_TaxID range for the
state abbreviation in A1 and return the corresponding TaxID.

One other tip. You might want to consider using Data Validation for the
State Abbreviations to ensure that only valid abbreviations are used.
See Debra Dalgleish's Contextures website for instructions:

http://www.contextures.com/xlDataVal01.html

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blasds78" wrote:

I'm a novice at this function.

We send money to the department of revenue for each state. Each state has
an assigned "account" number. My spreadsheet has a column in which a state
abbreviation should be typed. If I enter the state abbreviation "MO", I
would like to have the corresponding "account" number appear in the adjacent
cell.

Is this the best function to use? And, if so, how do I get it to work? I
keep getting error messages with this.

using vlookup
 

Thanks, Ron! That helped.

I have one last thing I'd like to change. If I input nothing into a cell,
how do I get rid of the "#N/A" and just have a blank cell?

Thanks,
Doug

"Ron Coderre" wrote:

Yes, it seems like VLOOKUP will do what you want.

Try this:

On a separate sheet, create a 2-column list of state abbreviations and TaxIDs
Since it's good practice to use range names in these situations:
select the 2-col list
<Insert<Name<Define
Names in workbook: LU_TaxID
Refers to: (your already selected list range)
Click the [OK] button

Next, on your input sheet...
Assuming you input a State Abbreviation in A1
B1: =VLOOKUP(A1,LU_TaxID,2,0)

That formula will search the first column of the LU_TaxID range for the
state abbreviation in A1 and return the corresponding TaxID.

One other tip. You might want to consider using Data Validation for the
State Abbreviations to ensure that only valid abbreviations are used.
See Debra Dalgleish's Contextures website for instructions:

http://www.contextures.com/xlDataVal01.html

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blasds78" wrote:

I'm a novice at this function.

We send money to the department of revenue for each state. Each state has
an assigned "account" number. My spreadsheet has a column in which a state
abbreviation should be typed. If I enter the state abbreviation "MO", I
would like to have the corresponding "account" number appear in the adjacent
cell.

Is this the best function to use? And, if so, how do I get it to work? I
keep getting error messages with this.

using vlookup
 

First, a technical point: a formula will NEVER leave a cell blank. "Blank"
means contains nothing. A formula is not nothing.

Second...continuing with my posted example:

Try this standard approach to avoiding displayed errors:
B1: =IF(ISNA(VLOOKUP(A1,LU_TaxID,2,0)),"",VLOOKUP(A1,L U_TaxID,2,0))

That formula makes the cell appear blank.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blasds78" wrote:

Thanks, Ron! That helped.

I have one last thing I'd like to change. If I input nothing into a cell,
how do I get rid of the "#N/A" and just have a blank cell?

Thanks,
Doug

"Ron Coderre" wrote:

Yes, it seems like VLOOKUP will do what you want.

Try this:

On a separate sheet, create a 2-column list of state abbreviations and TaxIDs
Since it's good practice to use range names in these situations:
select the 2-col list
<Insert<Name<Define
Names in workbook: LU_TaxID
Refers to: (your already selected list range)
Click the [OK] button

Next, on your input sheet...
Assuming you input a State Abbreviation in A1
B1: =VLOOKUP(A1,LU_TaxID,2,0)

That formula will search the first column of the LU_TaxID range for the
state abbreviation in A1 and return the corresponding TaxID.

One other tip. You might want to consider using Data Validation for the
State Abbreviations to ensure that only valid abbreviations are used.
See Debra Dalgleish's Contextures website for instructions:

http://www.contextures.com/xlDataVal01.html

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blasds78" wrote:

I'm a novice at this function.

We send money to the department of revenue for each state. Each state has
an assigned "account" number. My spreadsheet has a column in which a state
abbreviation should be typed. If I enter the state abbreviation "MO", I
would like to have the corresponding "account" number appear in the adjacent
cell.

Is this the best function to use? And, if so, how do I get it to work? I
keep getting error messages with this.

using vlookup
 

Ron,

Thanks again! I never would have figured that one out. I've not heard of
ISNA before.

Thanks also for the Data Validation suggestion. I have that in place in
other areas of the spreadsheet, and it would do weel in this use, too.

You've been a huge help! Have a good weekend!

Thanks,
Doug

"Ron Coderre" wrote:

First, a technical point: a formula will NEVER leave a cell blank. "Blank"
means contains nothing. A formula is not nothing.

Second...continuing with my posted example:

Try this standard approach to avoiding displayed errors:
B1: =IF(ISNA(VLOOKUP(A1,LU_TaxID,2,0)),"",VLOOKUP(A1,L U_TaxID,2,0))

That formula makes the cell appear blank.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blasds78" wrote:

Thanks, Ron! That helped.

I have one last thing I'd like to change. If I input nothing into a cell,
how do I get rid of the "#N/A" and just have a blank cell?

Thanks,
Doug

"Ron Coderre" wrote:

Yes, it seems like VLOOKUP will do what you want.

Try this:

On a separate sheet, create a 2-column list of state abbreviations and TaxIDs
Since it's good practice to use range names in these situations:
select the 2-col list
<Insert<Name<Define
Names in workbook: LU_TaxID
Refers to: (your already selected list range)
Click the [OK] button

Next, on your input sheet...
Assuming you input a State Abbreviation in A1
B1: =VLOOKUP(A1,LU_TaxID,2,0)

That formula will search the first column of the LU_TaxID range for the
state abbreviation in A1 and return the corresponding TaxID.

One other tip. You might want to consider using Data Validation for the
State Abbreviations to ensure that only valid abbreviations are used.
See Debra Dalgleish's Contextures website for instructions:

http://www.contextures.com/xlDataVal01.html

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"blasds78" wrote:

I'm a novice at this function.

We send money to the department of revenue for each state. Each state has
an assigned "account" number. My spreadsheet has a column in which a state
abbreviation should be typed. If I enter the state abbreviation "MO", I
would like to have the corresponding "account" number appear in the adjacent
cell.

Is this the best function to use? And, if so, how do I get it to work? I
keep getting error messages with this.