Google 表格 - ARRAYFORMULA 中的 VLOOKUP [英] Google sheets - VLOOKUP inside ARRAYFORMULA
问题描述
我正在尝试在 Google 表格中的数组公式中执行 Vlookup.
I am trying to do Vlookup inside an Array formula in Google Sheets.
我有两张纸，S1 和 S2.
I have two sheets, S1 and S2.
每个都有 3 个相同的列，Col A(日期)、Col B(名称)和 Col C(付款类型)
Each has 3 identical columns, Col A (Date), Col B(Name) and Col C(Payment-Type)
我想根据日期和姓名组合匹配将付款类型从工作表 2 读取到工作表 1.意思是如果日期和名称在工作表 2 中匹配，则返回付款类型.
I would like to read the payment type from sheet 2 into sheet 1 based upon date and name combination match. Meaning if the date and name matches in sheet 2, return the Payment Type.
这是我的 Vlookup 公式，
Here is my Vlookup formula,
=VLOOKUP(A2,FILTER('S2'!A2:C7,'S2'!A2:A7 = A2,'S2'!B2:B7 = B2),3,FALSE)
以上公式解释:
S1 是工作表 1，S2 是工作表 2
S1 is sheet 1 and S2 is sheet 2
A2 <- 表 1 中的日期
A2 <- Date from sheet 1
'S2'!A2:C7 <- 整个工作表 2 范围
'S2'!A2:C7 <- Entire sheet 2 range
'S2'!A2:A7 = A2 <- 将工作表 2 日期与工作表 A2 中的日期进行比较1
'S2'!A2:A7 = A2 <- Comparing sheet 2 dates with Date in A2 of sheet 1
'S2'!B2:B7 = B2 <- 将工作表 2 名称与工作表 B2 中的名称进行比较1
'S2'!B2:B7 = B2 <- Comparing sheet 2 names with Name in B2 of sheet 1
3 <- 返回第三列值，如果日期和姓名匹配.
3 <- Returning the third column value which is Payment Type if the date and name match.
这很好用.以下是包含示例的电子表格链接:
This works well. Here is a link to the spreadsheet with an example:
https://docs.google.com/spreadsheets/d/1tIlq_kBWlM1Stj_Iqoua2LswW2IUl7TBbhnFFhPX4uo/edit?usp=sharing
我想用第二行中的一个数组公式替换所有行上的 Vlookup 公式.
I would like to replace the Vlookup formulas on all rows with just one Array formula in the second row.
这就是我现在所拥有的，但不起作用.
This is what I have now which is not working.
=ARRAYFORMULA(VLOOKUP($A$2:$A$7,FILTER('S2'!A$2:C$7,'S2'!$A$2:A7 = $A$2:$A$7,'S2'!$B$2:B7 = $B$2:B7),3,FALSE))
请问，有人能告诉我这里有什么问题吗?我对这些公式很差.请参考我上面附上的表格.这将向您解释一切.
Please, can someone tell me what is wrong here? I am pretty poor with these formulas. Kindly refer the sheet I attached above. That will explain everything to you.
推荐答案
去掉C列的所有内容再进入C1
Remove all contents of column C and then enter in C1
={"Payment Type"; ARRAYFORMULA(IF(LEN(A2:A), VLOOKUP($A2:A&B2:B,{'S2'!$A$2:A7&'S2'!$B$2:B7, 'S2'!$C$2:C7},2,FALSE),))}
看看是否有效?
这篇关于Google 表格 - ARRAYFORMULA 中的 VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
