4 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    External Lookup only matching 90% of entries

      I have opened an Excel spreadsheet containing 3,300 line. I then have an External Lookup matching on 3 fields (Date, Amount and Employee Number all of which are text fields).

       

      The file that is being used as an External Lookup was a text file that was opened in Monarch and the required fields exported to an Excel file.

       

      The External Lookup is able to find a match for around 3,000 rows however it should be matching all 3,300. When I manually check for the 300 rows that do not match I can find them.

       

      Effectivley I have 300 lines that are not matching but the data on that sample 300 (i.e. the same employee number) are finding matches in the other 3,000 items.

       

      Originally the External Lookup was on a network drive and I thought access speed may be the issue however I moved the file to the C drive and still have the same issue.

       

      Every time I open the file in Monarch and add the External Lookup it is the same 300 lines that do not find a match.

       

      Does anyone have any suggestions what I could try and do to resolve this or has anyone had any similar issues to this?

       

      Thanks

        • External Lookup only matching 90% of entries
          Grant Perkins

          Hi James,

           

          Do I understand correctly that the lookup table is an Excel sheet derived form a text file?

           

          I assume the real lookup you are preforming and having problems with is for another file. Have you tried testing the lookup on the originating file?

           

          If the excel lookup does not work using its souorce file there is clearly some sort of anomaly either in the creation of the Excel table or in the lookup process.

           

          If the lookup works with the source file but not the intended file for which it was created then it may be that there is a difficult to spot data problem.

           

          Does this provide you with anything new to consider or have I just covered old ground?

           

           

          Grant

            • External Lookup only matching 90% of entries
              rjgrunner _

              Hi Guys,

               

              This may be over simplistic but I tend to find when I have a problem with an external lookup it is due to looking up a 'Named Range' vs. a 'Worksheet'. As your lookup file is added to the named range no longer covers all possibilities leading to the problem you mentioned.

               

              Learned from plenty of head scratching in the past!

               

              Hope this helps.

            • External Lookup only matching 90% of entries
              rorselli _

              I had problems in the past trying to make external lookups in excel files.

               

              In my case the solution was to export joint files as .DBF (IV). remember the field names and file name can be 8 chars long at most.

               

              when searching for lookups in .DBF files I had no errors.