Solved

Problem with Vlookup as Vlookup Range is bigger than 256 characters

Posted on 2013-11-18
5
324 Views
Last Modified: 2013-11-20
Hi Guys, I am having a problem as the "AD5" on the DummyRec6 attachment is not working as the Vlookup in "B5" is 420 characters long. Can someone suggest a solution? The other attachment is what is being VLookedup.
DummyRec6.xlsx
Murex-BS-rec-breaks---111113-KM.xlsx
0
Comment
Question by:Justincut
5 Comments
 
LVL 30

Expert Comment

by:captain
ID: 39656475
It appears that this is less of a data issue like you normally get with the 255 limit, but an operational/organisational. So the traditional INDEX workaround will not be of much help

Your lookup itself is straight forward but it becomes bloated due to your absolute path to the file location of your source data.

The obvious workaround would be to change the location of the files. Now I assume that you don't just need to do this on this one file, but on multiples in different folders.

What would be the application of the lookup? Your own use or multiple users?It does not look like you could work with a mapped drive as this won't significantly shorten the vlookups, as the file specific root starts fairly early. However is there scope to shortening the path by changing folder names?

Ultimately you may want to consider some form of 'localising' the file, either through a mapped drive or a true local copy, as your solution at the moment has a quite obvious pratical inconvenience: Your formula relates to a current month, so you will need to change this every time the month or the file location changes.

However all that being said you may be able to live with a simple trick:
Create one or more new tabs and simply point the cells there to the relevant cell in the source file, like
='V:\T....'

Open in new window


That way you create a carbon copy of the file locally in your workbook, but it does not give you much flexibility for months, file locations etc. That would still be a manual change each time..

hth
capt.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39656484
What happened to the UDF that we worked on here?

Your other thread
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39656512
The string in both files should be identical. This is not the case in the given files. So you would not get the match. This is not a 255 char limit problem.
0
 

Author Comment

by:Justincut
ID: 39656606
Sorry Chaps, I have put the Vlookup to another tab in the DummyRec attachment here to illustrate my problem. In the AD column on the "Rec" tab, I am getting a VALUE error message. Any ideas in combatting this problem?
DummyRec6.xlsx
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39656651
If you use the function from your previous thread,

Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant
 Dim cl As Range
 For Each cl In c.Columns(1).Cells
     If UCase(Lval) = UCase(cl) Then
         MyVlookup = cl.Offset(, oset - 1)
         Exit Function
     End If
     Next
 End Function

Open in new window


and apply formula

=IF(ISNA(myVlookup($B5,'Prev Day'!$B:$AL,AD$1)),IF($J5="","NEW",IF(ISNA(myVlookup($C5,'Prev Day'!$C:$AL,AD$1-1)),"NEW",myVlookup($C5,'Prev Day'!$C:$AL,AD$1-1))),myVLOOKUP($B5,'Prev Day'!$B:$AL,AD$1))

Then you get 0 which means no matches found with this function.

Again, though, it is recommended you reduce the range sizes because this will be a slow process.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now