VLOOKUP - isn't working??

[tlapointe1970]

Attached is my spreadsheet I am working on ... in the 'Small" tab you will see a string of N/A on the right. I was doing a simple vlookup that isn't working. I don't see any formatting of the B column ticker symbols that should interfere with the lookup on the "universe" tab - both sets of ticker symbols are set to "general" ... the same data could be looked up on the IWV tab as well ... I tried looking up on either worksheet both ways and come up with the same errors.


Can someone take a peak and let me know if they see what the problem is?

Thanks. Terri

[martindwilson]

check all columns with ago etc some have spaces after the names so ago will not match ago space space

[Marcol]

Most of your data has a varying amount of leading and trailing spaces.
This is causing the function to fail because the various sheets differ in the amount of spaces any one code has.

There are also 872 duplicates in your lookup table "universe" your lookup will return the first found in each case so your data will be compromised.

The only way to go is to clean out all your data that consists of a code in all sheets,
Use e.g. Trim(A1) in a spare column, then Paste Special > Values over the code column

Once you have checked for duplicates all should be straight forward.

I have trimmed "universe", "IWW", & "IWV" in the attached.

Hope this helps

[martindwilson]

another way to trim spaces is text to columns
select column
data/text to columns/fixed width
next
create a break way over to the right
next
select any column (probably only one) with no data in it
select
do not import column(skip)
click finish

[tlapointe1970]

Thank you very much for your help