使用 INDEX 和 MATCH VBA 查找左侧的值反向 Vlookup [英] Finding a value to the left Reverse Vlookup using INDEX and MATCH VBA

问题描述

我看到很多论坛都说可以找到左列的数据，而不是 Vlookup 功能.

I've seen many forum saying that it is possible to find data to the left column, as opposed to Vlookup function.

我有一张我想从左边获取值的工作表.换句话说，我想在右边搜索一个序列号，比如第 3 列，并在第 1 列上获得一个值.

I have a sheet of which I would like to get the value from the left. In other words, I would like to seach for a serial # on the right, say column 3, and get a value on column 1.

VBA 中 Vlookup 的替代方法是什么?(即反向查找)

What would an alternative to Vlookup in VBA? (i.e., reverse vlookup)

我在几个论坛上看到了使用索引匹配的示例，但我无法理解语法.有人可以给我提供一个简单的例子吗?

I seen several forums with examples using index match, but I cannot understand the syntax. Can someone please provide me with a simple example?

这是我之前使用的，发现Vlookup无法向左搜索:

Here is what I was using before and found out that Vlookup cannot search to the left:

Sal = Application.WorksheetFunction.VLookup("3491709101",Sheets(PreviousTabName).Range(ThisRAnge), 2, False)

推荐答案

索引匹配语法 VBA

Dim ReturnValueRange as range, LookupRange as range

Set ReturnValueRange = Thisworkbook.Sheets("Sheet1").range("$E$1:$E$7")
Set LookupRange = Thisworkbook.Sheets("Sheet1").range("$G$1:$G$7")

LookUpValue = "findMe"

Result = Application.WorksheetFunction.INDEX(ReturnValueRange,Application.WorksheetFunction.MATCH(LookUpValue, LookupRange, 0))

索引匹配语法公式

=INDEX(ReturnValueRange,MATCH(LookUpValue, LookupRange, 0))

LEFT VLOOKUP 语法 VBA

• 不匹配返回错误

Result = WorksheetFunction.VLOOKUP(LookUpValue, WorksheetFunction.CHOOSE(Array(1,2), LookupRange, ReturnValueRange), 2, 0))

LEFT VLOOKUP 语法公式

=VLOOKUP(LookUpValue, WorksheetFunction.CHOOSE({1,2}, LookupRange, ReturnValueRange), 2, 0))

• CHOOSE"函数正在处理这两个字段.
• 您正在为数据范围分配参考索引"编号 –将 LookupRange 分配给索引号 1，将 ReturnValueRange 分配给索引号 2
• 所以，当你在 VLookup 函数中输入2"时，你实际上是引用 CHOOSE 函数中的索引号 2.

这篇关于使用 INDEX 和 MATCH VBA 查找左侧的值反向 Vlookup的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！