VLOOKUP returning wrong row

Are there any hidden decimals in the lookup criteria? Also, if you have alpha
numeric lookup values make sure it is sorted by Excel and not by human
sorting as this can be a problem (see http://www.auditexcel.co.za/errors.html
). If none of these maybe you can give us some of the sample data?

"jthomas" wrote:

> I have a VLOOKUP function in Excel that is returning data from the row above
> the target row. It must be finding the correct match data and column, but
> always from the row above the one it should. Using the Exact match parameter
> just returns an "#N/A" error. My table array is sorted.
>
> Any ideas?
>
> Thanks!

How would I check for hidden decimals? I don't believe there are any. I did
have Excel sort the data.

Here is my formula:
=VLOOKUP(C5,'Argus Data'!A:F,6)

Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
"0" (from blank row above). When C5 is ACN the result is 66 (from row above
for ACI).

Here is some sample data (sorry about formatting):

ticker security_name twelve_month_rating five_year_rating price_per_share target_price
AA ALCOA INC COM BUY BUY 27.91 37
AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
AAPL APPLE COMPUTER INC BUY BUY 42.75 51
ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
ABS ALBERTSONS INC HOLD HOLD 20.95
ABT ABBOTT LABS HOLD BUY 47.02
ABX BARRICK GOLD COM BUY HOLD 24.6 26
ACE ACE LTD HOLD HOLD 46.23
ACI ARCH COAL INC BUY BUY 58.72 66
ACN ACCENTURE LTD BUY BUY 25 32
ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
ADI ANALOG DEVICES INC HOLD BUY 39.06
ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53


Thanks for the help!
JT


"Adrian M" wrote:

> Are there any hidden decimals in the lookup criteria? Also, if you have alpha
> numeric lookup values make sure it is sorted by Excel and not by human
> sorting as this can be a problem (see http://www.auditexcel.co.za/errors.html
> ). If none of these maybe you can give us some of the sample data?
>
> "jthomas" wrote:
>
> > I have a VLOOKUP function in Excel that is returning data from the row above
> > the target row. It must be finding the correct match data and column, but
> > always from the row above the one it should. Using the Exact match parameter
> > just returns an "#N/A" error. My table array is sorted.
> >
> > Any ideas?
> >
> > Thanks!

Looks to me like your "C5" value might have trailing spaces, like "ADP ". If
that's the case, and you don't specify 0 or FALSE as the last parameter in
the VLOOKUP, it will return the closest match, which would be the row above
it.

I run into this problem all the time when I get sheets from other people who
have the terrible habit of adding a space to everything they type into
Excel.

Also, with VLOOKUP and HLOOKUP (versus the plain LOOKUP), I don't believe
you have to sort your data--one of the advantages of using those two
functions...

"jthomas" <[email protected]> wrote in message
news:[email protected]...
> How would I check for hidden decimals? I don't believe there are any. I
did
> have Excel sort the data.
>
> Here is my formula:
> =VLOOKUP(C5,'Argus Data'!A:F,6)
>
> Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
> "0" (from blank row above). When C5 is ACN the result is 66 (from row
above
> for ACI).
>
> Here is some sample data (sorry about formatting):
>
> ticker security_name twelve_month_rating five_year_rating price_per_share
target_price
> AA ALCOA INC COM BUY BUY 27.91 37
> AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> ABS ALBERTSONS INC HOLD HOLD 20.95
> ABT ABBOTT LABS HOLD BUY 47.02
> ABX BARRICK GOLD COM BUY HOLD 24.6 26
> ACE ACE LTD HOLD HOLD 46.23
> ACI ARCH COAL INC BUY BUY 58.72 66
> ACN ACCENTURE LTD BUY BUY 25 32
> ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> ADI ANALOG DEVICES INC HOLD BUY 39.06
> ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
>
>
> Thanks for the help!
> JT
>
>
> "Adrian M" wrote:
>
> > Are there any hidden decimals in the lookup criteria? Also, if you have
alpha
> > numeric lookup values make sure it is sorted by Excel and not by human
> > sorting as this can be a problem (see
http://www.auditexcel.co.za/errors.html
> > ). If none of these maybe you can give us some of the sample data?
> >
> > "jthomas" wrote:
> >
> > > I have a VLOOKUP function in Excel that is returning data from the row
above
> > > the target row. It must be finding the correct match data and column,
but
> > > always from the row above the one it should. Using the Exact match
parameter
> > > just returns an "#N/A" error. My table array is sorted.
> > >
> > > Any ideas?
> > >
> > > Thanks!

As a followup, if you specify "FALSE" as the last parameter in the lookup,
then your list does not need to be sorted. If you specify "TRUE", then it
must be sorted...


"jthomas" <[email protected]> wrote in message
news:[email protected]...
> How would I check for hidden decimals? I don't believe there are any. I
did
> have Excel sort the data.
>
> Here is my formula:
> =VLOOKUP(C5,'Argus Data'!A:F,6)
>
> Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
> "0" (from blank row above). When C5 is ACN the result is 66 (from row
above
> for ACI).
>
> Here is some sample data (sorry about formatting):
>
> ticker security_name twelve_month_rating five_year_rating price_per_share
target_price
> AA ALCOA INC COM BUY BUY 27.91 37
> AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> ABS ALBERTSONS INC HOLD HOLD 20.95
> ABT ABBOTT LABS HOLD BUY 47.02
> ABX BARRICK GOLD COM BUY HOLD 24.6 26
> ACE ACE LTD HOLD HOLD 46.23
> ACI ARCH COAL INC BUY BUY 58.72 66
> ACN ACCENTURE LTD BUY BUY 25 32
> ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> ADI ANALOG DEVICES INC HOLD BUY 39.06
> ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
>
>
> Thanks for the help!
> JT
>
>
> "Adrian M" wrote:
>
> > Are there any hidden decimals in the lookup criteria? Also, if you have
alpha
> > numeric lookup values make sure it is sorted by Excel and not by human
> > sorting as this can be a problem (see
http://www.auditexcel.co.za/errors.html
> > ). If none of these maybe you can give us some of the sample data?
> >
> > "jthomas" wrote:
> >
> > > I have a VLOOKUP function in Excel that is returning data from the row
above
> > > the target row. It must be finding the correct match data and column,
but
> > > always from the row above the one it should. Using the Exact match
parameter
> > > just returns an "#N/A" error. My table array is sorted.
> > >
> > > Any ideas?
> > >
> > > Thanks!

Ok, one last followup...........

It may be that either your C5 value has a trailing space, OR your data array
(Argus Data) has trailing spaces after the entries in the first column.

You could select the entire first column in Argus Data, and do a
search/replace function replacing all spaces with nothing... thus making
sure you don't have trailing spaces...

"Marc" <[email protected]> wrote in message
news:[email protected]...
> As a followup, if you specify "FALSE" as the last parameter in the lookup,
> then your list does not need to be sorted. If you specify "TRUE", then it
> must be sorted...
>
>
> "jthomas" <[email protected]> wrote in message
> news:[email protected]...
> > How would I check for hidden decimals? I don't believe there are any.
I
> did
> > have Excel sort the data.
> >
> > Here is my formula:
> > =VLOOKUP(C5,'Argus Data'!A:F,6)
> >
> > Where C5 refers to a ticker symbol. When C5 is ADP the formula result
is
> > "0" (from blank row above). When C5 is ACN the result is 66 (from row
> above
> > for ACI).
> >
> > Here is some sample data (sorry about formatting):
> >
> > ticker security_name twelve_month_rating five_year_rating
price_per_share
> target_price
> > AA ALCOA INC COM BUY BUY 27.91 37
> > AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> > AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> > AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> > ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> > ABS ALBERTSONS INC HOLD HOLD 20.95
> > ABT ABBOTT LABS HOLD BUY 47.02
> > ABX BARRICK GOLD COM BUY HOLD 24.6 26
> > ACE ACE LTD HOLD HOLD 46.23
> > ACI ARCH COAL INC BUY BUY 58.72 66
> > ACN ACCENTURE LTD BUY BUY 25 32
> > ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> > ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> > ADI ANALOG DEVICES INC HOLD BUY 39.06
> > ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
> >
> >
> > Thanks for the help!
> > JT
> >
> >
> > "Adrian M" wrote:
> >
> > > Are there any hidden decimals in the lookup criteria? Also, if you
have
> alpha
> > > numeric lookup values make sure it is sorted by Excel and not by human
> > > sorting as this can be a problem (see
> http://www.auditexcel.co.za/errors.html
> > > ). If none of these maybe you can give us some of the sample data?
> > >
> > > "jthomas" wrote:
> > >
> > > > I have a VLOOKUP function in Excel that is returning data from the
row
> above
> > > > the target row. It must be finding the correct match data and
column,
> but
> > > > always from the row above the one it should. Using the Exact match
> parameter
> > > > just returns an "#N/A" error. My table array is sorted.
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks!
>
>

Bingo!

Now I remember running into something like this before. I am downloading
the table I am querying, what a pain to have to eliminate spaces.

Thanks!
JT


"Marc" wrote:

> Ok, one last followup...........
>
> It may be that either your C5 value has a trailing space, OR your data array
> (Argus Data) has trailing spaces after the entries in the first column.
>
> You could select the entire first column in Argus Data, and do a
> search/replace function replacing all spaces with nothing... thus making
> sure you don't have trailing spaces...
>
> "Marc" <[email protected]> wrote in message
> news:[email protected]...
> > As a followup, if you specify "FALSE" as the last parameter in the lookup,
> > then your list does not need to be sorted. If you specify "TRUE", then it
> > must be sorted...
> >
> >
> > "jthomas" <[email protected]> wrote in message
> > news:[email protected]...
> > > How would I check for hidden decimals? I don't believe there are any.
> I
> > did
> > > have Excel sort the data.
> > >
> > > Here is my formula:
> > > =VLOOKUP(C5,'Argus Data'!A:F,6)
> > >
> > > Where C5 refers to a ticker symbol. When C5 is ADP the formula result
> is
> > > "0" (from blank row above). When C5 is ACN the result is 66 (from row
> > above
> > > for ACI).
> > >
> > > Here is some sample data (sorry about formatting):
> > >
> > > ticker security_name twelve_month_rating five_year_rating
> price_per_share
> > target_price
> > > AA ALCOA INC COM BUY BUY 27.91 37
> > > AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> > > AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> > > AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> > > ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> > > ABS ALBERTSONS INC HOLD HOLD 20.95
> > > ABT ABBOTT LABS HOLD BUY 47.02
> > > ABX BARRICK GOLD COM BUY HOLD 24.6 26
> > > ACE ACE LTD HOLD HOLD 46.23
> > > ACI ARCH COAL INC BUY BUY 58.72 66
> > > ACN ACCENTURE LTD BUY BUY 25 32
> > > ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> > > ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> > > ADI ANALOG DEVICES INC HOLD BUY 39.06
> > > ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
> > >
> > >
> > > Thanks for the help!
> > > JT
> > >
> > >
> > > "Adrian M" wrote:
> > >
> > > > Are there any hidden decimals in the lookup criteria? Also, if you
> have
> > alpha
> > > > numeric lookup values make sure it is sorted by Excel and not by human
> > > > sorting as this can be a problem (see
> > http://www.auditexcel.co.za/errors.html
> > > > ). If none of these maybe you can give us some of the sample data?
> > > >
> > > > "jthomas" wrote:
> > > >
> > > > > I have a VLOOKUP function in Excel that is returning data from the
> row
> > above
> > > > > the target row. It must be finding the correct match data and
> column,
> > but
> > > > > always from the row above the one it should. Using the Exact match
> > parameter
> > > > > just returns an "#N/A" error. My table array is sorted.
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Thanks!
> >
> >
>
>
>

Bingo!

Now I remember running into something like this before. I am downloading
the table I am querying, what a pain to have to eliminate spaces.

Thanks!
JT


"Marc" wrote:

> Ok, one last followup...........
>
> It may be that either your C5 value has a trailing space, OR your data array
> (Argus Data) has trailing spaces after the entries in the first column.
>
> You could select the entire first column in Argus Data, and do a
> search/replace function replacing all spaces with nothing... thus making
> sure you don't have trailing spaces...
>
> "Marc" <[email protected]> wrote in message
> news:[email protected]...
> > As a followup, if you specify "FALSE" as the last parameter in the lookup,
> > then your list does not need to be sorted. If you specify "TRUE", then it
> > must be sorted...
> >
> >
> > "jthomas" <[email protected]> wrote in message
> > news:[email protected]...
> > > How would I check for hidden decimals? I don't believe there are any.
> I
> > did
> > > have Excel sort the data.
> > >
> > > Here is my formula:
> > > =VLOOKUP(C5,'Argus Data'!A:F,6)
> > >
> > > Where C5 refers to a ticker symbol. When C5 is ADP the formula result
> is
> > > "0" (from blank row above). When C5 is ACN the result is 66 (from row
> > above
> > > for ACI).
> > >
> > > Here is some sample data (sorry about formatting):
> > >
> > > ticker security_name twelve_month_rating five_year_rating
> price_per_share
> > target_price
> > > AA ALCOA INC COM BUY BUY 27.91 37
> > > AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> > > AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> > > AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> > > ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> > > ABS ALBERTSONS INC HOLD HOLD 20.95
> > > ABT ABBOTT LABS HOLD BUY 47.02
> > > ABX BARRICK GOLD COM BUY HOLD 24.6 26
> > > ACE ACE LTD HOLD HOLD 46.23
> > > ACI ARCH COAL INC BUY BUY 58.72 66
> > > ACN ACCENTURE LTD BUY BUY 25 32
> > > ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> > > ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> > > ADI ANALOG DEVICES INC HOLD BUY 39.06
> > > ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
> > >
> > >
> > > Thanks for the help!
> > > JT
> > >
> > >
> > > "Adrian M" wrote:
> > >
> > > > Are there any hidden decimals in the lookup criteria? Also, if you
> have
> > alpha
> > > > numeric lookup values make sure it is sorted by Excel and not by human
> > > > sorting as this can be a problem (see
> > http://www.auditexcel.co.za/errors.html
> > > > ). If none of these maybe you can give us some of the sample data?
> > > >
> > > > "jthomas" wrote:
> > > >
> > > > > I have a VLOOKUP function in Excel that is returning data from the
> row
> > above
> > > > > the target row. It must be finding the correct match data and
> column,
> > but
> > > > > always from the row above the one it should. Using the Exact match
> > parameter
> > > > > just returns an "#N/A" error. My table array is sorted.
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Thanks!
> >
> >
>
>
>

Ok, one last followup...........

It may be that either your C5 value has a trailing space, OR your data array
(Argus Data) has trailing spaces after the entries in the first column.

You could select the entire first column in Argus Data, and do a
search/replace function replacing all spaces with nothing... thus making
sure you don't have trailing spaces...

"Marc" <[email protected]> wrote in message
news:[email protected]...
> As a followup, if you specify "FALSE" as the last parameter in the lookup,
> then your list does not need to be sorted. If you specify "TRUE", then it
> must be sorted...
>
>
> "jthomas" <[email protected]> wrote in message
> news:[email protected]...
> > How would I check for hidden decimals? I don't believe there are any.
I
> did
> > have Excel sort the data.
> >
> > Here is my formula:
> > =VLOOKUP(C5,'Argus Data'!A:F,6)
> >
> > Where C5 refers to a ticker symbol. When C5 is ADP the formula result
is
> > "0" (from blank row above). When C5 is ACN the result is 66 (from row
> above
> > for ACI).
> >
> > Here is some sample data (sorry about formatting):
> >
> > ticker security_name twelve_month_rating five_year_rating
price_per_share
> target_price
> > AA ALCOA INC COM BUY BUY 27.91 37
> > AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> > AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> > AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> > ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> > ABS ALBERTSONS INC HOLD HOLD 20.95
> > ABT ABBOTT LABS HOLD BUY 47.02
> > ABX BARRICK GOLD COM BUY HOLD 24.6 26
> > ACE ACE LTD HOLD HOLD 46.23
> > ACI ARCH COAL INC BUY BUY 58.72 66
> > ACN ACCENTURE LTD BUY BUY 25 32
> > ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> > ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> > ADI ANALOG DEVICES INC HOLD BUY 39.06
> > ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
> >
> >
> > Thanks for the help!
> > JT
> >
> >
> > "Adrian M" wrote:
> >
> > > Are there any hidden decimals in the lookup criteria? Also, if you
have
> alpha
> > > numeric lookup values make sure it is sorted by Excel and not by human
> > > sorting as this can be a problem (see
> http://www.auditexcel.co.za/errors.html
> > > ). If none of these maybe you can give us some of the sample data?
> > >
> > > "jthomas" wrote:
> > >
> > > > I have a VLOOKUP function in Excel that is returning data from the
row
> above
> > > > the target row. It must be finding the correct match data and
column,
> but
> > > > always from the row above the one it should. Using the Exact match
> parameter
> > > > just returns an "#N/A" error. My table array is sorted.
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks!
>
>

As a followup, if you specify "FALSE" as the last parameter in the lookup,
then your list does not need to be sorted. If you specify "TRUE", then it
must be sorted...


"jthomas" <[email protected]> wrote in message
news:[email protected]...
> How would I check for hidden decimals? I don't believe there are any. I
did
> have Excel sort the data.
>
> Here is my formula:
> =VLOOKUP(C5,'Argus Data'!A:F,6)
>
> Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
> "0" (from blank row above). When C5 is ACN the result is 66 (from row
above
> for ACI).
>
> Here is some sample data (sorry about formatting):
>
> ticker security_name twelve_month_rating five_year_rating price_per_share
target_price
> AA ALCOA INC COM BUY BUY 27.91 37
> AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> ABS ALBERTSONS INC HOLD HOLD 20.95
> ABT ABBOTT LABS HOLD BUY 47.02
> ABX BARRICK GOLD COM BUY HOLD 24.6 26
> ACE ACE LTD HOLD HOLD 46.23
> ACI ARCH COAL INC BUY BUY 58.72 66
> ACN ACCENTURE LTD BUY BUY 25 32
> ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> ADI ANALOG DEVICES INC HOLD BUY 39.06
> ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
>
>
> Thanks for the help!
> JT
>
>
> "Adrian M" wrote:
>
> > Are there any hidden decimals in the lookup criteria? Also, if you have
alpha
> > numeric lookup values make sure it is sorted by Excel and not by human
> > sorting as this can be a problem (see
http://www.auditexcel.co.za/errors.html
> > ). If none of these maybe you can give us some of the sample data?
> >
> > "jthomas" wrote:
> >
> > > I have a VLOOKUP function in Excel that is returning data from the row
above
> > > the target row. It must be finding the correct match data and column,
but
> > > always from the row above the one it should. Using the Exact match
parameter
> > > just returns an "#N/A" error. My table array is sorted.
> > >
> > > Any ideas?
> > >
> > > Thanks!

Looks to me like your "C5" value might have trailing spaces, like "ADP ". If
that's the case, and you don't specify 0 or FALSE as the last parameter in
the VLOOKUP, it will return the closest match, which would be the row above
it.

I run into this problem all the time when I get sheets from other people who
have the terrible habit of adding a space to everything they type into
Excel.

Also, with VLOOKUP and HLOOKUP (versus the plain LOOKUP), I don't believe
you have to sort your data--one of the advantages of using those two
functions...

"jthomas" <[email protected]> wrote in message
news:[email protected]...
> How would I check for hidden decimals? I don't believe there are any. I
did
> have Excel sort the data.
>
> Here is my formula:
> =VLOOKUP(C5,'Argus Data'!A:F,6)
>
> Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
> "0" (from blank row above). When C5 is ACN the result is 66 (from row
above
> for ACI).
>
> Here is some sample data (sorry about formatting):
>
> ticker security_name twelve_month_rating five_year_rating price_per_share
target_price
> AA ALCOA INC COM BUY BUY 27.91 37
> AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> ABS ALBERTSONS INC HOLD HOLD 20.95
> ABT ABBOTT LABS HOLD BUY 47.02
> ABX BARRICK GOLD COM BUY HOLD 24.6 26
> ACE ACE LTD HOLD HOLD 46.23
> ACI ARCH COAL INC BUY BUY 58.72 66
> ACN ACCENTURE LTD BUY BUY 25 32
> ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> ADI ANALOG DEVICES INC HOLD BUY 39.06
> ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
>
>
> Thanks for the help!
> JT
>
>
> "Adrian M" wrote:
>
> > Are there any hidden decimals in the lookup criteria? Also, if you have
alpha
> > numeric lookup values make sure it is sorted by Excel and not by human
> > sorting as this can be a problem (see
http://www.auditexcel.co.za/errors.html
> > ). If none of these maybe you can give us some of the sample data?
> >
> > "jthomas" wrote:
> >
> > > I have a VLOOKUP function in Excel that is returning data from the row
above
> > > the target row. It must be finding the correct match data and column,
but
> > > always from the row above the one it should. Using the Exact match
parameter
> > > just returns an "#N/A" error. My table array is sorted.
> > >
> > > Any ideas?
> > >
> > > Thanks!

How would I check for hidden decimals? I don't believe there are any. I did
have Excel sort the data.

Here is my formula:
=VLOOKUP(C5,'Argus Data'!A:F,6)

Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
"0" (from blank row above). When C5 is ACN the result is 66 (from row above
for ACI).

Here is some sample data (sorry about formatting):

ticker security_name twelve_month_rating five_year_rating price_per_share target_price
AA ALCOA INC COM BUY BUY 27.91 37
AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
AAPL APPLE COMPUTER INC BUY BUY 42.75 51
ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
ABS ALBERTSONS INC HOLD HOLD 20.95
ABT ABBOTT LABS HOLD BUY 47.02
ABX BARRICK GOLD COM BUY HOLD 24.6 26
ACE ACE LTD HOLD HOLD 46.23
ACI ARCH COAL INC BUY BUY 58.72 66
ACN ACCENTURE LTD BUY BUY 25 32
ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
ADI ANALOG DEVICES INC HOLD BUY 39.06
ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53


Thanks for the help!
JT


"Adrian M" wrote:

> Are there any hidden decimals in the lookup criteria? Also, if you have alpha
> numeric lookup values make sure it is sorted by Excel and not by human
> sorting as this can be a problem (see http://www.auditexcel.co.za/errors.html
> ). If none of these maybe you can give us some of the sample data?
>
> "jthomas" wrote:
>
> > I have a VLOOKUP function in Excel that is returning data from the row above
> > the target row. It must be finding the correct match data and column, but
> > always from the row above the one it should. Using the Exact match parameter
> > just returns an "#N/A" error. My table array is sorted.
> >
> > Any ideas?
> >
> > Thanks!

Are there any hidden decimals in the lookup criteria? Also, if you have alpha
numeric lookup values make sure it is sorted by Excel and not by human
sorting as this can be a problem (see http://www.auditexcel.co.za/errors.html
). If none of these maybe you can give us some of the sample data?

"jthomas" wrote:

> I have a VLOOKUP function in Excel that is returning data from the row above
> the target row. It must be finding the correct match data and column, but
> always from the row above the one it should. Using the Exact match parameter
> just returns an "#N/A" error. My table array is sorted.
>
> Any ideas?
>
> Thanks!

Ok, one last followup...........

It may be that either your C5 value has a trailing space, OR your data array
(Argus Data) has trailing spaces after the entries in the first column.

You could select the entire first column in Argus Data, and do a
search/replace function replacing all spaces with nothing... thus making
sure you don't have trailing spaces...

"Marc" <[email protected]> wrote in message
news:[email protected]...
> As a followup, if you specify "FALSE" as the last parameter in the lookup,
> then your list does not need to be sorted. If you specify "TRUE", then it
> must be sorted...
>
>
> "jthomas" <[email protected]> wrote in message
> news:[email protected]...
> > How would I check for hidden decimals? I don't believe there are any.
I
> did
> > have Excel sort the data.
> >
> > Here is my formula:
> > =VLOOKUP(C5,'Argus Data'!A:F,6)
> >
> > Where C5 refers to a ticker symbol. When C5 is ADP the formula result
is
> > "0" (from blank row above). When C5 is ACN the result is 66 (from row
> above
> > for ACI).
> >
> > Here is some sample data (sorry about formatting):
> >
> > ticker security_name twelve_month_rating five_year_rating
price_per_share
> target_price
> > AA ALCOA INC COM BUY BUY 27.91 37
> > AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> > AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> > AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> > ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> > ABS ALBERTSONS INC HOLD HOLD 20.95
> > ABT ABBOTT LABS HOLD BUY 47.02
> > ABX BARRICK GOLD COM BUY HOLD 24.6 26
> > ACE ACE LTD HOLD HOLD 46.23
> > ACI ARCH COAL INC BUY BUY 58.72 66
> > ACN ACCENTURE LTD BUY BUY 25 32
> > ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> > ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> > ADI ANALOG DEVICES INC HOLD BUY 39.06
> > ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
> >
> >
> > Thanks for the help!
> > JT
> >
> >
> > "Adrian M" wrote:
> >
> > > Are there any hidden decimals in the lookup criteria? Also, if you
have
> alpha
> > > numeric lookup values make sure it is sorted by Excel and not by human
> > > sorting as this can be a problem (see
> http://www.auditexcel.co.za/errors.html
> > > ). If none of these maybe you can give us some of the sample data?
> > >
> > > "jthomas" wrote:
> > >
> > > > I have a VLOOKUP function in Excel that is returning data from the
row
> above
> > > > the target row. It must be finding the correct match data and
column,
> but
> > > > always from the row above the one it should. Using the Exact match
> parameter
> > > > just returns an "#N/A" error. My table array is sorted.
> > > >
> > > > Any ideas?
> > > >
> > > > Thanks!
>
>

Bingo!

Now I remember running into something like this before. I am downloading
the table I am querying, what a pain to have to eliminate spaces.

Thanks!
JT


"Marc" wrote:

> Ok, one last followup...........
>
> It may be that either your C5 value has a trailing space, OR your data array
> (Argus Data) has trailing spaces after the entries in the first column.
>
> You could select the entire first column in Argus Data, and do a
> search/replace function replacing all spaces with nothing... thus making
> sure you don't have trailing spaces...
>
> "Marc" <[email protected]> wrote in message
> news:[email protected]...
> > As a followup, if you specify "FALSE" as the last parameter in the lookup,
> > then your list does not need to be sorted. If you specify "TRUE", then it
> > must be sorted...
> >
> >
> > "jthomas" <[email protected]> wrote in message
> > news:[email protected]...
> > > How would I check for hidden decimals? I don't believe there are any.
> I
> > did
> > > have Excel sort the data.
> > >
> > > Here is my formula:
> > > =VLOOKUP(C5,'Argus Data'!A:F,6)
> > >
> > > Where C5 refers to a ticker symbol. When C5 is ADP the formula result
> is
> > > "0" (from blank row above). When C5 is ACN the result is 66 (from row
> > above
> > > for ACI).
> > >
> > > Here is some sample data (sorry about formatting):
> > >
> > > ticker security_name twelve_month_rating five_year_rating
> price_per_share
> > target_price
> > > AA ALCOA INC COM BUY BUY 27.91 37
> > > AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> > > AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> > > AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> > > ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> > > ABS ALBERTSONS INC HOLD HOLD 20.95
> > > ABT ABBOTT LABS HOLD BUY 47.02
> > > ABX BARRICK GOLD COM BUY HOLD 24.6 26
> > > ACE ACE LTD HOLD HOLD 46.23
> > > ACI ARCH COAL INC BUY BUY 58.72 66
> > > ACN ACCENTURE LTD BUY BUY 25 32
> > > ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> > > ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> > > ADI ANALOG DEVICES INC HOLD BUY 39.06
> > > ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
> > >
> > >
> > > Thanks for the help!
> > > JT
> > >
> > >
> > > "Adrian M" wrote:
> > >
> > > > Are there any hidden decimals in the lookup criteria? Also, if you
> have
> > alpha
> > > > numeric lookup values make sure it is sorted by Excel and not by human
> > > > sorting as this can be a problem (see
> > http://www.auditexcel.co.za/errors.html
> > > > ). If none of these maybe you can give us some of the sample data?
> > > >
> > > > "jthomas" wrote:
> > > >
> > > > > I have a VLOOKUP function in Excel that is returning data from the
> row
> > above
> > > > > the target row. It must be finding the correct match data and
> column,
> > but
> > > > > always from the row above the one it should. Using the Exact match
> > parameter
> > > > > just returns an "#N/A" error. My table array is sorted.
> > > > >
> > > > > Any ideas?
> > > > >
> > > > > Thanks!
> >
> >
>
>
>

I have a VLOOKUP function in Excel that is returning data from the row above
the target row. It must be finding the correct match data and column, but
always from the row above the one it should. Using the Exact match parameter
just returns an "#N/A" error. My table array is sorted.

Any ideas?

Thanks!

As a followup, if you specify "FALSE" as the last parameter in the lookup,
then your list does not need to be sorted. If you specify "TRUE", then it
must be sorted...


"jthomas" <[email protected]> wrote in message
news:[email protected]...
> How would I check for hidden decimals? I don't believe there are any. I
did
> have Excel sort the data.
>
> Here is my formula:
> =VLOOKUP(C5,'Argus Data'!A:F,6)
>
> Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
> "0" (from blank row above). When C5 is ACN the result is 66 (from row
above
> for ACI).
>
> Here is some sample data (sorry about formatting):
>
> ticker security_name twelve_month_rating five_year_rating price_per_share
target_price
> AA ALCOA INC COM BUY BUY 27.91 37
> AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> ABS ALBERTSONS INC HOLD HOLD 20.95
> ABT ABBOTT LABS HOLD BUY 47.02
> ABX BARRICK GOLD COM BUY HOLD 24.6 26
> ACE ACE LTD HOLD HOLD 46.23
> ACI ARCH COAL INC BUY BUY 58.72 66
> ACN ACCENTURE LTD BUY BUY 25 32
> ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> ADI ANALOG DEVICES INC HOLD BUY 39.06
> ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
>
>
> Thanks for the help!
> JT
>
>
> "Adrian M" wrote:
>
> > Are there any hidden decimals in the lookup criteria? Also, if you have
alpha
> > numeric lookup values make sure it is sorted by Excel and not by human
> > sorting as this can be a problem (see
http://www.auditexcel.co.za/errors.html
> > ). If none of these maybe you can give us some of the sample data?
> >
> > "jthomas" wrote:
> >
> > > I have a VLOOKUP function in Excel that is returning data from the row
above
> > > the target row. It must be finding the correct match data and column,
but
> > > always from the row above the one it should. Using the Exact match
parameter
> > > just returns an "#N/A" error. My table array is sorted.
> > >
> > > Any ideas?
> > >
> > > Thanks!

Looks to me like your "C5" value might have trailing spaces, like "ADP ". If
that's the case, and you don't specify 0 or FALSE as the last parameter in
the VLOOKUP, it will return the closest match, which would be the row above
it.

I run into this problem all the time when I get sheets from other people who
have the terrible habit of adding a space to everything they type into
Excel.

Also, with VLOOKUP and HLOOKUP (versus the plain LOOKUP), I don't believe
you have to sort your data--one of the advantages of using those two
functions...

"jthomas" <[email protected]> wrote in message
news:[email protected]...
> How would I check for hidden decimals? I don't believe there are any. I
did
> have Excel sort the data.
>
> Here is my formula:
> =VLOOKUP(C5,'Argus Data'!A:F,6)
>
> Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
> "0" (from blank row above). When C5 is ACN the result is 66 (from row
above
> for ACI).
>
> Here is some sample data (sorry about formatting):
>
> ticker security_name twelve_month_rating five_year_rating price_per_share
target_price
> AA ALCOA INC COM BUY BUY 27.91 37
> AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
> AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
> AAPL APPLE COMPUTER INC BUY BUY 42.75 51
> ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
> ABS ALBERTSONS INC HOLD HOLD 20.95
> ABT ABBOTT LABS HOLD BUY 47.02
> ABX BARRICK GOLD COM BUY HOLD 24.6 26
> ACE ACE LTD HOLD HOLD 46.23
> ACI ARCH COAL INC BUY BUY 58.72 66
> ACN ACCENTURE LTD BUY BUY 25 32
> ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
> ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
> ADI ANALOG DEVICES INC HOLD BUY 39.06
> ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53
>
>
> Thanks for the help!
> JT
>
>
> "Adrian M" wrote:
>
> > Are there any hidden decimals in the lookup criteria? Also, if you have
alpha
> > numeric lookup values make sure it is sorted by Excel and not by human
> > sorting as this can be a problem (see
http://www.auditexcel.co.za/errors.html
> > ). If none of these maybe you can give us some of the sample data?
> >
> > "jthomas" wrote:
> >
> > > I have a VLOOKUP function in Excel that is returning data from the row
above
> > > the target row. It must be finding the correct match data and column,
but
> > > always from the row above the one it should. Using the Exact match
parameter
> > > just returns an "#N/A" error. My table array is sorted.
> > >
> > > Any ideas?
> > >
> > > Thanks!

How would I check for hidden decimals? I don't believe there are any. I did
have Excel sort the data.

Here is my formula:
=VLOOKUP(C5,'Argus Data'!A:F,6)

Where C5 refers to a ticker symbol. When C5 is ADP the formula result is
"0" (from blank row above). When C5 is ACN the result is 66 (from row above
for ACI).

Here is some sample data (sorry about formatting):

ticker security_name twelve_month_rating five_year_rating price_per_share target_price
AA ALCOA INC COM BUY BUY 27.91 37
AAI AIRTRAN HLDGS INC BUY BUY 11.81 14
AAP ADVANCE AUTO PARTS INC BUY BUY 69.05 78
AAPL APPLE COMPUTER INC BUY BUY 42.75 51
ABC AMERISOURCEBERGEN CORP HOLD HOLD 71.56
ABS ALBERTSONS INC HOLD HOLD 20.95
ABT ABBOTT LABS HOLD BUY 47.02
ABX BARRICK GOLD COM BUY HOLD 24.6 26
ACE ACE LTD HOLD HOLD 46.23
ACI ARCH COAL INC BUY BUY 58.72 66
ACN ACCENTURE LTD BUY BUY 25 32
ADBE ADOBE SYSTEMS INC BUY BUY 29.46 40
ADCT ADC TELECOMMUNICATION STK HOLD BUY 26.31
ADI ANALOG DEVICES INC HOLD BUY 39.06
ADP AUTOMATIC DATA PROCESS BUY BUY 44.36 53


Thanks for the help!
JT


"Adrian M" wrote:

> Are there any hidden decimals in the lookup criteria? Also, if you have alpha
> numeric lookup values make sure it is sorted by Excel and not by human
> sorting as this can be a problem (see http://www.auditexcel.co.za/errors.html
> ). If none of these maybe you can give us some of the sample data?
>
> "jthomas" wrote:
>
> > I have a VLOOKUP function in Excel that is returning data from the row above
> > the target row. It must be finding the correct match data and column, but
> > always from the row above the one it should. Using the Exact match parameter
> > just returns an "#N/A" error. My table array is sorted.
> >
> > Any ideas?
> >
> > Thanks!

Are there any hidden decimals in the lookup criteria? Also, if you have alpha
numeric lookup values make sure it is sorted by Excel and not by human
sorting as this can be a problem (see http://www.auditexcel.co.za/errors.html
). If none of these maybe you can give us some of the sample data?

"jthomas" wrote:

> I have a VLOOKUP function in Excel that is returning data from the row above
> the target row. It must be finding the correct match data and column, but
> always from the row above the one it should. Using the Exact match parameter
> just returns an "#N/A" error. My table array is sorted.
>
> Any ideas?
>
> Thanks!