Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default vlookup does not give the result in the cell even though it is

seen in the function's window, what is missing?
=vlookup(F2,AG2:Ah24,2) i tried to add at the end true or false but it does
not work. ??
f2=zip code
ag column =list of zip
ah=name of city=2
thanx for the solution.
bee

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 345
Default vlookup does not give the result in the cell even though it is

Bee, I had a similar problem and resolved it by sorting the lookup i.e in
your example, the zip codes.

"kbee" wrote:

seen in the function's window, what is missing?
=vlookup(F2,AG2:Ah24,2) i tried to add at the end true or false but it does
not work. ??
f2=zip code
ag column =list of zip
ah=name of city=2
thanx for the solution.
bee

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default vlookup does not give the result in the cell even though it is

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


"kbee" wrote in message ...
| seen in the function's window, what is missing?
| =vlookup(F2,AG2:Ah24,2) i tried to add at the end true or false but it does
| not work. ??
| f2=zip code
| ag column =list of zip
| ah=name of city=2
| thanx for the solution.
| bee
|


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to find Matches and Give a Result cheryl Excel Worksheet Functions 5 July 26th 07 09:06 PM
vlookup shows result one cell above the expected result Marie Excel Worksheet Functions 7 November 14th 06 03:52 AM
Provide a match from 2 cells to give a result from another leefarrell Excel Worksheet Functions 4 August 9th 06 01:31 PM
How do I get a cell to give a result based on the month we are in? SHBmgr Excel Worksheet Functions 3 July 17th 06 10:23 PM
entering a26-02 into a cell give strange result Anthony Excel Discussion (Misc queries) 1 January 27th 05 11:34 PM


All times are GMT +1. The time now is 01:03 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"