problems with VLOOKUPS?

Hi:

I have recently seen two problems with VLOOKUPS. I have characterized these
as "hiccups". Let me explain.

Yesterday, a VLOOKUP for just one cell just flat out did not work. And, the
problem was just for one cell. In order to have the results that normally
show, I had to copy and paste "criteria" from one cell to another. (And,
typing in the criteria would not work--I had to copy and paste.) Again, this
was just one cell.

In another instance, there was a string of 8 cells within a group of 35
cells that did not get updated properly by the VLOOKUP. These 8 cells were
right in the same group within these 35.

It's hard to explain. But, it's just as if there is a flaw in the use of
VLOOKUPS.

I know. Maybe it was a mistake that I made in the programming. But, I and
several others looked over the programming and did not see any issues.

Has anyone out there noticed any similar problems with VLOOKUPS? To be
honest, that really worries me. I have to use this programming in my wor,k
and I am now worried that such results as seen above could hurt my
creedibility.

Thanks!

childofthe1980s

My standard answer:

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


"childothe1980s" <[email protected]> wrote in message news:[email protected]...
| Hi:
|
| I have recently seen two problems with VLOOKUPS. I have characterized these
| as "hiccups". Let me explain.
|
| Yesterday, a VLOOKUP for just one cell just flat out did not work. And, the
| problem was just for one cell. In order to have the results that normally
| show, I had to copy and paste "criteria" from one cell to another. (And,
| typing in the criteria would not work--I had to copy and paste.) Again, this
| was just one cell.
|
| In another instance, there was a string of 8 cells within a group of 35
| cells that did not get updated properly by the VLOOKUP. These 8 cells were
| right in the same group within these 35.
|
| It's hard to explain. But, it's just as if there is a flaw in the use of
| VLOOKUPS.
|
| I know. Maybe it was a mistake that I made in the programming. But, I and
| several others looked over the programming and did not see any issues.
|
| Has anyone out there noticed any similar problems with VLOOKUPS? To be
| honest, that really worries me. I have to use this programming in my wor,k
| and I am now worried that such results as seen above could hurt my
| creedibility.
|
| Thanks!
|
| childofthe1980s