algorithm - 修改后的 VlookUp 返回与查找值对应的第 k 个值

Question

我想修改这个函数:Custom Excel VBA Function (Modified VLOOKUP) from Cell referring to a range in different file gives an error

我需要的功能在概念上很简单——我需要 VlookUp，它的返回值对应于查找值的第 k 次出现而不是标准的第 1 次出现，例如:

如果第 k 次出现不存在，则函数应返回错误。

类似电子表格的数据:

     A       B   
1   "a"    "1a"   
2   "a"    "2a"    
3   "b"    "1b"
4   "a"    "3a"
5   "b"    "2a"

VLOOKUPnew(lookup_value =A1, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=1) should return 1a

VLOOKUPnew(lookup_value =A1, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=2) should return 2a

VLOOKUPnew(lookup_value =A1, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=3) should return 3a

VLOOKUPnew(lookup_value =A3, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=1) should return 1b

VLOOKUPnew(lookup_value =A3, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=2) should return 2b

VLOOKUPnew(lookup_value =A3, table_array =A1:B3, 
        col_index_num = 2, exactMatch =0, k=3) should return error

我熟悉 R 和 Matlab，所以我的想法是面向向量的，我首先尝试通过重写一行代码(来 self 链接到的帖子):

row = .Match(lookup_value, table_array.Columns(1), 0)

进入:

If k =2 Then
row_1 = .Match(lookup_value, table_array.Columns(1), 0)
number_of_rows=table_array.Columns(1).Rows.Count

row = .Match(lookup_value, table_array.Columns(1).Rows( (row_1+1):number_of_rows ), 0)

上面一行是伪代码，因为我不知道如何正确编写它(.Rows( (row_1+1):number_of_rows ) 是数字向量，看起来很有趣)

else
row = .Match(lookup_value, table_array.Columns(1), 0)<br/>
End If

for k > 2 将此代码放入 for 循环会很简单(但效率低下)。

我注意到修改后的 .Match() 也将 k 作为参数，这将使所有工作都需要。使用循环查找值的第 k 次出现的位置似乎很慢，或者我对 VBA 不是很熟悉。

Answer 1

您可以尝试这两个基于 Excel 的公式:根据您的数据表进行调整。

• 方法一:

CountIF 函数允许您计算查找值在列范围内出现的次数。

=COUNTIF(columnRange,lookupvalue)

假设这就是您要查找的内容:从引用中提取的数据。

CUST 列使用 =F78&COUNTIF($F$75:$F78,F78)

填充

Master Data Starts from `F75 to H84`    
Customer    CUST    Phone number
Smith   Smith1  320-966-4023
Smith   Smith2  686-612-7782
Jason   Jason1  122-617-7154
Albert  Albert1 547-436-7376
Nancy   Nancy1  956-633-7322
Smith   Smith3  132-716-5240
Grove   Grove1  340-267-0529
Andy    Andy1   531-413-4718
Jason   Jason2  613-228-4294
Nancy   Nancy2  272-525-2042

最后第 n 次查找:

例如客户第 4 次出现的电话号码 = Smith

=VLOOKUP($D$74&"4",$G$75:$H$93,2,FALSE)

Lookup  
Customer    Smith
Phone number    
1st 320-966-4023
2nd 686-612-7782
3rd 132-716-5240
4th 185-813-8883

Reference from Chandoo: 4. Lookup 2nd / 3rd / 4th occurrence of an item in a list .

• 方法 2:用于以下公式的示例数据:

公式:

=INDEX(ALTable,SMALL(IF(OFFSET(ALTable,0,0,ROWS(ALTable),1)=F90,
ROW(OFFSET(ALTable,0,0,ROWS(ALTable),1))-ROW(OFFSET(ALTable,0,0,1,1))+1,
ROW(OFFSET(ALTable,ROWS(ALTable)-1,0,1,1))+1),F91),2)

Reference from CPearson Arbitary Lookups .我个人不喜欢volatile functions例如 index()...虽然..