Solved

Vlookup Function problem

Posted on 2013-11-24
14
415 Views
Last Modified: 2013-11-27
Hi Guys, because of the Vlookup limit of 256 characters, I have had to create a Function which creates my own Vlookup, but my spreadsheet is very slow as its covering the whole column.How do I just adapt it to the first 1000 rows?  Code is below.



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
0
Comment
Question by:Justincut
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +4
14 Comments
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39672506
Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant
Dim cl As Range
Dim count =1

For Each cl In c.Columns(1).Cells
if count < 1000
    If UCase(Lval) = UCase(cl) Then
        MyVlookup = cl.Offset(, oset - 1)
        count = count+1
        Exit Function
    End If
else
       Exit Function
end if
    Next
End Function
0
 
LVL 51

Expert Comment

by:Rgonzo1971
ID: 39672628
Hi,

pls try

it reduces the rows to the last row from the bottom
Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant
Dim cl As Range
Set mycolrange = Range(Cells(1, c.Columns(1)), Cells(Rows.Count, c.Columns(1)).End(xlUp))
For Each cl In mycolrange
    If UCase(Lval) = UCase(cl) Then
        MyVlookup = cl.Offset(, oset - 1)
        Exit Function
    End If
    Next
End Function

Open in new window


Personnaly I would have Change it to

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

Open in new window

to use the column of the range

Regards
0
 
LVL 81

Expert Comment

by:byundt
ID: 39673142
When you test the code suggested by Rgonzo1971, make sure that the worksheet containing the lookup table is active. You will get an unexpected result if it is not--lookup will be performed on cells with same address only in the active worksheet.

I would also suggest changing Lval from a Range to a Variant. That way, you can test your function with numbers or text constants as well as range references.

Finally, you may want to try using VLOOKUP before looping through all the cells. If it returns a value, then you don't need to do anything else.

I tweaked Rgonzo1971's code to cater to the above issues:
Function MyVlookup(Lval As Variant, c As Range, oset As Long) As Variant
Dim cl As Range, MyColRange As Range
Dim v As Variant
On Error Resume Next
v = Application.VLookup(Lval, c, oset, False)
On Error GoTo 0

If IsError(v) Then
    With c.Worksheet
        Set MyColRange = Range(c.Cells(1, 1), .Cells(.Rows.Count, c.Column).End(xlUp))
    End With
    For Each cl In MyColRange
        If UCase(Lval) = UCase(cl) Then
            v = cl.Offset(, oset - 1)
            Exit For
        End If
    Next
End If
MyVlookup = v
End Function

Open in new window

0
RoboForm Secure Password Management System

RoboForm Everywhere - Superb Browser Support
Windows / Apple / IOS / Android / Linux / Chrome OS
Use different complex passwords everywhere
Best Secure Password Management by far
Synchronize all of your devices instantly
Safe, Secure & Highly Recommended!

 
LVL 12

Expert Comment

by:zalazar
ID: 39673165
Instead of using Vlookup or using some code as suggested you might want to have a look at using a formula with Index in combination with Match,

E.g. Looking up D1 in B1:B10 and returning a result from A1:A10
  (note that ; is used as separator this might be different in your situation)
=INDEX(A1:A10;MATCH(TRUE;INDEX(B1:B10=D1;0);0))

Open in new window

As Index doesn't have the 256 chars limitation but Match has you have to use Index twice in the formula. The other advantage of using Index and Match is that you don't have to sort the range where you lookup the value.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39673224
zalazar,
That's a neat workaround! And it doesn't even need to be array-entered.

You can eliminate one of the INDEX functions if you array-enter the following:
=INDEX(A1:A10,MATCH(TRUE,B1:B10=D1,0))

I did get a noticeable recalc delay when I replaced A1:A10 and B1:B10 with entire columns, so Justincut would be well advised to restrict the formula to the first 1000 rows or so.

Brad
0
 

Author Comment

by:Justincut
ID: 39674289
On this attachment then, I want the "AD" column on the "Rec" tab to lookup up Column B on the "Prevday" tab and take the values of the "AD" column on the "Prevday" tab. How would I use Index, Match in this scenario?
DummyRec6.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39674378
Try this formula in AD5 copied down

=INDEX('Prev Day'!AD$5:AD$1000,MATCH(TRUE,INDEX('Prev Day'!B$5:B$1000=B5,0),0))

that uses zalazar's suggestion above.

You seem to be restricting column B to 255 characters anway, using LEFT function, so a traditional VLOOKUP would give you the same result in those circumstances......but if you remove the LEFT function from column B then some of the results can only be achieved by this approach (e.g. rows 602 and 603)

regards, barry
0
 

Author Comment

by:Justincut
ID: 39674418
Ok, I am still getting NA's in Column AD. see attachment. Any ideas why?
DummyRec6.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 39674591
I'm assuming that for some of the entries there's just no match. The cell contents would have to match exactly (except for case), so, for instance, B5 doesn't exactly match 'Prev day'!B5, are you expecting a match there or somewhere else?

regards, barry
0
 

Author Comment

by:Justincut
ID: 39674719
Hi Guys, can you do an INDEX MATCH to another file? We basically roll a daily Rec every day by date and then do a Vlookup on the Previous Day
0
 
LVL 45

Expert Comment

by:aikimark
ID: 39674970
Two thoughts:
1. Since you have a static value to compare, you should do the UCase() outside any looping structures.
2. Since you need to do a text (non-binary) comparison, you should do a performance comparison of the UCase() against StrComp() and InStr() functions, which have a comparison type parameter.
0
 

Author Comment

by:Justincut
ID: 39675409
Can you say that in a simpler way? I am not a very good programmer.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39675601
If you rename the previous day file every day to a standard file name (like Previous Day.xlsx) and open it before opening the file with INDEX & MATCH, you will not need to update your formulas. They will automatically draw data from the previous day file as required.
0
 
LVL 12

Expert Comment

by:zalazar
ID: 39681749
My share to solve this issue was very small but anyway...
@Brad, thanks for appreciating the workaround and eliminating one index lookup which makes it much better.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Outlook Free & Paid Tools
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 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