Trong bài viết này chúng ta sẽ đi tìm hiểu về cách sử dụng các hàm INDEX/MATCH, cách kết hợp chúng dể tìm kiếm nhiều điều kiện, và trả về nhiều kết quả. Không nói nhiều nữa chúng ta bắt tay vào việc ngay thôi
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à “Chu Lập Thành“, 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õ.
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)
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à 3, tương ứng dòng tìm thấy từ trên xuống. INDEX($C$1:$C$8, 3) => Kết quả là 9.
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!
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 “Phong Vân” 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 ngoặc nhọn 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ớ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ớ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 “Phong Vaan” 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].
Trên đây là các ví dụ về cách sử dụng hàm INDEX/MATCH – Tìm kiếm nhiều điều kiện, và trả về nhiều kết quả trong Excel. Hi vọng bài viết này giúp ích được cho các bạn. Chúc các bạn thành công!
