Excel VBA VLookup on a tables second column

Question

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.

Answer 1

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)

Answer 2

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