software

I'm a novice at this function.

We send money to the department of revenue for each state. Each state has
an assigned quot;accountquot; number. My spreadsheet has a column in which a state
abbreviation should be typed. If I enter the state abbreviation quot;MOquot;, I
would like to have the corresponding quot;accountquot; 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
lt;Insertgt;lt;Namegt;lt;Definegt;
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:

www.contextures.com/xlDataVal01.html

Does that help?

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

XL2002, WinXP-Proquot;blasds78quot; wrote:

gt; I'm a novice at this function.
gt;
gt; We send money to the department of revenue for each state. Each state has
gt; an assigned quot;accountquot; number. My spreadsheet has a column in which a state
gt; abbreviation should be typed. If I enter the state abbreviation quot;MOquot;, I
gt; would like to have the corresponding quot;accountquot; number appear in the adjacent
gt; cell.
gt;
gt; Is this the best function to use? And, if so, how do I get it to work? I
gt; 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 quot;#N/Aquot; and just have a blank cell?

Thanks,
Doug

quot;Ron Coderrequot; wrote:

gt; Yes, it seems like VLOOKUP will do what you want.
gt;
gt; Try this:
gt;
gt; On a separate sheet, create a 2-column list of state abbreviations and TaxIDs
gt; Since it's good practice to use range names in these situations:
gt; select the 2-col list
gt; lt;Insertgt;lt;Namegt;lt;Definegt;
gt; Names in workbook: LU_TaxID
gt; Refers to: (your already selected list range)
gt; Click the [OK] button
gt;
gt; Next, on your input sheet...
gt; Assuming you input a State Abbreviation in A1
gt; B1: =VLOOKUP(A1,LU_TaxID,2,0)
gt;
gt; That formula will search the first column of the LU_TaxID range for the
gt; state abbreviation in A1 and return the corresponding TaxID.
gt;
gt; One other tip. You might want to consider using Data Validation for the
gt; State Abbreviations to ensure that only valid abbreviations are used.
gt; See Debra Dalgleish's Contextures website for instructions:
gt;
gt; www.contextures.com/xlDataVal01.html
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;blasds78quot; wrote:
gt;
gt; gt; I'm a novice at this function.
gt; gt;
gt; gt; We send money to the department of revenue for each state. Each state has
gt; gt; an assigned quot;accountquot; number. My spreadsheet has a column in which a state
gt; gt; abbreviation should be typed. If I enter the state abbreviation quot;MOquot;, I
gt; gt; would like to have the corresponding quot;accountquot; number appear in the adjacent
gt; gt; cell.
gt; gt;
gt; gt; Is this the best function to use? And, if so, how do I get it to work? I
gt; gt; keep getting error messages with this.

First, a technical point: a formula will NEVER leave a cell blank. quot;Blankquot;
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)),quot;quot;,VLOOKUP(A1,L U_TaxID,2,0))

That formula makes the cell appear blank.

Does that help?

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

XL2002, WinXP-Proquot;blasds78quot; wrote:

gt; Thanks, Ron! That helped.
gt;
gt; I have one last thing I'd like to change. If I input nothing into a cell,
gt; how do I get rid of the quot;#N/Aquot; and just have a blank cell?
gt;
gt; Thanks,
gt; Doug
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Yes, it seems like VLOOKUP will do what you want.
gt; gt;
gt; gt; Try this:
gt; gt;
gt; gt; On a separate sheet, create a 2-column list of state abbreviations and TaxIDs
gt; gt; Since it's good practice to use range names in these situations:
gt; gt; select the 2-col list
gt; gt; lt;Insertgt;lt;Namegt;lt;Definegt;
gt; gt; Names in workbook: LU_TaxID
gt; gt; Refers to: (your already selected list range)
gt; gt; Click the [OK] button
gt; gt;
gt; gt; Next, on your input sheet...
gt; gt; Assuming you input a State Abbreviation in A1
gt; gt; B1: =VLOOKUP(A1,LU_TaxID,2,0)
gt; gt;
gt; gt; That formula will search the first column of the LU_TaxID range for the
gt; gt; state abbreviation in A1 and return the corresponding TaxID.
gt; gt;
gt; gt; One other tip. You might want to consider using Data Validation for the
gt; gt; State Abbreviations to ensure that only valid abbreviations are used.
gt; gt; See Debra Dalgleish's Contextures website for instructions:
gt; gt;
gt; gt; www.contextures.com/xlDataVal01.html
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;blasds78quot; wrote:
gt; gt;
gt; gt; gt; I'm a novice at this function.
gt; gt; gt;
gt; gt; gt; We send money to the department of revenue for each state. Each state has
gt; gt; gt; an assigned quot;accountquot; number. My spreadsheet has a column in which a state
gt; gt; gt; abbreviation should be typed. If I enter the state abbreviation quot;MOquot;, I
gt; gt; gt; would like to have the corresponding quot;accountquot; number appear in the adjacent
gt; gt; gt; cell.
gt; gt; gt;
gt; gt; gt; Is this the best function to use? And, if so, how do I get it to work? I
gt; gt; gt; 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

quot;Ron Coderrequot; wrote:

gt; First, a technical point: a formula will NEVER leave a cell blank. quot;Blankquot;
gt; means contains nothing. A formula is not nothing.
gt;
gt; Second...continuing with my posted example:
gt;
gt; Try this standard approach to avoiding displayed errors:
gt; B1: =IF(ISNA(VLOOKUP(A1,LU_TaxID,2,0)),quot;quot;,VLOOKUP(A1,L U_TaxID,2,0))
gt;
gt; That formula makes the cell appear blank.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;blasds78quot; wrote:
gt;
gt; gt; Thanks, Ron! That helped.
gt; gt;
gt; gt; I have one last thing I'd like to change. If I input nothing into a cell,
gt; gt; how do I get rid of the quot;#N/Aquot; and just have a blank cell?
gt; gt;
gt; gt; Thanks,
gt; gt; Doug
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Yes, it seems like VLOOKUP will do what you want.
gt; gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; On a separate sheet, create a 2-column list of state abbreviations and TaxIDs
gt; gt; gt; Since it's good practice to use range names in these situations:
gt; gt; gt; select the 2-col list
gt; gt; gt; lt;Insertgt;lt;Namegt;lt;Definegt;
gt; gt; gt; Names in workbook: LU_TaxID
gt; gt; gt; Refers to: (your already selected list range)
gt; gt; gt; Click the [OK] button
gt; gt; gt;
gt; gt; gt; Next, on your input sheet...
gt; gt; gt; Assuming you input a State Abbreviation in A1
gt; gt; gt; B1: =VLOOKUP(A1,LU_TaxID,2,0)
gt; gt; gt;
gt; gt; gt; That formula will search the first column of the LU_TaxID range for the
gt; gt; gt; state abbreviation in A1 and return the corresponding TaxID.
gt; gt; gt;
gt; gt; gt; One other tip. You might want to consider using Data Validation for the
gt; gt; gt; State Abbreviations to ensure that only valid abbreviations are used.
gt; gt; gt; See Debra Dalgleish's Contextures website for instructions:
gt; gt; gt;
gt; gt; gt; www.contextures.com/xlDataVal01.html
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;blasds78quot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I'm a novice at this function.
gt; gt; gt; gt;
gt; gt; gt; gt; We send money to the department of revenue for each state. Each state has
gt; gt; gt; gt; an assigned quot;accountquot; number. My spreadsheet has a column in which a state
gt; gt; gt; gt; abbreviation should be typed. If I enter the state abbreviation quot;MOquot;, I
gt; gt; gt; gt; would like to have the corresponding quot;accountquot; number appear in the adjacent
gt; gt; gt; gt; cell.
gt; gt; gt; gt;
gt; gt; gt; gt; Is this the best function to use? And, if so, how do I get it to work? I
gt; gt; gt; gt; keep getting error messages with this.