vlookup
 

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

vlookup
 

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)


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568361

vlookup
 

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

"Joe" wrote:

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

vlookup
 

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.

"SteveG" wrote:


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)


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568361

vlookup
 

Rich Stanek Wrote:
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


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.


--
mr_ben
------------------------------------------------------------------------
mr_ben's Profile: http://www.excelforum.com/member.php...o&userid=37106
View this thread: http://www.excelforum.com/showthread...hreadid=568361

vlookup
 

Joe,

VLOOKUP returns the N/A error when the value it is looking for is not
found. Make sure that the data format in Current Rankings and B2 are
the same. Make sure that the data in Current Rankings does not have
trailing spaces. On one match that you are certain of, try deleting
the name or data and typing it in making sure you don't hit the space
bar at the end. If your lookup works after that, you can use the TRIM
function to remove the trailing spaces from the rest. Insert a row to
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 and
Paste SpecialValues over the old values. You can then delete the row
you inserted to trim. Once your get your formula working, to avoid the
N/A error in a VLOOKUP use:

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

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

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568361

vlookup
 

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)?

"SteveG" wrote:


Joe,

VLOOKUP returns the N/A error when the value it is looking for is not
found. Make sure that the data format in Current Rankings and B2 are
the same. Make sure that the data in Current Rankings does not have
trailing spaces. On one match that you are certain of, try deleting
the name or data and typing it in making sure you don't hit the space
bar at the end. If your lookup works after that, you can use the TRIM
function to remove the trailing spaces from the rest. Insert a row to
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 and
Paste SpecialValues over the old values. You can then delete the row
you inserted to trim. Once your get your formula working, to avoid the
N/A error in a VLOOKUP use:

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

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

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568361

vlookup
 

Joe,

Go to the tab June 2006 in the Current Rankings workbook. Insert an
entire column between columns H and I (this is only temporary). Do
this by clicking on the column header of column I. Right mouse click
and select insert. This will make the I column of data shift to J
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 can
do this by clicking on the column header) and copy it. Then select
column H by clicking on the column header. Right mouse click on the
highlighted column H and select Paste Special from the options. When
the dialog box comes up, select "Values" from the list of Paste
options. Click OK. You can then delete column I which will shift the
data back to it's original layout. This replaces your old data list
with an identical list but removing any trailing spaces.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568361

vlookup
 

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

"SteveG" wrote:


Joe,

Go to the tab June 2006 in the Current Rankings workbook. Insert an
entire column between columns H and I (this is only temporary). Do
this by clicking on the column header of column I. Right mouse click
and select insert. This will make the I column of data shift to J
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 can
do this by clicking on the column header) and copy it. Then select
column H by clicking on the column header. Right mouse click on the
highlighted column H and select Paste Special from the options. When
the dialog box comes up, select "Values" from the list of Paste
options. Click OK. You can then delete column I which will shift the
data back to it's original layout. This replaces your old data list
with an identical list but removing any trailing spaces.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=568361

vlookup
 

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


"Joe" wrote in message
...
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

"SteveG" wrote:


Joe,

Go to the tab June 2006 in the Current Rankings workbook. Insert an
entire column between columns H and I (this is only temporary). Do
this by clicking on the column header of column I. Right mouse click
and select insert. This will make the I column of data shift to J
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 can
do this by clicking on the column header) and copy it. Then select
column H by clicking on the column header. Right mouse click on the
highlighted column H and select Paste Special from the options. When
the dialog box comes up, select "Values" from the list of Paste
options. Click OK. You can then delete column I which will shift the
data back to it's original layout. This replaces your old data list
with an identical list but removing any trailing spaces.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
View this thread:
http://www.excelforum.com/showthread...hreadid=568361