J

#### JimFor

I have been trying to use the VLOOKUP function to provide two additional

columns of data given one column. I have account numbers and need to generate

corresponding account names and tax ids. The following was suggested to me in

another post but it does not do all that I need to do (But it's a start.) :

"Subject: Re: VLOOKUP Function Question

From: BenjieLop (e-mail address removed)

Date: 10/23/2004 7:30 PM Central Daylight Time

Message-id: <[email protected]>

Assume that you have the following table in Sheet1:

Column A : Account Number

Column B : Account Name

Column C : Tax ID

In Sheet 2, assume that this is where you will enter the Account

Number.

If the "Account Number" is entered in Cell A2, then enter the following

in:

Cell B2 =VLOOKUP(A2,Sheet1!$A$2:$C$100,2,0)

and this will give you the "Account Name" that corresponds to the

"Account Number" in Cell A2. Copy this formula down until your

requirements are met.

Cell C2 =VLOOKUP(A2,Sheet1!$A$2:$C$100,3,0)

and this will give you the "Tax ID" that corresponds to the "Account

Number" in Cell A2. Again, copy this formula down until your

requirements are met."

To recap, I have a list of account numbers. Each account number has an

account name and tax id located in a different list. Assuming I am doing

things correctly, the above seems to work only when the data sets are in

sentential order and there is a one to one match between the two lists of

account numbers. My problem seems to be that I have, say, 12,000 possible

account numbers, names and tax ids. And I need to generate account name and

tax id data for, say, 500 account numbers out of those 12,000 possible

accounts. The VLOOKUP function must search out the correct account number

and tax id in the sheet where those values are located and deliver them to the

sheet with only account numbers. So far, I can only make it work if there

are exactly the same number of account numbers on each sheet and they are all

in sequential order. But it does not work with the larger data set where things

are not that neat. In fact, the VLOOKUP formula just appears in the square

where I want an account name after I press ENTER. But the formula does work

with a very small number account numbers all in one to one order. Any

suggestions? Do I have to put both lists in order? How important is the

range number? I had the limited success I had when the range number did not

correspond to actual the range used. Do I have the correct formula? Or...?

(I'm assuming that if an account number appears several times in a row, the

VLOOKUP function will provide the correct data more than once) Hope this is

clear. Still learning about this.

Thanks