Bí kíp võ lâm 1: INDEX/MATCH – Tìm kiếm nhiều điều kiện, và trả về nhiều kết quả

Học Excel Online đi sâu vào cách sử dụng kết hợp hàm index và match. Cho bạn khả năng tìm kiếm nhiều điều kiện cũng như trả về nhiều kết quả

Xem nhanh

INDEX/MATCH dùng như thế nào?

Cú pháp thường gặp, hay dùng với VLOOKUP:

Nếu với VLOOKUP, ta có công thức như 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)

Ta có ví dụ sau, với hàm VLOOKUP các bạn lưu ý các vấn đề sau:

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

Xem thêm: Sử dụng hàm index và hàm match nhiều điều kiện

Và lúc này chúng ta có công thức: =VLOOKUP(E2, $A$1:$C$8, 3, 0). Với E2 là giá trị cần tìm, trong vùng dữ liệu từ A1:C8, và dấu $ mang ý nghĩa cố định vùng dữ liệu tìm kiếm để khi kéo công thức vùng sẽ cố định. Cột dữ liệu trả về là cột thứ 3, tính từ vị trí đếm từ cột chứa giá trị dò tìm sang bên phải. Và số 0, là tìm chính xác, luôn luôn là số 0.

Vậy với INDEX thì sẽ 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)

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

Lúc này các bạn sẽ thấy hàm có cú pháp như sau: =INDEX(Vùng kết quả, Dòng, Cột).

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

Vì sao lại nói dùng INDEX/MATCH dễ hơn VLOOKUP?

VLOOKUP đòi hỏi cột chứa giá trị dò tìm phải nằm ngoài cùng bên trái vùng dữ liệu. Nếu nằm bên phải thì lúc này phải dùng hàm mảng kết hợp với hàm CHOOSE để lấy kết quả tương ứng. Vậy cùng xem lại ví dụ, bạn chỉ việc quét vùng chọn cột kết quả, tìm trong cột chứa giá trị dò tìm. Thế là xong!

Ví dụ 2: VLOOKUP kết hợp CHOOSE để dò tìm ngược

Ngược lại với ví dụ trước đó, chúng ta có cột Lớp nằm ngoài cùng bên trái, và bài toán là từ tên Học viên, chúng ta sẽ tìm ra lớp của Học viên đó. Bạn sẽ viết hàm VLOOKUP theo như thông thường thế nào? Nghĩ xem nhé? Vậy với hàm VLOOKUP, các bạn phải dùng kết hợp hàm CHOOSE, với cú pháp =CHOOSE({1,2}, Cột chứa giá trị dò tìm, Cột Kết quả).

Vậy ta có cú pháp tổng quát như 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 dấu phân cách của bạn là dấu chấm phẩy, thì 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 INDEX/MATCH thì các 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). Đơn giản rồi phải không nào?

Dùng VLOOKUP/CHOOSE hoặc INDEX/MATCH tìm theo nhiều điều kiện

Ta có ví dụ như trên, lúc này có 2 bạn “Nguyễn Thị Đét” cùng tên học 2 lớp khác nhau, tương đương với 2 điều kiện để chúng ta tìm ra điểm thi của từng bạn. Vậy làm thế nào để tìm ra? Vẫn là hàm VLOOKUP/CHOOSE, lúc này bạn cần ghép 2 điều kiện với nhau bằng dấu & (dấu “and”/”và”), cùng với việc ghép 2 cột chứa giá trị dò tìm với nhau cũng với dấu &. Ta 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à đây là công thức mảng, đòi hỏi các bạn phải nhấn CTRL+SHIFT+ENTER, thay vì Enter (trả về #NA), lúc này các bạn sẽ thấy có móc sừng trâu xuất hiện trong công thức.

Tại sao lại MATCH lúc tìm 1, lúc lại là TRUE và lúc nào phải nhấn CTRL+SHIFT+ENTER?

Ví dụ 3: INDEX/MATCH tìm kiếm theo nhiều điều kiện

Với INDEX/MATCH, các bạn có cú pháp như 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 là công thức mảng, nên phải có nhấn CTRL+SHIFT+ENTER. Vì sao lúc lại là 1, lúc lại TRUE? Khi bạn chỉ có 1 biểu thức, lúc này kết quả sẽ trả về TRUE/FALSE, khi có 2 biểu thức TRUE*TRUE, Excel sẽ chuyển TRUE thành 1*1 = 1.

Giá trị dò tìm là TRUE, 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, 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])
Tìm FALSE lúc nào? Khi bạn cần tìm giá trị không thỏa theo biểu thức so sánh của mình.

Dùng INDEX/MATCH kết hợp INDEX để chuyển đổi công thức mảng thành công thức bình thường.

Trong ví dụ 3, các bạn làm quen với công thức mảng, đòi hỏi thao tác phải nhấn CTRL+SHIFT+ENTER, để tránh việc phải làm thao tác này, bạn có thể kết hợp thêm hàm INDEX bên trong hàm MATCH để trả về giá trị đầu tiên trong danh sách MATCH tìm thấy.

Ví dụ 4: INDEX/MATCH dò tìm theo nhiều điều kiện

Với cú pháp từ ô G7, G4 trong ví dụ trên ta 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 ý, luôn có 2 cái số “, 0), 0)”, số 0 đầu tiên cho hàm INDEX(Biểu thức,0). 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 INDEX kết hợp COUNTIFS. Kết quả trả về nhiều kết quả từ một hoặc nhiều điều kiện.

Với giá trị dò tìm “Nguyễn Thị Đét” bạn có nhiều kết quả trả về, vậy có cách nào liệt kê được tất cả kết quả không? Câu trả lời là có. Với 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ụ: (0=COUNTIFS($F$1:F1,$B$1:$B$8)). Nghĩa là: Xét thêm điều kiện đã trả về kết quả trước đó hay chưa? Nếu đã trả về kết quả rồi, thì loại trừ để lấy cái tiếp theo. Lúc này ta sẽ có kết quả mong muốn.

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

Làm thế nào để in phiếu xuất kho có nhiều sản phẩm? Cùng một phiếu, có nhiều mã sản phẩm khác nhau

Ví dụ với 1 mã xuất kho, bạn sẽ xuất ra nhiều sản phẩm khác nhau. Lúc này in phiếu xuất kho, bạn chỉ việc nhập mã phiếu xuất kho, sẽ trả về danh sách sản phẩm tương ứng.

Ta lập cột phụ tham chiếu theo mã phiếu xuất kho, lúc này COUNTIFS làm nhiệm vụ đánh số thứ tự giúp chúng ta. Vẫn là cột dây vào 1 đầu cột, dây còn lại 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 phải cố định để khi kéo xuống chúng ta không thay đổi điều kiện tìm kiếm. Lúc này các bạn sẽ thấy số tăng dần theo vùng Mã phiếu xuất kho, nếu không tìm thấy nữa, thì chỉ là lặp lại cái cuối cùng tìm thấy.

Lúc này trở về Sheet Phiếu Xuất Kho để in ấn, chỉ việc lập công thức tương ứng như sau, mình giải thích từ trái sang:

STT: Nếu Sản phẩm trả về rỗng, thì sẽ trả về rỗng, ngược lại lấy giá trị ô ngay bên trên + 1. Hàm N() ở đây để kiểm tra nếu là chữ sẽ trả về 0, nếu là số thì trả về số tương ứng. N(“STT”) => 0+1 = 1, nếu sản phẩm có kết quả. Tìm hiểu thêm về Hướng dẫn sử dụng hàm N trong Excel.
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ề). Ta có Vùng kết quả là B1:D6, lúc này bạn có thể vận dụng chỉ cột B1:B6 cũng được, nhưng chúng ta có thể trả về cột tương ứng ta mong muốn. Ở đây ta 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 chúng ta kéo công thức xuống B13, nó trở thành ROW(2:2) = 2.
Số lượng: Ở đây bạn thấy chỉ khác mỗi Cột trả về đúng không? Tại sao lại là COLUMN(B1)? Hàm COLUMN(B1) sẽ trả về kết quả cột B1 là cột bao nhiêu, tức là 2. Khi kéo sang phải, nó trở thành COLUMN(C1), tức là 3. Vậy khi kéo sang trái chỗ cột Sản phẩm thì nó thành gì? Bạn đoán xem? Là COLUMN(A1), tức là 1. Giờ thì bạn hiểu vì sao mình để công thức cho các bạn thấy rồi phải không?
Chỗ #NA của STT và Sản phẩm, mình cố tình để kết quả như vậy, nếu bạn muốn không hiển thị #NA, hãy dùng IFERROR theo cột Số lượng và Kho nhé!

Để tìm hiểu thêm các bạn có thể sử dụng chức năng tìm kiếm trên web tại ô tìm kiếm, hoặc tìm kiếm với Google, hãy thêm từ khóa “blog.hocexcel.online” + “từ khóa”. Ví dụ: “blog.hocexcel.online”,”INDEX/MATCH”.

Đón xem: – Bí kíp võ lâm – Tập 2: SUMPRODUCT thần chưởng.