表格第二列上的 Excel VBA VLookup
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42201646/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA VLookup on a tables second column
提问by Mark Lawrence
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.
只是想看看是否可以在表的第二列上使用 VLookup？我无休止地搜索并找不到答案，但我觉得这应该是可能的。
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.
我要补充一点，VBA 不是我的强项。我来自 PHP/MySQL 背景，但在我的工作中没有使用任何一种的奢侈，所以我求助于学习 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:
此代码正在查找用户环境变量并在 tbldadmin 表中查找匹配项。该表的构造与标题类似：
Name UserID Email RowID
Tom Smith Tom [email protected] 2
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)
我试图查找用户 ID 并返回名称，但是目前我已将代码设置为查找名称并返回 RowID（我添加了第二段代码来获取 RowID 并返回名称）
I can see a lot of non-VBA examples where this is done, however none that appear to use tables.
我可以看到很多完成此操作的非 VBA 示例，但是似乎没有一个使用表格。
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.
可以按照描述使用 VLookup 来搜索第二列中的匹配项并从第一列返回一个值吗？如果我能搜索第二列，我会从最后一列返回。
采纳答案by Jordan
If you want to VLOOKUPbut your data is not in the leftmost column you can use INDEX/MATCHwhich allows you to specify where the data you are looking up is as well as which data you want to return. Try this:
如果您想要VLOOKUP但您的数据不在最左边的列中，您可以使用INDEX/MATCH来指定要查找的数据的位置以及要返回的数据。尝试这个：
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 Nameinstead of the RowID.
您可以更改Range("officeAdmin[RowID]")您要查找哪个列，例如Range("officeAdmin[Name]")将返回Name代替RowID。
回答by R3uK
You can directly Offsetthe Range in which you want to look for :
您可以直接Offset查找要查找的 Range ：
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)
