Cách dùng hàm tìm kiếm có điều kiện trong Excel

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é!

Cách dùng hàm INDEX/MATCH

• Cú pháp thường gặp, thường được dùng với VLOOKUP:

Nếu với VLOOKUP, bạn có công thức sau:
=VLOOKUP(Giá trị dò tìm, Vùng dữ liệu [có: Cột dò tìm + Cột kết quả], Thứ tự cột trả về, Tìm chính xác/gần đúng)

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:

• Giá trị dò tìm ở đây là “Huỳnh Văn Vê“, có tại cột vùng từ A1:A8, và cột chứa đó luôn luôn nằm phía bên trái ngoài cùng của vùng dữ liệu (A1:C8).
• Vùng dữ liệu: A1:C8, khi bạn tiến hành quét vùng dữ liệu luôn phải nhớ rằng bạn sẽ phải cố định với phím F4, trước khi làm thực hiện bất cứ việc gì tiếp theo: $A$1:$C$8. Lí do là vì khi bạn thực hiện kéo công thức, vùng tham chiếu dữ liệu lúc đó sẽ luôn được cố định.
• Cột trả về, bạn đếm theo thứ tự từ trái quan phải, tính từ vị trí cột chứa giá trị dò tìm. Trong ví dụ bên dưới là cột thứ 3.
• Bạn cũng luôn nhớ rằng, khi nhập số 0 cuối cùng trong hàm VLOOKUP và hàm MATCH. Lí do là vì số 0 tương ứng với FALSE, tức là tìm kiếm chính xác, luôn luôn tìm kiếm chính xác. Vậy tại sao không tìm tương đối? Có một vài trường hợp bạn sẽ tìm tương đối, gần đúng, nhưng lúc đó là vài trường hợp khi bạn đã nắm rõ.

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?
=INDEX(Cột kết quả, MATCH(Giá trị dò tìm, Cột dò tìm chứa giá trị cần tìm, Tìm chính xác/gần đúng)

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ùng kết quả: $C$1:$C$8, sẽ khác với vùng dữ liệu của hàm VLOOKUP, lúc này bạn chỉ có thể chọn mỗi vùng dữ liệu cột Điểm thi thay cho cả cột chứa giá trị dò tìm.
• Hàm MATCH(Giá trị dò tìm, Vùng tìm kiếm, Tìm chính xác/tương đối). Ô E3 lúc này là giá trị dò tìm, và vùng tìm kiếm chỉ là đúng vùng cột có chứa giá trị dò tìm: A1:A8, tương tự như với hàm VLOOKUP, vùng tìm kiếm luôn phải được cố định vùng $A$1:$A$8. Và sẽ luôn luôn tìm chính xác, là số 0 hoặc là FALSE.
• Với công thức trên, bạn có thể thấy hàm MATCH sẽ trả về giá trị là 2, tương ứng với dòng tìm thấy từ trên xuống. INDEX($C$1:$C$8, 2) => Kết quả sẽ là 7.

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!

Khá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ện

Bạ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ớ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.

• Giá trị dò tìm là TRUE, xảy ra khi có một biểu thức: (Biểu thức so sánh) => ([Giá trị A]=[Vùng cột chứa giá trị A])
• Giá trị dò tìm là 1, xảy ra khi có hai biểu thức trở lên: (Biểu thức 1)*(Biểu thức 2) => ([Giá trị A]=[Vùng cột chứa giá trị A])*([Giá trị B]=[Vùng cột chứa giá trị B])*([Giá trị n]=[Vùng cột chứa giá trị n])
• Là FALSE lúc nào? Lúc bạn cần tìm giá trị và không thỏa mãn theo biểu thức so sánh của mình.

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ới cú pháp từ ô G7, G4 ở ví dụ trên, bạn sẽ có:

• =INDEX(Vùng kết quả, MATCH(TRUE, INDEX(Biểu thức, 0), 0))
• =INDEX(Vùng kết quả, MATCH(1, INDEX((Biểu thức 1)*(Biểu thức 2), 0), 0))
• Lưu ý rằng sẽ luôn có 2 cái số “, 0), 0)”, số 0 đầu tiên cho hàm INDEX(Biểu thức,0) và số 0 cuối cùng cho hàm MATCH(,,0).

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.

Đố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.

• Kết quả trả về #NA tức là không tìm thấy nữa. Để không hiển thị lỗi, bạn có thể sử dụng hàm IFERROR(Công thức, “”).
• Biểu thức điều kiện theo tên đầu tiên, bạn sẽ cần thực hiện cố định cả ô $E$2, để khi kéo công thức xuống sẽ có thể cố định được ô giá trị dò tìm.
• $F$1:F1, tại sao chỉ nên cố định cái đầu tiên, bởi vì để khi kéo xuống bên dưới thì nó sẽ trở thành $F$1:F[2->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 nhau

Ví 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:

• STT: Nếu như Sản phẩm trả về rỗng, thì ô sẽ trả về rỗng, ngược lại, nếu lấy giá trị ô ngay bên trên + 1. Hàm N() ở đây để kiểm tra nếu là chữ thì sẽ trả về 0. Trong khi đó, nếu là số thì sẽ trả về số tương ứng. N(“STT”) => 0+1 = 1, nếu sản phẩm có kết quả.
• Sản phẩm: =INDEX(Vùng kết quả [ nhiều cột], MATCH(ROW(1:1), Vùng cột phụ,0), Cột trả về). Bạn có Vùng kết quả là B1:D6, lúc này bạn vận dụng chỉ cột B1:B6 cũng được, nhưng bạn có thể trả về cột tương ứng như bạn mong muốn. Ở đây bạn có vùng B1:D6, cột trả về là 1 = cột B. Còn ROW(1:1) là gì? ROW(1:1) trả về 1. Và khi bạn tiến hành kéo công thức xuống B13, nó trở thành ROW(2:2) = 2.
• Số lượng: Chỉ đang có mỗi Cột trả về, vậy tại sao lại là COLUMN(B1)? Và hàm COLUMN(B1) sẽ trả về kết quả của cột B1 là cột bao nhiêu, có nghĩa là 2. Khi tiến hành kéo sang phải, nó trở thành hàm COLUMN(C1), tức là 3. Vậy khi tiến hành kéo sang trái chỗ cột Sản phẩm thì kết quả à COLUMN(A1), tức là 1.
• Chỗ #NA của STT và Sản phẩm, bạn cố tình để kết quả như vậy, nếu bạn muốn không hiển thị #NA, hãy sử dụng IFERROR theo cột Số lượng và Kho nhé!

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