Làm thế nào để tra cứu giá trị trả về nhiều giá trị tương ứng trong Excel?
Hướng dẫn này nói về việc tìm kiếm một giá trị trả về nhiều giá trị tương ứng trong Excel như được hiển thị như ảnh chụp màn hình bên dưới:
Giá trị tra cứu trả về nhiều giá trị tương ứng với công thức mảng
Giá trị tra cứu trả về nhiều giá trị tương ứng với Bộ lọc
Giá trị tra cứu trả về nhiều giá trị tương ứng với Hàm xác định
Giá trị tra cứu trả về nhiều giá trị tương ứng với công thức mảng
Đây là một công thức mảng dài có thể giúp ích trong việc tìm kiếm một giá trị và trả về nhiều giá trị tương ứng.
1. Nhập giá trị bạn muốn tra cứu vào ô trống. Xem ảnh chụp màn hình:
2. Trong ô liền kề, nhập công thức này =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),"",
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)) vào nó và nhấn Shift + Ctrl + Nhập các phím với nhau, sau đó kéo Tự động điền để điền vào các ô cho đến khi ô trống đầu tiên xuất hiện. Xem ảnh chụp màn hình:
Lưu ý: Trong công thức trên, $ A $ 1: $ B $ 7 cho biết dữ liệu phạm vi, $ A $ 1: $ A $ 7 là viết tắt của dải cột mà bạn tìm kiếm giá trị nhất định, $ D $ 4 cho biết ô bạn nhập tìm kiếm giá trị trong bước 1, 2 cho biết việc tìm các giá trị tương ứng trong cột thứ hai.
Giá trị tra cứu trả về nhiều giá trị tương ứng với Bộ lọc
Trong Excel, bạn cũng có thể sử dụng tính năng Bộ lọc để giải quyết vấn đề này.
1. Chọn phạm vi cột bạn muốn tra cứu giá trị và nhấp vào Ngày > Lọc. Xem ảnh chụp màn hình:
2. Sau đó, nhấp vào nút mũi tên trong ô đầu tiên của dải ô bạn đã chọn và kiểm tra giá trị bạn chỉ muốn tra cứu trong danh sách thả xuống. Xem ảnh chụp màn hình:
3. nhấp chuột OK, bây giờ bạn chỉ thấy giá trị tra cứu và các giá trị tương ứng của nó được lọc ra.
Giá trị tra cứu trả về nhiều giá trị tương ứng với Hàm xác định
Nếu bạn quan tâm đến Hàm xác định, bạn cũng có thể giải quyết vấn đề với Hàm đã xác định.
1. nhấn Alt + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. nhấp chuột Mô-đun > Chèn để chèn một Mô-đun và sao chép VBA bên dưới vào cửa sổ.
VBA: Giá trị tra cứu trả về nhiều giá trị tương ứng.
Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
If pWorkRng = pRng.Cells(i, 1) Then
arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
ReDim Preserve arr(UBound(arr) + 1)
End If
Next
If pType = "h" Then
xCol = Range(Application.Caller.Address).Columns.Count
For i = UBound(arr) To xCol
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function
3. Đóng cửa sổ và nhập công thức này vào một ô = MyVlookup (A10, $ A $ 2: $ B $ 7,2) (A10 cho biết giá trị tra cứu, $ A $ 2: $ B $ 7 cho biết phạm vi dữ liệu, 2 cho biết số chỉ mục cột). Và hãy nhấn Shift + Ctrl + Nhập chìa khóa. Sau đó, kéo chốt điền xuống các ô, đặt con trỏ vào Thanh công thức, và hãy nhấn Shift + Ctrl + Nhập một lần nữa.
Mẹo: Nếu bạn muốn trả về các giá trị trong các ô ngang, bạn có thể nhập công thức này = MyVlookup (A10, $ A $ 2: $ B $ 7, 2, "h").
Công cụ năng suất văn phòng tốt nhất
Nâng cao kỹ năng Excel của bạn với Kutools for Excel và trải nghiệm hiệu quả hơn bao giờ hết. Kutools for Excel cung cấp hơn 300 tính năng nâng cao để tăng năng suất và tiết kiệm thời gian. Bấm vào đây để có được tính năng bạn cần nhất...
Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều
- Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
- Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
- Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
