Excel 2010:VLOOKUP具有多个结果 [英] Excel 2010: VLOOKUP with multiple result
问题描述
我有以下数据:
示例:
A B C
EmployeeID EmployeeName EmployeeSalary
-------------------------------------------
E101 JAK 20000
E102 SAM 25000
E103 John 20000
E104 Shawn 30000
我有一个单元格 H1 ，在其中键入员工的薪水，在下面的单元格中，该单元格是 H2，I2，J2 ，应根据以下信息列出员工详细信息到 H1 单元格中给定的薪水.
I have the cell H1 in which i type salary of the employee, and in the below cell that is cell H2, I2, J2 should list the employee details according to the given salary in the cell H1.
我为此使用了 VLOOKUP 函数.
对于单元格H2:
=IFERROR(VLOOKUP(H1,C2:A5,1,FALSE),"EmployeeID not found")
对于单元格I2:
=IFERROR(VLOOKUP(H1,C2:B4,2,FALSE),"EmployeeName not found")
对于单元格J2:
=IFERROR(VLOOKUP(H1,C2:C4,3,FALSE),"EmployeeSalary not found")
注意:上面的方法可以很好地显示单个结果，但是当我输入 20000 时，它只会显示单个记录，而并非所有满足给定条件的记录.
Note: The above works fine for single result to display but when i enter 20000 it will only show single record NOT all which meet the given criteria.
推荐答案
有三种处理方法:
首先是公式:
我将字段设置为这样，通过另一种方法将很明显:
I set up the Field as such which will become apparent with another method:
因此，在J4中，我输入了以下公式:
So in J4 I put the following formula:
=IFERROR(AGGREGATE(14,6,$C$2:INDEX(C:C,MATCH(1E+99,C:C))/($C$2:INDEX(C:C,MATCH(1E+99,C:C))=$H$2),ROW(1:1)),"")
在H4中，我输入:
=IF($J4<>"",INDEX(A$2:INDEX(A:A,MATCH(1E+99,$C:$C)),AGGREGATE(15,6,(ROW($C$2:INDEX($C:$C,MATCH(1E+99,$C:$C)))-1)/($C$2:INDEX($C:$C,MATCH(1E+99,$C:$C))=$J4),COUNTIF($J$4:$J4,$J4))),"")
然后我将其拖到I4.然后将所有三个公式向下拖动，直到确定您已涵盖所有可能的结果.
Which I then drag across to I4. Then drag all three formulas down till you are sure you have covered all the possible results.
这是非CSE数组公式.数组公式的计算是指数的，因此我们需要将参考范围限制为所需的最小值.所有 INDEX($ C:$ C，MATCH(1E + 99，$ C:$ C))都找到包含数据的最后一个单元格并将其设置为结束引用.
This is a non CSE array formula. Array formulas calculations are exponential, so we need to limit the reference range to the minimum needed. All the INDEX($C:$C,MATCH(1E+99,$C:$C)) finds the last cell with data and sets this as the end reference.
第一个上的 IFERROR()包装器允许将公式向下复制，使其比列表将返回的位置更远，并避免使用#N/A .在图片中，公式占据了前8行.
The IFERROR() wrapper on the first allows the formula to be copied down further than the list will return and avoid the #N/A. In the picture the formulas occupy the first 8 rows.
第二，我们使用高级过滤器:
Second we use the Advanced Filter:
首先，我们像这样设置H1周围的区域:
First we set up the area around H1 like this:
然后，我们导航至数据"选项卡上的高级过滤器".弹出窗口:
Then we navigate to Advanced Filter which is on the Data tab. This window pops open:
然后我们输入信息:
将副本"标记为另一个位置.列表范围是 $ A $ 1:$ C $ 5 条件范围为 $ A $ 1:$ C $ 5 复制到范围是 $ H $ 3:$ J $ 3
Mark the Copy to another location.
List Range is $A$1:$C$5
Criteria Range is $A$1:$C$5
Copy to range is $H$3:$J$3
然后按一下.
第三个是模仿高级过滤器的vba:
The third is vba which mimic the Advanced Filter:
Sub atfilt()
Dim ws As Worksheet
Dim rng As Range
Dim critrng As Range
Dim cpytorng As Range
Dim lstrow As Long
Set ws = Sheets("Sheet9")
lstrow = ws.Range("A" & ws.Rows.Count).End(xlUp).row
Set rng = ws.Range("A1:C" & lstrow)
Set critrng = ws.Range("H1:H2")
Set cpytorng = ws.Range("H3:J3")
rng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=critrng, CopyToRange:=cpytorng, Unique:=False
End Sub
每个都有其缺点:
Each has their disadvantages:
公式:如果数据集很大(1,000行或更多)，则计算将很长.
Formula: If the data set is large then(1,000 rows or more) the calculations will be long.
高级过滤器:每次需要新过滤器时，都必须重做每个步骤.这不是自动的.
Advanced Filter: Each step must be redone each time a new filter is wanted. It is not automatic.
VBA:它是VBA，需要对如何使用它有一定的了解.
VBA: It is VBA and requires a certain understanding on how to use it.
这篇关于Excel 2010:VLOOKUP具有多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
