Posted to microsoft.public.excel.misc
|
|
VLOOKUP and repeating results
Great! it worked,
Thanks "Niek Otten" a lot once again.
"Niek Otten" wrote:
=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
"jpreman" wrote in message ...
| 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. EditCopy. Select your "numbers". EditPaste 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 he 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; InsertNameDefine.
|
| 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
|
|
| "jpreman" wrote in message ...
| | 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.
| |
| |
| |
| |
|
|
|
|