VLOOKUP returning wrong row

[Guest]

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!

 

[Guest]

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?

 

[Guest]

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

 

[Marc]

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

 

[Marc]

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

 

[Marc]

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

 

[Guest]

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