Bài viết dưới đây chúng ta sẽ cùng nhau tìm hiểu về cách sử dụng hàm tìm kiếm có điều kiện trong Excel nhé! Show
Cách dùng hàm INDEX/MATCH
Nếu với VLOOKUP, bạn có công thức sau: Bạn có ví dụ sau như hình bên dưới, với hàm VLOOKUP bạn lưu ý những vấn đề sau:
Và lúc này bạn sẽ có công thức: =VLOOKUP(E2, $A$1:$C$8, 3, 0). Với ô E2 là giá trị cần tìm, nằm tại vùng dữ liệu từ A1:C8, và dấu $ có ý nghĩa cố định vùng dữ liệu tìm kiếm để khi thực hiện kéo công thức vùng thì sẽ cố định. Cột dữ liệu trả về là cột thứ 3, tính từ vị trí đếm của cột chứa giá trị dò tìm sang bên phải. Và số 0, có nghĩa là tìm chính xác, luôn luôn là số 0. Vậy với hàm INDEX thì sẽ có thay đổi như thế nào? Lúc này, bạn sẽ thấy rằng hàm có cú pháp: =INDEX(Vùng kết quả, Dòng, Cột).
Vì sao sử dụng INDEX/MATCH lại dễ hơn VLOOKUP?Hàm VLOOKUP đòi hỏi cột có chứa giá trị dò tìm phải nằm ngoài cùng, phía bên trái vùng dữ liệu. Nếu như cột nằm bên phải thì lúc này phải sử dụng hàm mảng kết hợp với hàm CHOOSE để tiến hành lấy kết quả tương ứng. Cùng theo dõi ví dụ bên dưới, bạn chỉ việc thực hiện quét vùng chọn cột kết quả, rồi tìm trong cột chứa giá trị dò tìm. Vậy là xong! Ví dụ 2: VLOOKUP kết hợp CHOOSE để thực hiện dò tìm ngượcKhác với ví dụ trước đó, bạn có cột Lớp nằm ngoài cùng phía bên trái, và bài toán là từ tên Học viên, bạn sẽ thực hiện tìm ra lớp của Học viên đó. Cách làm như sau, bạn phải sử dụng kết hợp hàm CHOOSE, có cú pháp =CHOOSE({1,2}, Cột chứa giá trị dò tìm, Cột Kết quả). Vậy là bạn đã có cú pháp tổng quát sau: =VLOOKUP(Giá trị dò tìm, CHOOSE({1,2}, Cột chứa giá trị dò tìm, Cột kết quả), Cột trả về[2], Tìm chính xác [0]) Nếu như dấu phân cách của bạn là dấu chấm phẩy, thì lúc này công thức sẽ là: =VLOOKUP(Giá trị dò tìm; CHOOSE({1 \ 2}; Cột chứa giá trị dò tìm; Cột kết quả); Cột trả về[2]; Tìm chính xác [0]) Với hàm INDEX/MATCH thì bạn thấy vẫn như ví dụ 1, =INDEX(Vùng kết quả, MATCH(Giá trị dò tìm, Vùng dò tìm, Tìm chính xác). Qúa đơn giản đúng không nào? Sử dụng hàm VLOOKUP/CHOOSE hoặc là hàm INDEX/MATCH để tìm theo nhiều điều kiệnBạn có ví dụ như hình minh họa bên trên, lúc này có 2 bạn “Nguyễn Thị Đét” là cùng tên học ở 2 lớp khác nhau, tương đương với 2 điều kiện để bạn thực hiện tìm ra điểm thi của từng bạn. Cách làm sẽ là, vẫn là hàm VLOOKUP/CHOOSE, bạn sẽ ghép 2 điều kiện này lại với nhau bằng dấu & (dấu “and”/”và”), cùng với việc ghép 2 cột có chứa giá trị dò tìm với nhau cùng với dấu &. Bạn sẽ có cú pháp như sau: =VLOOKUP([Giá trị dò A]&[Giá trị dò tìm B]&[Giá trị dò tìm n], CHOOSE({1, 2}, [Vùng cột chứa giá trị A]&[Vùng cột chứa giá trị B]&[Vùng cột chứa giá trị n], [Vùng cột kết quả]), 2 là Cột trả về, 0 là Tìm chính xác) Và trên đây là công thức mảng, đòi hỏi bạn phải nhấn tổ hợp phím CTRL+SHIFT+ENTER, thay cho việc nhấn Enter (trả về #NA), lúc này bạn sẽ thấy được có móc sừng trâu xuất hiện trong công thức. Tại sao lại dùng hàm MATCH lúc tìm 1, lúc lại là TRUE và khi nào phải nhấn CTRL+SHIFT+ENTER?Ví dụ 3: Hàm INDEX/MATCH tìm kiếm theo nhiều điều kiệnVới hàm INDEX/MATCH, bạn có cú pháp sau: =INDEX(Vùng kết quả, MATCH(1,([Giá trị dò tìm A]=[Vùng cột giá trị A])*([Giá trị dò tìm B]=[Vùng cột giá trị B]*([Giá trị dò tìm n]=[Vùng cột giá trị n]),0) Và đây sẽ là công thức mảng, nên bạn phải nhấn tổ hợp phím CTRL+SHIFT+ENTER. Vậy vì sao lúc lại là 1, lúc lại TRUE? Khi bạn chỉ có 1 biểu thức, thì lúc này kết quả sẽ được trả về TRUE/FALSE, còn nếu khi có 2 biểu thức TRUE*TRUE, Excel thì sẽ chuyển TRUE thành 1*1 = 1.
Xem thêm: Hướng dẫn cách xuống dòng trong 1 ô của Excel Sử dụng hàm INDEX/MATCH kết hợp INDEX để thực hiện chuyển đổi công thức mảng thành công thức bình thường.Trong ví dụ 3, bạn sẽ được làm quen với công thức mảng, đòi hỏi phải nhấn tổ hợp phím CTRL+SHIFT+ENTER, để tránh việc phải thực hiện thao tác này, bạn có thể kết hợp thêm với hàm INDEX ở bên trong hàm MATCH và để trả về giá trị đầu tiên trong danh sách mà hàm MATCH tìm thấy. Ví dụ 4: Hàm INDEX/MATCH dò tìm theo nhiều điều kiệnVới cú pháp từ ô G7, G4 ở ví dụ trên, bạn sẽ có:
Làm báo cáo chi tiết, trích lọc hóa đơn với hàm INDEX kết hợp hàm COUNTIFS. Kết quả trả về nhiều kết quả từ một hoặc là nhiều điều kiện.Ví dụ 5: Tiến hành tìm kiếm trả về nhiều kết quảĐối với giá trị dò tìm “Nguyễn Thị Đét” bạn có nhiều kết quả trả về và cách để liệt kê tất cả kết quả đó chính là sử dụng cú pháp (0=COUNTIFS([$[Ô đầu tiên trả về kết quả]:[Ô đầu tiên trả về kết quả]], Vùng kết quả), trong ví dụ ta có: (0=COUNTIFS($F$1:F1,$B$1:$B$8)). Nghĩa là: Cần xét thêm điều kiện đã trả về kết quả trước đó hay chưa? Nếu như đã trả về kết quả rồi thì cần loại trừ để lấy cái tiếp theo. Lúc này bạn sẽ có được kết quả mong muốn.
Làm thế nào để tiến hành in phiếu xuất kho có nhiều sản phẩm? Cùng một phiếu nhưng có nhiều mã sản phẩm khác nhauVí dụ đối với 1 mã xuất kho, bạn sẽ tiến hành xuất ra nhiều sản phẩm khác nhau. Lúc này, khi thực hiện in phiếu xuất kho, bạn chỉ việc tiến hành nhập mã phiếu xuất kho, thì sẽ trả về danh sách sản phẩm tương ứng. Bạn lập cột phụ tham chiếu theo mã phiếu xuất kho, và lúc này hàm COUNTIFS sẽ làm nhiệm vụ đánh số thứ tự giúp bạn. Vẫn là bạn sẽ cột dây vào 1 đầu cột, dây còn lại sẽ thả tự do để diều bay cao: $A$2:A2 => COUNTIF($A$2:A2, $E$9), và $E$9 là giá trị dò tìm, cũng cần phải cố định để thực hiện khi kéo xuống để không làm thay đổi điều kiện tìm kiếm. Lúc này, bạn sẽ thấy rằng số tăng dần theo vùng Mã phiếu xuất kho, nếu như không tìm thấy nữa, thì lúc đó chỉ là lặp lại cái cuối cùng tìm thấy. Lúc này bạn trở về Sheet Phiếu Xuất Kho để in ấn, chỉ cần lập công thức tương ứng như sau, bài viết này giải thích từ trái sang phải:
Hi vọng bài viết trên đây sẽ giúp bạn hiểu hơn về các hàm tìm kiếm có điều kiện trong Excel, chúc bạn thực hiện thành công! Xem thêm: Hướng dẫn làm tròn đến hàng nghìn trong Excel |