VLOOKUP and repeating results

G

Guest

Thanks for reading this post.

I have data in sheet 1 with about 1000 Rows and 3 columns. The columns
contain the following.
- Old Account number (with leading zero) - not arranged
serially. Even if
arranged serially the increments are varied as some accounts
had been
closed.
- New account number
- Name

Similarly in sheet 2 the following fields are there
- Old Account number (with leading zero)
- Name
- Amount

I need to extract next to each Old Account Number in sheet 2 the
corresponding New Account Number from sheet 1. How can I achieve this?

Ps.
VLOOKUP in many instances returns the same New Account Number consecutively
despite a difference in the Old Account Number.
 
N

Niek Otten

=========================================================
Vlookup gives wrong answer

Niek Otten, April 1 2006

Frequent causes:

1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function.
Check both the search arguments and the lookup table.
Formatting as numbers afterwards doesn't help.
Remedy:
Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your "numbers". Edit>Paste Special, check Multiply.

2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted.

3. There are spaces or other invisible characters in either the search arguments or the lookup table.
This often happens when you import data from other applications.
Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
Use the TRIM function to remove all spaces except single spaces between words.
Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David
McRitchie,
which can be downloaded here: http://www.mvps.org/dmcritchie/excel/join.htm#trimall

4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed in
the Paste process
and now point to the wrong range.
Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20.
When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute ones
(normally only for the table, not for the search argument, but this depends on your specific problem).
The first hit of F4 changes the default relative address to an absolute one. That is usually what you need.
Even better: use a Defined Name for the table instead of cell addresses; Insert>Name>Define.

5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name was
not adjusted accordingly.
To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary)
and insert new rows or cells between those two.
Then the definition of the range or the Defined Name will adjust automatically.
Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables.
=========================================================


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Thanks for reading this post.
|
| I have data in sheet 1 with about 1000 Rows and 3 columns. The columns
| contain the following.
| - Old Account number (with leading zero) - not arranged
| serially. Even if
| arranged serially the increments are varied as some accounts
| had been
| closed.
| - New account number
| - Name
|
| Similarly in sheet 2 the following fields are there
| - Old Account number (with leading zero)
| - Name
| - Amount
|
| I need to extract next to each Old Account Number in sheet 2 the
| corresponding New Account Number from sheet 1. How can I achieve this?
|
| Ps.
| VLOOKUP in many instances returns the same New Account Number consecutively
| despite a difference in the Old Account Number.
|
|
|
|
 
G

Guest

Thanks a lot Niek Otten for your prompt response.

Following are my comments on "Frequent causes" given by you.

1. When text is converted to number the leading zeros are ommitted.

3. There are no spaces or invisible characters.

4. Formula was not copied from elsewhere.

5. Table was not extended

However, under cause 2 I have to select TRUE as the forth argument. if I had
to change this how should I construct the formula?

Given below is a sample:-

In Sheet 2
From Sheet 1
-----------------------------------------------
-----------------------------------------------
OLD AC NO Name New AC NO
Name
--------------- ---------------------- ------------------
---------------------
01390051144 SYED JAMALUDDIN 3990000026201 SYED JAMALUDDIN
01390051152 MADHUSUDHANAN 3990000010181 MADHUSUDHANAN
01390051158 THOMAS 3990000010181 MADHUSUDHANAN
01390051169 PREMANANDAN 3990000010220 PREMANANDAN
01390051173 JOHN 3990000010246 JOHN
01390051176 SINGH 3990000010262 SINGH
01390051194 VINAYAK 3990000010262 SINGH
01390051197 MOHD 3990000010262 SINGH
01390051198 DANIEL 3990000010262 SINGH
01390051213 ISMAIL 3990000010262 SINGH
 
N

Niek Otten

=VLOOKUP(A1,Sheet1!$A$1:$C$10,2,0) for the new accountno. Change 2 to 3 for new name
Copy the formula down as far as you have data in Sheet2

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thanks a lot Niek Otten for your prompt response.
|
| Following are my comments on "Frequent causes" given by you.
|
| 1. When text is converted to number the leading zeros are ommitted.
|
| 3. There are no spaces or invisible characters.
|
| 4. Formula was not copied from elsewhere.
|
| 5. Table was not extended
|
| However, under cause 2 I have to select TRUE as the forth argument. if I had
| to change this how should I construct the formula?
|
| Given below is a sample:-
|
| In Sheet 2
| From Sheet 1
| -----------------------------------------------
| -----------------------------------------------
| OLD AC NO Name New AC NO
| Name
| --------------- ---------------------- ------------------
| ---------------------
| 01390051144 SYED JAMALUDDIN 3990000026201 SYED JAMALUDDIN
| 01390051152 MADHUSUDHANAN 3990000010181 MADHUSUDHANAN
| 01390051158 THOMAS 3990000010181 MADHUSUDHANAN
| 01390051169 PREMANANDAN 3990000010220 PREMANANDAN
| 01390051173 JOHN 3990000010246 JOHN
| 01390051176 SINGH 3990000010262 SINGH
| 01390051194 VINAYAK 3990000010262 SINGH
| 01390051197 MOHD 3990000010262 SINGH
| 01390051198 DANIEL 3990000010262 SINGH
| 01390051213 ISMAIL 3990000010262 SINGH
|
|
|
|
| "Niek Otten" wrote:
|
| > =========================================================
| > Vlookup gives wrong answer
| >
| > Niek Otten, April 1 2006
| >
| > Frequent causes:
| >
| > 1. Some cells look like numbers, but are actually text. You can check with the ISTEXT function.
| > Check both the search arguments and the lookup table.
| > Formatting as numbers afterwards doesn't help.
| > Remedy:
| > Format an empty cell as Number. Enter the number 1. Edit>Copy. Select your "numbers". Edit>Paste Special, check
Multiply.
| >
| > 2. The data is not sorted ascending and the 4th argument of the VLOOKUP is TRUE or is omitted.
| >
| > 3. There are spaces or other invisible characters in either the search arguments or the lookup table.
| > This often happens when you import data from other applications.
| > Use the LEN() function to see how many characters there really are in the cell and compare that with what you see.
| > Use the TRIM function to remove all spaces except single spaces between words.
| > Use the CLEAN function to remove all nonprintable characters. HTML characters can be removed with a macro by David
| > McRitchie,
| > which can be downloaded here: http://www.mvps.org/dmcritchie/excel/join.htm#trimall
| >
| > 4. The formula was copied from somewhere else, but the addresses of the lookup table were not absolute so have changed
in
| > the Paste process
| > and now point to the wrong range.
| > Use absolute addresses for the lookup table, like $A$1:$B$20 instead of relative addresses like A1:B20.
| > When editing or entering a formula, use the F4 key to toggle between several forms of relative addresses and absolute
ones
| > (normally only for the table, not for the search argument, but this depends on your specific problem).
| > The first hit of F4 changes the default relative address to an absolute one. That is usually what you need.
| > Even better: use a Defined Name for the table instead of cell addresses; Insert>Name>Define.
| >
| > 5. The table was extended after its initial use, but the definition of the table in the VLOOKUP or in the Defined Name
was
| > not adjusted accordingly.
| > To prevent this from happening: always use explicit bottom and top rows (with dummy error values if necessary)
| > and insert new rows or cells between those two.
| > Then the definition of the range or the Defined Name will adjust automatically.
| > Users of Excel 2003 and higher may profit from the List feature in the Data menu to maintain tables.
| > =========================================================
| >
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| >
| > | Thanks for reading this post.
| > |
| > | I have data in sheet 1 with about 1000 Rows and 3 columns. The columns
| > | contain the following.
| > | - Old Account number (with leading zero) - not arranged
| > | serially. Even if
| > | arranged serially the increments are varied as some accounts
| > | had been
| > | closed.
| > | - New account number
| > | - Name
| > |
| > | Similarly in sheet 2 the following fields are there
| > | - Old Account number (with leading zero)
| > | - Name
| > | - Amount
| > |
| > | I need to extract next to each Old Account Number in sheet 2 the
| > | corresponding New Account Number from sheet 1. How can I achieve this?
| > |
| > | Ps.
| > | VLOOKUP in many instances returns the same New Account Number consecutively
| > | despite a difference in the Old Account Number.
| > |
| > |
| > |
| > |
| >
| >
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top