Hướng dẫn dùng INDEX và MATCH trong Excel một cách nhanh chóng nhất

Hàm Index có 2 dạng là hàm Index dạng mảng và Index dạng tham chiếu.

Hàm Index dạng mảng trả về giá trị của 1 ô dữ liệu có chỉ số hàng và chỉ số cột đang tìm kiếm. Cú pháp hàm dạng mạng là Index (Array, Row_num,[column_num]). Trong đó:

Array: Mảng dữ liệu tham chiếu.

Row_num: Hàng chứa giá trị cần lấy.

Column_num: Cột chứa giá trị cần lấy.

Hàm Index: dạng tham chiếu trả về giá trị của 1 ô có chỉ số hàng và cột đang tìm kiếm. Cú pháp hàm dạng tham chiếu là INDEX (Reference, Row_num, [Column_num], [Area_num]. Trong đó:

• Reference: Vùng tham chiếu chứa giá trị cần tìm.
• Row_num: Chỉ số hàng chứa giá trị cần tìm.
• Column_num: Chỉ số cột chứa giá trị cần tìm.
• Area_num: Số vùng trả về, để trống mặc định là 1.

Hàm Match trả về số thứ tự của giá trị cần tìm trong bảng. Cấu trúc hàm Match là =MATCH (Lookup_Value, Lookup_array, [Match_type]). Trong đó:

Lookup_Value: Giá trị cần tìm kiếm.

Lookup_array: Mảng chứa giá trị cần tìm.

Match_type: Kiểu tìm kiếm. Có 3 kiểu tìm kiếm:

• Tìm kiếm giá trị nhỏ hơn giá trị cần tìm kiếm khi match_type =1.
• Tìm kiếm giá trị bằng giá trị cần tìm kiếm khi match_type =0.
• Tìm kiếm giá trị lớn hơn giá trị cần tìm kiếm khi match_type = -1

Trước khi tìm hiểu hàm INDEX kết hợp hàm MATCH được ứng dụng như thế nào, ta tìm hiểu về cách sử dụng hàm VLOOKUP như sau:

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, học exceltrướ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õ.

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)

• 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.

=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.

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ậ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?

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?

=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.

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ớ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].

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ả.
• 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é!

Như vậy bài viết đã hướng dẫn cho bạn cách  dùng INDEX và MATCH trong Excel . Hy vọng rằng bài viết này sẽ giúp ích cho những ai đang tìm hiểu về INDEX và MATCH. Chúc các bạn thành công!