vlookup函数的设计只返回一个查找值的第一个实例的对应值。但是有些工作需要返回多个符合条件的值，下面我们来看下面几种情况：
1. 垂直返回多个值
C8单元格里的数组公式：
=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, “”), ROW(A1)))
还有一个普通常见的但更复杂的公式：
=INDEX($C$2:$C$5,SMALL(INDEX(($B$8=$B$2:$B$6)*(MATCH(ROW($B$2:$B$6),ROW($B$2:$B$6)))+($B$8<>$B$2:$B$6)*1048577, 0, 0), ROW(A1)))
输入以后往下拉公式，得到上面图片的结果
2.水平返回多个值
C9单元格里的数组公式：
=INDEX($C$2:$C$6, SMALL(IF($B$9=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, “”), COLUMN(A1)))
还有一个普通常见的但更复杂的公式：
=INDEX($C$2:$C$5,SMALL(INDEX(($B$9=$B$2:$B$6)*(MATCH(ROW($B$2:$B$6),ROW($B$2:$B$6)))+($B$9<>$B$2:$B$6)*1048577, 0, 0), COLUMN(A1)))
输入以后往右拉公式，得到上面图片的结果
3.返回多条记录值
A10单元格的数组公式：
=INDEX($A$2:$C$7,SMALL(IF($B$9=$A$2:$A$7,ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, “”), ROW(A1)),COLUMN(A1))
复制单元格A10，粘贴单元格B10：C10。然后复制并粘贴到A10：C10单元格A11：C12。
还有一个普通常见的但更复杂的公式：
=INDEX($A$2:$C$7,SMALL(INDEX(($B$9=$A$2:$A$7)*(MATCH(ROW($A$2:$A$7),ROW($A$2:$A$7)))+($B$9<>$A$2:$A$7)*1048577, 0, 0),ROW(A1)),COLUMN(A1))
How to create an array formula
4.怎样创建数组公式
复制 (Ctrl + c) 和 粘贴 (Ctrl + v) 数组公式到公式栏.
按住组合键不松手Ctrl + Shift.
再按Enter键
释放所有按键
怎样去掉#num的错误
数组公式
=IFERROR(array_formula, “”)
解释垂直返回值的数组公式
Step1-确定哪些单元格和查找值相匹配
=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, “”), ROW(A1)))
"=" 是个比较操作符 ,核对检查$B$8的值是否等于数组区域($B$2:$B$6)的值，这个操作符对大小写不敏感。
$B$8=$B$2:$B$6
上面的语句，第一步运算后的结果是：
“Pen“={“Pen“, “Eraser”, “Paper”, “Pen“, “Paper Clip”}
第二步运算后的结果是：
{“Pen“=”Pen“, “Pen“=”Eraser”, “Pen“=”Paper”; “Pen“=”Pen“, “Pen“=”Paper Clip”}
第二步运算后的结果是：
{TRUE, FALSE, FALSE, TRUE, FALSE}
Step2-创建含有行号的数组
=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, “”), ROW(A1)))
ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1
上面的语句，第一步运算后的结果是：
{2,3,4,5,6} – MIN({2,3,4,5,6})+1
第二步运算后的结果是：
{2,3,4,5,6} – 2+1
第三步运算后的结果是：
{2,3,4,5,6} – 1
最后的结果是：
{1,2,3,4,5}
Step3-筛选出等于查找值的行号
=INDEX($C$2:$C$6, SMALL(IF({TRUE,FALSE,FALSE,TRUE,FALSE},{1,2,3,4,5} , “”), ROW(A1)))
运算的结果是：
=INDEX($C$2:$C$6, SMALL({1,””,””,4,””}, ROW(A1)))
Step 4 – Return the k-th smallest row number
Step4-返回第k个最小的行数
=INDEX($C$2:$C$6, SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, “”), ROW(A1)))
SMALL(IF($B$8=$B$2:$B$6, ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, “”), ROW(A1))
运算后的结果：
SMALL({1,””,””,4,””}, ROW(A1))
从数组(1,””,””,4,””)返回第K个最小的行数
返回第几个最小的行数用ROW（A1）来生成
C8单元格的公式复制到C9单元格里, ROW(A1)变成了 ROW(A2). ROW(A2) 等于2.
C8单元格里的公式:
=INDEX($C$2:$C$6, SMALL((1,””,””,4,””), ROW(A1)))
=INDEX($C$2:$C$6, SMALL((1,””,””,4,””), 1))
数组里 (1,””,””,4,””)的最第个最小的数是 1.
C8单元格里的公式::
=INDEX($C$2:$C$6, SMALL((1,””,””,4,””), ROW(A2)))
数组里 (1,””,””,4,””)的第2个最小的数是 4
Step 5 – Return value in range
Step5-返回范围的值
INDEX(array,row_num,[column_num])
返回给定的数组范围内行号和列号交叉的值
In Cell C8:
=INDEX($C$2:$C$6,1)
运算后的结果：
=INDEX({1.5,2,1,1.7,3}, 1)
返回结果是： $1.50
In Cell C9:
=INDEX($C$2:$C$6,4)
返回结果是：$1,70:
文章所有函数的说明：
IF(logical_test;[value_if:true];[value_if_false])
根据指定的条件来判断其"真"(TRUE)、"假"(FALSE)，根据逻辑计算的真假值，从而返回相应的内容。可以使用函数 IF 对数值和公式进行条件检测。
SMALL(array,k)
返回数据组中的第K个最小值。
INDEX(array,row_num,[column_num])
返回指定的行与列交叉处的单元格引用。
MIN(number1,[number2])
此公式的作用是返回给定参数表中的最小值。忽略逻辑值和文本。
ROW(reference)
Reference 为需要得到其行号的单元格或单元格区域。
如果省略 reference，则假定是对函数 ROW 所在单元格的引用。
vba自定义函数水平或垂直返回多个值
在你输入数组公式前，请确保已经把下面的代码复制到一个标准模块里
=vbaVlookup(lookup_value, table_array, col_index_num, [h])
h is optional, h= return values horizontally
h 是个可选的参数，如果写入h参数，则水平返回值，否则，则垂直返回值
C9:C11单元格区域的数组公式是：
=vbaVlookup(B9, B2:C6, 2)
怎样输入自定义函数？
选中C9：C11单元格区域
输入上面的自定义函数
按下 Ctrl + Shift不松手
再按一次Enter键
松手
C14:D14:单元格区域的数组公式是：
=vbaVlookup(B14, $B$2:$C$6, 2, “h”)
怎样输入自定义函数？
选中C14:D14单元格区域
输入上面的自定义函数
按下 Ctrl + Shift不松手
再按一次Enter键
松手
怎样复制数组公式到下一行
选中C14:D14单元格区域
复制选中区域
选中 C15:D15单元格区域
粘贴
Vba 代码
复制下面的代码
按下组合键 Alt + F11
插入1个标准模块
4.粘贴代码到模块里
5.返回到Excel
Option Explicit
Function vbaVlookup(lookup_value As Range, tbl As Range, col_index_num As Integer, Optional layout As String = "v")
Dim r As Single, Lrow, Lcol As Single, temp() As Variant
ReDim temp(0)
For r = 1 To tbl.Rows.Count
If lookup_value = tbl.Cells(r, 1) Then
temp(UBound(temp)) = tbl.Cells(r, col_index_num)
ReDim Preserve temp(UBound(temp) + 1)
End If
Next r
If layout = "h" Then
Lcol = Range(Application.Caller.Address).Columns.Count
For r = UBound(temp) To Lcol
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
ReDim Preserve temp(UBound(temp) - 1)
vbaVlookup = temp
Else
Lrow = Range(Application.Caller.Address).Rows.Count
For r = UBound(temp) To Lrow
temp(UBound(temp)) = ""
ReDim Preserve temp(UBound(temp) + 1)
Next r
ReDim Preserve temp(UBound(temp) - 1)
vbaVlookup = Application.Transpose(temp)
End If
End Function