Justincut

asked on

# Vlookup Function problem

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

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

Hi,

pls try

it reduces the rows to the last row from the bottom

Personnaly I would have Change it to

Regards

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

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

to use the column of the rangeRegards

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:

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

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)

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))`

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

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,B

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

ASKER

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

DummyRec6.xlsx

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

=INDEX('Prev Day'!AD$5:AD$1000,MATCH(TR

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

ASKER

Ok, I am still getting NA's in Column AD. see attachment. Any ideas why?

DummyRec6.xlsx

DummyRec6.xlsx

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

regards, barry

ASKER

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

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.

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.

ASKER

Can you say that in a simpler way? I am not a very good programmer.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.

@Brad, thanks for appreciating the workaround and eliminating one index lookup which makes it much better.

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