vlookup-downloading any new updates

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

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

[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

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

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

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

[mr_ben]

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.

[SteveG]

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 Special>Values 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

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

"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 Special>Values 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
>
>

[SteveG]

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

[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

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

[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


"Joe" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>