Continuing VLOOKUP Question

[JimFor]

Hi,

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

 

[JulieD]

Hi Jim

basically the VLOOKUP function will look at a range, match up the
information in the left column of the range with the lookup_value and return
a related piece of information from another column in the range. If you use
the fourth parameter of 0 then your lookup_table does not need to be sorted.

My understanding of you situation is that you have two lists (somewhere)

list 1
Column A - Account Numbers
Column B - Account Name

list 2
Column A - Account Numbers
Column B - Tax ID

if so your first VLOOKUP will have to reference List 1 and the second List 2
(so assuming list 1 is on sheet 2 & list two on sheet 3) the formula will
look something like this:

given Account Number (in sheet1 A1)
find Account Name (return to sheet 1 B1)
in B1
=VLOOKUP(A1,Sheet2!$A$2:$B$100,2,0)
(lookup the value in A1, in the list in sheet2 column A and return the
information in column B)

then to get TaxID in sheet1 C1
=VLOOKUP(A1,Sheet3!$A$2:$B$100,2,0)
(lookup the value in A1, in the list in sheet3 column A and return the
information in column B)

If this isn't what you're after or doesn't work, could you please reply with
the actual range address and structure (headings) of your lists.

Note, firstly, that VLOOKUP will return the FIRST instance of a found item
.... so if you have account number 27, three time in your list, VLOOKUP will
only return the first Account Name and Tax ID in the list. If you need more
Account Names & Tax IDs returned, i believe it can be done but it's not as
easy as using a basic VLOOKUP statement.

secondly, if when you type =VLOOKUP(A1,Sheet3!$A$2:$B$100,2,0) you see that
and not the answer then it is probably because your cell is formatted to
TEXT ... select an unused cell somewhere on the worksheet, copy it, click on
the cell that is misbehaving choose edit / paste special ADD and click OK.

Let us know how you go.

Cheers
julieD

 

[Myrna Larson]

The formulas you were given should work. Of course you must change the
reference to the table. You say you have 12000 accounts, so the range should
include the 12000 rows. With the 4th argument as 0, the account list does NOT
have to be sorted.

If you are saying that you see the formula itself rather than the result of
the lookup, the problem is that the cell has been formatted as text. Clear the
formatting (Edit/Clear/Formats) and reenter the formula.

 

[JimFor]

Thanks for the replies. I will let you know how it turns out. I'm pretty
close. Just did a test at home with 5 account numbers in no order and with one
not being used. It worked OK. So the system should work. Perhaps the problem
lies with the format of the cell? Or the range number? I will try again
tomorrow. Hard to try different things at work because of the volume of work.
I am forced to try a bit, go back to work and make changes at home.
Thanks again for all your help.

 

[JimFor]

Well, it ALMOST worked. The function does give me the account names and tax
ids. However, it does not always give me all of them. Say I have 15 repeating
account numbers corresponding to 15 orders of something. I need the VLOOKUP
function to give me the account name and tax id for that account 15 times.
Sometimes it does. But sometimes it gives me a smaller number of account data,
say ten or seven. A "N/A" appears in a cell where an account name or tax id
should appear. And data can be missing anywhere in the spaces where it should
appear. Not just at the first part or last part of a column. Sometimes you
will get 12 "N/As" in a row, three names, and 6 more "NAs." Any suggestions
how I can get all the data? Perhaps the format of the cells which provide the
data to my second sheet should be changed? Or??? If an account name (and
number) is on the source list, and it appears twice next to list of account
numbers on the "target" sheet, the process should just repeat itself. It does
not. I have thousands of transactions and have had a fair number of "N/As."
Would like to pass this on to others involved but I can't until it works
better. So close...
Thanks

 

[JulieD]

Hi Jim

basically, as i said in my original post, VLOOKUP will only return the
information for the first occurance of a value in the list. However, Frank
Kabel has in the past posted an array formula which can be used to return
multiple occurances of a value - the following formula is based on Frank's
previous posts.

Assume your lookup table is columns A & B of sheet 2
the value you are looking for is in cell A1 of sheet 1
in Sheet1!B1 type

=INDEX(Sheet2!$B$1:$B$4,SMALL(IF(Sheet2!$A$1:$A$4=Sheet1!A1,ROW(Sheet2!$A$1:$A$4)),ROW(1:1)))

(all on one line)
and press control & shift & enter to enter the formula in the cell
now get the fill handle and fill down as far as the number of possible
results. Excel will return the related information for all the matches
until it finds no more, and then will return #NA.

Hope this helps
Cheers
JulieD

 

[JimFor]

Thanks, I'll try it. But the VLOOKUP function did appear to provide multiples
of the same value. But it was not consistent.

 

[JulieD]

Hi Jim

if you've managed to achieve this with a VLOOKUP i'ld be interested in
seeing the exact formula you've used ... as this would be a very useful
"feature" ... however, i'm guessing something like this has happened:

Row Formula
1 =VLOOKUP("cat",A1:A100,2,0)
2 =VLOOKUP("cat",A2:A101,2,0)

etc

Cheers
JulieD

 

[JimFor]

Here is what I mean. Suppose I have on Sheet 1 a column of account numbers,
names and tax ids. On sheet 2 I have a column of account numbers. I need
names and tax ids and, using the formula, I get those from sheet 2. That
works OK. But the problem arises (sometimes) if I have an account number
appearing more than once in column A, sheet 2. I type in the formula, hit
"Enter" and start to drag the box surrounding the "target" cell. If I have 10
of the same account numbers in a row, say 25366, and that is the account number
for "John Smith" I should have 10 "John Smiths" appear in sheet 2. Sometimes I
do. Sometimes I do not. If not, I get an "N/A" appearing in some cells. I'm
sure the formula works. But not all of the time. I want to find out why I get
"N/A" when I should get "John Smith."

 

[Myrna Larson]

My suspicion is that you have not used absolute references to refer to the
table; if that's the case, after you've dragged the formula down 10 rows, the
reference changes, from, say, A1:B10 to A11:A20.

If the table on sheet1 is in A1:B35, you have to write the formula as, e.g.,

=VLOOKUP(A2,Sheet1!$A$1:$B$35,2,0)

Note the dollar signs.

 

[JimFor]

I did use the dollar signs. I have tried the formula several times using my
own test data and it always works. But it does not always work at work. Maybe
I'm wrong, but if it repeats a name once, it should work all of the time. If I
have the name "Joe" associated with the account number 25 on sheet one, and I
need "Joe" to appear wherever 25 appears in sheet 2 and if when using the
formula I get "Joe" to appear twice on sheet 2, it should appear 20 times, 50
times,100 times, whatever. However many times the number 25 appears on sheet 2.
I am missing something. Maybe I am doing something wrong. I will recheck the
formula. Or perhaps it has something to do with the data. I don't know.
Perhaps it is the speed of the computer. I'm using a very slow computer. It
does have trouble keeping up. It is basically glorified terminal. The work
load has increased greatly over the last six months or so. Just got dropped
into this job to help out and no one really knows what is going on. I am
slowly learning. But things are certainly better than having to use the "Find"
function and cutting and pasting each account name. Thanks.

 

[JulieD]

Hi JimFor

Just using VLOOKUP (with an absolute range address) should not give you the
result for more than one occurance of a value .. did you try the method i
outlined two posts back (the revamp of Frank's formula)? how did you go
with that?

Cheers
JulieD

 

[JimFor]

JimFor

Just using VLOOKUP (with an absolute >range address) should not give you the
result for more than one occurance of a >value .. did you try the method i
outlined two posts back (the revamp of >Frank's formula)? how did you go
with that?

I will try that and post what happened.
Think I discovered the reasons for the "N/A"s in some cells. Perhaps it was
related to the computer I was using? The computer I am using is very slow.
It had a hard time checking over 30,000 account numbers. I want to hand this
project off to other people so I used anther person's computer and the formula
I was using worked just fine. No "N/A"s. No lag coming up with the names
and ids Next I will test the formula you gave me. But it may take a few days.
(I'm also looking into the possibility of writing the entire task I perform
into a VBA program and automate it. Still too much being done manually but
it's a long story...)
Thanks

 

[JulieD]

Hi Jim

i would be interested to see how you go with it down the track - hope it
works out.

Cheers
JulieD

 

[JimFor]

Took me a long time to reply. Tried your forumla got a circular reference error
notice, I believe. If you want, try both your formula and the one I used on
this setup.
This is what I tested the formulas on.

Sheet1
Col A Col B Col C
Account Account Tax ID
Number Name
2 Fred 223
2 Fred 223
22 Mary 211
3 Pete 133
11 Jane 21

Add any others you want but be sure to have some multiple Account Names/TaxIDs.

Sheet 2
Col A Col B Col C
Account
Number
2
2
22
3
11

Enter each formula into Cell B1 and see what you get. The formula I used
worked on this and a longer list. Thanks for the help you and anyone else have
given me. I am slowly making progress. Like said earlier, seems like much of
what is being done where I work should be automated and I am slowly working on
a VBA approach to get it done. I will then give it to the MIS people.