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.

There are more ways to do what you want to do than using VLOOKUP. Try using this formula instead... =IF(MATCH("Tom", $B$2:$B$B3,0),A2,"") You could also try using INDEX/MATCH — Martin Router King
NO you can't search the second column, VLOOKUP is designed to look only the first column of your selection. BUT you can use INDEX() MATCH() combination, take a look at this article — Han Soalone

Jordan Jordan · Accepted Answer · 2017-02-13T10:28:58

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.

Excel VBA VLookup on a tables second column

2 Answers