Cách tra cứu nhiều trường hợp của một giá trị trong Excel
Nếu bạn đang ở đây, điều đó có nghĩa là bạn đang cố gắng sử dụng hàm VLOOKUP để truy xuất nhiều giá trị từ một tập dữ liệu. Nhưng hãy nói thẳng, hàm VLOOKUP KHÔNG THỂ TRẢ LẠI NHIỀU GIÁ TRỊ. Nhưng điều đó không có nghĩa là chúng tôi không làm được. Chúng tôi có thể thực hiện tra cứu để truy xuất nhiều giá trị thay vì chỉ một giá trị đầu tiên.
Công thức chung
{=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(first cell of lookup_value_range)+1),ROW(1:1)))}
Mảng: Phạm vi từ nơi bạn muốn tìm nạp dữ liệu.
lookup_value: lookup_value của bạn mà bạn muốn lọc.
lookup_value_range: Phạm vi mà bạn muốn lọc lookup_value.
Ô đầu tiên trong phạm vi lookup_value: nếu phạm vi lookup_value của bạn là $ A $ 5: $ A $ 100 thì ô đó là $ A $ 5.
Nhập nó dưới dạng công thức mảng. Sau khi viết công thức, nhấn tổ hợp phím CTRL + SHIFT + ENTER để biến nó thành công thức mảng.
Ví dụ về tra cứu nhiều kết quả Tôi có dữ liệu sinh viên này trong phạm vi A2: E14. Trong ô G1, tôi có danh sách thả xuống các giá trị vùng, ví dụ: Trung tâm, Đông, Bắc, Nam và Tây.
Bây giờ tôi muốn, bất kỳ khu vực nào tôi có trong G1, danh sách tất cả học sinh từ khu vực đó sẽ được hiển thị trong cột H.
Để tra cứu nhiều giá trị trong excel, hãy xác định các biến của chúng tôi.
Mảng: $ C $ 2: $ C $ 14 lookup_value: $ G $ 1 lookup_value_range: $ A $ 2: $ A $ 14 Ô đầu tiên của dải lookup_value: $ A $ 2 Theo dữ liệu trên, công thức để lấy nhiều giá trị trong excel sẽ là:
{=INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1)))}
Sao chép công thức này trong H2 và nhấn CTRL + SHIFT + ENTER. Bây giờ, hãy kéo công thức này xuống cho đến khi bạn gặp lỗi #NUM. #NUM cho biết rằng không còn giá trị phù hợp nào khác với giá trị lookup_value đó và nó là giá trị cuối của danh sách.
Nếu #NUM làm phiền bạn, bạn có thể có liên kết: / logic-công thức-excel-iferror-function [hàm IFERROR] ở phía trước công thức và hiển thị một số thông tin có ý nghĩa thay vì lỗi.
{=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),ROW(1:1))),"--List Ends--")}
Điều này sẽ hiển thị – Danh sách kết thúc – khi tất cả các mục được hiển thị.
Bây giờ chúng ta hãy hiểu CÁCH NÓ hoạt động.
Mặc dù công thức có thể trông phức tạp, nhưng ý tưởng rất đơn giản. Chúng ta cần lấy số chỉ mục của mỗi lần xuất hiện giá trị, sau đó truy xuất các giá trị bằng cách sử dụng liên kết: / tra cứu-công thức-excel-index-function [hàm INDEX] của Excel.
chức năng. Công thức thực sự phức tạp hoàn toàn, hãy chia nhỏ nó.
IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14): Bây giờ, phần này trả về số hàng nếu giá trị của bộ đếm ô là G1 (trung tâm) trong phạm vi $ A $ 2: $ A $ 14 khác trả về FALSE. Trong ví dụ này, nó sẽ trả về
\ {2; FALSE; FALSE; FALSE; FALSE; 7; 8; FALSE; FALSE; 11; FALSE; FALSE; FALSE}.
-
Bây giờ, vì mảng trên chứa số hàng từ hàng đầu tiên (1: 1) và chúng ta cần hàng bắt đầu từ mảng của chúng ta (A2: A14). Để làm như vậy, chúng ta sử dụng -ROW ($ A $ 2) +1 trong công thức IF. Điều này sẽ trả về một số hàng trước khi bắt đầu mảng của chúng ta.
Đối với ví dụ này, nó là -1. Nếu nó bắt đầu từ A3, nó sẽ trả về -2, v.v. Điều này số sẽ bị trừ cho mỗi số trong mảng do IF trả về. Vì vậy, cuối cùng IF ($ G $ 1 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1) điều này sẽ dịch thành \ {1; FALSE; FALSE; FALSE; FALSE; 6; 7; FALSE; FALSE; 10; FALSE; FALSE; FALSE}.
ROW (1: 1) sẽ trả về 1. Do đó, hàm trên sẽ trả về giá trị nhỏ nhất đầu tiên trong mảng, là 2.
Khi bạn sao chép công thức này trong các ô bên dưới, ROW (1: 1) sẽ trở thành ROW (2: 2) và nó sẽ trả về giá trị nhỏ thứ 2 trong mảng, là 7, v.v. Điều này cho phép hàm trả về giá trị tìm thấy đầu tiên trước tiên. Nhưng nếu bạn muốn nhận giá trị tìm thấy cuối cùng trước tiên thì hãy sử dụng Hàm LARGE thay vì hàm NHỎ.
Giờ đây, bằng cách sử dụng các giá trị được trả về ở các hàm trên, hàm INDEX dễ dàng trả về từng giá trị phù hợp từ một phạm vi.
Vì vậy, các bạn có thể LOOKUP nhiều giá trị tương ứng bằng một giá trị tra cứu. Trong gif ở trên, tôi đã sử dụng hàm IFERROR để bắt lỗi và định dạng có điều kiện để làm cho nó trực quan hơn một chút. Trong ví dụ trên, tôi đã sử dụng nhiều hàm và kỹ thuật khác mà tôi đã giải thích trong các bài viết khác.