CODEWITHSUNDEEP
vbaexcelvlookup
Mark Lawrence
Mark Lawrence

Reputation: 355

Excel VBA VLookup on a tables second column

Just trying to see if its possible to use VLookup on the second column of a table? I have searched endlessly and cant find an answer to this but I feel like this should be possible.

I'll add at this point that VBA is not my strong point. I come from a PHP/MySQL background but dont have the luxury of using either at my work so Im resorting to learning VBA.

My code so far (simplified):

Dim userEnviron As String
Dim rowId As Integer

rowId = 0
userEnviron = Environ("Username")

Dim tbladmin As ListObject
Set tbladmin = Sheets("Office Details").ListObjects("officeAdmin")

On Error Resume Next
rowId = Application.VLookup(userEnviron , Range(tbladmin), 4, False)

This code is looking up the user environmental variable and looking for a match in the tbldadmin table. The table is constructed like so with headers:

Name UserID Email RowID Tom Smith Tom [email protected] 2

Im trying to lookup the UserID and return the Name, however at the moment I have the code set to lookup the Name and return the RowID (I had added a second snippet of code to take the RowID and return the name)

I can see a lot of non-VBA examples where this is done, however none that appear to use tables.

Can VLookup be used as described, to search for a match in the second column and return a value from the first column? Hell I'll take a return from the last column if only I can search the second column.

Upvotes: 4

Views: 16458

Answers (3)

Daniele
Daniele

Reputation: 329

the right approach should not to use VLOOKUP on a ListObject, but maybe to avail of the Find() native method of ListObject. Take a look at this page

Upvotes: 0

R3uK
R3uK

Reputation: 14537

You can directly Offset the Range in which you want to look for :

Dim userEnviron As String
Dim rowId As Integer

rowId = 0
userEnviron = Environ("Username")

Dim tbladmin As ListObject
Set tbladmin = Sheets("Office Details").ListObjects("officeAdmin")

On Error Resume Next
rowId = Application.VLookup(userEnviron, tbladmin.Range.Offset(0, 1), 3, False)

Upvotes: 2

Jordan
Jordan

Reputation: 4514

If you want to VLOOKUP but your data is not in the leftmost column you can use INDEX/MATCH which allows you to specify where the data you are looking up is as well as which data you want to return. Try this:

rowId = WorksheetFunction.INDEX(Range("officeAdmin[RowID]"), WorksheetFunction.MATCH(userEnviron, Range("officeAdmin[UserID]"), 0))

You can change Range("officeAdmin[RowID]") for whichever column you want to lookup, for example Range("officeAdmin[Name]") will return the Name instead of the RowID.

Upvotes: 7

Related Questions