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.

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.

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.

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,LU_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.

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,LU_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.