=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.
| > |
| > |
| > |
| > |
| >
| >
| >
