vlookup

J

Joe

I cannot get the vlookup funcion to work in any of my spreadsheets. Once the
argument is entered, the cell will either display the entire argument, or
#N/A with a green error tab in the upper left corner of the cell
(=VLOOKUP(B2,'[Current Ranking.xls]June 2006'!$H:$I,2,FALSE). I have
confirmed that there are multiple matches between these two columns and have
tried all of the suggestions in the HELP menu, to no avail (formating,
sorting, moving to other sheets and/or cells, as well as deleting and/or
clearing sheets and cells). After having spoken with several other regular
users of this function (and downloading any new updates for my Excel 2003
from Microsoft), an answer to this issue has not arisen. Your thoughts,
please. . .
 
S

SteveG

Joe,

Remove the "(" from the beginning of your formula.

(=VLOOKUP(B2,'[Current Ranking.xls]June 2006'!$H:$I,2,FALSE)

should be

=VLOOKUP(B2,'[Current Ranking.xls]June 2006'!$H:$I,2,FALSE)
 
R

Rich Stanek

When I have ran into this in the past it is normally because the field you
are typing you vlookup into is formatted as text. Try writting a simple
formula in its place like =1+1. If it still displays the argument then this
is your problem.

Fix. Change the formatting to general, then retype the 1+1, if you get the
"2", then you are good to go, and then can rewrite your vlookup formula.

Hope this helps
 
J

Joe

Thanx, Steve.

Still no luck. I also tried Steve's advice: 1+1=2 and yet I still return a
#N/A with an error message all the way down the sheet.
 
M

mr_ben

Rich said:
When I have ran into this in the past it is normally because the field
you
are typing you vlookup into is formatted as text. Try writting a
simple
formula in its place like =1+1. If it still displays the argument then
this
is your problem.

Fix. Change the formatting to general, then retype the 1+1, if you get
the
"2", then you are good to go, and then can rewrite your vlookup
formula.

Hope this helps
Click to expand...

An easier way is to use the text to columns tool.

Highlight the whole column then select the tool and the press finish.
That way the whole column will be done.
 
S

SteveG

Joe,

VLOOKUP returns the N/A error when the value it is looking for is no
found. Make sure that the data format in Current Rankings and B2 ar
the same. Make sure that the data in Current Rankings does not hav
trailing spaces. On one match that you are certain of, try deletin
the name or data and typing it in making sure you don't hit the spac
bar at the end. If your lookup works after that, you can use the TRI
function to remove the trailing spaces from the rest. Insert a row t
the right. Select the entire row and then enter =TRIM(A:A) in row 1.
Commit this with Ctrl-Shift-Enter, not just enter. Then copy that an
Paste Special>Values over the old values. You can then delete the ro
you inserted to trim. Once your get your formula working, to avoid th
N/A error in a VLOOKUP use:

=IF(ISNA(VLOOKUP(B2,'[Current Ranking.xls]Jun
2006'!$H:$I,2,FALSE)),"",VLOOKUP(B2,'[Current Ranking.xls]Jun
2006'!$H:$I,2,FALSE))

This will return a blank cell if the value is not found.

HTH

Stev
 
J

Joe

Thanx, Steve.

I followed you instructions with the following results:
When I replaced a duplicate number in both columns, I got the desired results.
However, after following your TRIM instructions, I got no results.
I have to admit that I was not 100% clear on your TRIM instructions. Just to
be sure, would you please explain them again with more specific detail for my
application (I've been using Excel only for months and vlookup only for
days)?
 
S

SteveG

Joe,

Go to the tab June 2006 in the Current Rankings workbook. Insert a
entire column between columns H and I (this is only temporary). D
this by clicking on the column header of column I. Right mouse clic
and select insert. This will make the I column of data shift to
leaving I blank. In I1 type in
=TRIM(H1). Copy this down to the end of your data in column H.

Highlight the entire column I where you used the TRIM function (you ca
do this by clicking on the column header) and copy it. Then selec
column H by clicking on the column header. Right mouse click on th
highlighted column H and select Paste Special from the options. Whe
the dialog box comes up, select "Values" from the list of Past
options. Click OK. You can then delete column I which will shift th
data back to it's original layout. This replaces your old data lis
with an identical list but removing any trailing spaces.

HTH

Stev
 
J

Joe

OK. Thanx, Steve. That worked - finally!
I had to "TRIM" all of the columns I was working in to get this to work.
Do you know what is causing it to fail like that, and what can I do in the
future to avoid having this problem again (I am bringing in data from an
AS400 system to create my reports and analysis)? Am I importing the
information incorrectly? Whatever you can suggest will be greatly appreciated.
Thanx, again.

Joe
 
D

doctorjones_md

Joe,

When you import data (especially from another program or platform), there
will be times when you'll encounter data validation and formatting errors.
You can create macros which will clean-up/scrub your data during the import
process. You can take the steps that Steve gave you, and create a macro.

HTHs
 
Top