Hàm INDEX MATCH là một trong những hàm tìm kiếm cơ bản trong Excel. So với hai hàm tìm kiếm phổ biến là VLOOKUP và HLOOKUP thì việc kết hợp hàm INDEX MATCH sẽ linh hoạt và mạnh mẽ hơn trong rất nhiều trường hợp.
Trong bài viết này, hoctin.vn sẽ giới thiệu với các bạn cách kết hợp hàm INDEX MATCH thông qua các ví dụ và bài tập có lời giải. Đồng thời giải thích những lưu ý và lỗi thường mắc phải khi kết hợp hai hàm này.
Mục Lục Bài Viết
1. Hàm INDEX Trong Excel Là Gì?
Hàm INDEX nói một cách đơn giản là hàm lấy giá trị của ô trong Excel.
Có hai dạng công thức của hàm INDEX: dạng mảng và dạng tham chiếu.
1.1 Hàm INDEX Dạng Mảng Trong Excel
Công thức hàm INDEX dạng mảng:
=INDEX(Array,Row_num,[Column_num])
- Trong đó:
- Array: Một phạm vi ô hoặc một mảng
- Row_num: Một hàng thuộc phạm vi ô hoặc mảng đã khai báo ở Array
- Column_num: Một cột thuộc phạm vi ô hoặc mảng đã khai báo ở Array
Như vậy, cách dùng hàm INDEX có thể được hiểu như là một lệnh tìm kiếm giá trị trong Excel dựa trên Row_num (thứ tự hàng) và Column_num (thứ tự cột) của giá trị đó trong Array (mảng)
Ví Dụ Cách Dùng Hàm INDEX Dạng Mảng Trong Excel:
Ví dụ: Cho bảng Số Lượng Các Mặt Hàng Bán Ra Trong Quý I/ 2019 như bảng dưới
Yêu cầu: Dùng hàm INDEX để tìm số lượng bán ra của mặt hàng Trứng Gà trong tháng 1
Với đề bài như vậy chúng ta có thể sử dụng hàm INDEX với công thức =INDEX(B4:E8,2,4)
Trong đó:
B4:E8 là phạm vi ô (vùng chọn)
2,4: lần lượt là dòng và cột chứa số lượng bán ra của mặt hàng Trứng Gà
Ta sẽ được kết quả của hàm INDEX =INDEX(B4:E8,2,4) là 83
Lưu ý khi tham chiếu đến một bảng, Row_num và Column_num là thứ tự cột và hàng của vùng tham chiếu, không phải của bảng Excel.
Như ví dụ trên thì Row_num và Column_num lần lượt là [2,4] (thứ tự trong vùng tham chiếu B4:E8) chứ không phải là [5,5] (thứ tự trong bảng Excel)
1.2 Hàm INDEX Dạng Tham Chiếu Trong Excel:
=INDEX(Reference,Row_num,[Column_num],[Area_num])
-
Trong đó:
-
Reference: Vùng tham chiếu, bao gồm nhiều phạm vi ô
- Row_num: Thứ tự hàng
- Column_num: Thứ tự cột
- Area_num: Số thứ tự của một trong các phạm vi ô muốn chọn
Đây là cách dùng phức tạp hơn của hàm INDEX, bạn có thể theo dõi bài viết sau của hoctin.vn để tham khảo thêm về cách dùng này
Hàm INDEX dạng tham chiếu trong Excel, ví dụ và bài tập
2. Hàm MATCH Trong Excel Là Gì?
Hàm MATCH là hàm trả về vị trí ô của một giá trị trong Excel. Vị trí ô trả về sẽ dựa trên giá trị tìm kiếm và loại tìm kiếm trong công thức hàm MATCH.
1.1 Cách Dùng Hàm MATCH Trong Excel
Cách sử dụng của hàm MATCH trong Excel sẽ dựa trên công thức:
=MATCH (lookup_value, lookup_array, [match_type])
- Trong đó:
- lookup_value: giá trị cần tìm kiếm
- lookup_array: phạm vi ô cần tìm kiếm
- match_type: loại tìm kiếm của hàm MATCH: -1 (greater than); 0 (exact match) và match_type mặc định là 1 (less than)
1.2 Ví Dụ Cách Dùng Hàm MATCH Để Tìm Vị Trí Của Một Giá Trị Cho Trước:
Ví dụ trong trường hợp này, muốn kiểm tra giá trị “Tuấn” ở vị trí (thứ tự) nào trong dãy giá trị An, Bình, Dũng, Quang, Tuấn, chúng ta sẽ dùng hàm MATCH có công thức: =MATCH(C4,B4:B8,0)
Trong đó:
- C4 là giá trị cần tìm (Tuấn)
- B4:B8 là phạm vi cần tìm
- 0 là loại tìm kiếm exact MATCH
Kết quả của hàm MATCH trên sẽ được 5, đúng với Số TT của Tuấn trong bảng.
3. Cách Sử Dụng Hàm INDEX Và MATCH Làm Hàm Kết Hợp Trong Excel
3.1 Kết Hợp Hàm INDEX MATCH Thành Hàm Tìm Kiếm Nhiều Điều Kiện
Bài tập 1: Cho bảng Số Lượng Các Mặt Hàng Bán Ra Trong Quý 1/ 2019
Yêu cầu: Dùng hàm INDEX và MATCH trong Excel để tìm số lượng bán ra của hai mặt hàng là Trứng Gà và Bột Mì trong tháng 1 và tháng 3.
Phân tích bài toán này ta thấy:
- Giá trị cần tìm: là số lượng bán ra của một mặt hàng, với 2 điều kiện
- Điều kiện 1: Là số lượng bán ra của mặt hàng trứng gà
- Điều kiện 2: Là số lượng bán ra trong tháng 1 (hoặc tháng 3)
Như vậy, để giải được bài toán này ta có thể kết hợp hàm MATCH và INDEX để tạo thành một hàm lookup 2 điều kiện:
Công thức kết hợp hàm MATCH và INDEX:
=INDEX(C3:E8,MATCH(A13,B3:B8,0),MATCH(C12,C3:E3,0))
Trong đó:
- C3:E8: Array của hàm INDEX là phạm vi ô chứa giá trị cần tìm.
- MATCH(A13,B3:B8,0): đóng vai trò là Row_num trong hàm INDEX
- MATCH(C12,C3:E3,0): đóng vai trò là Column_num của hàm INDEX
Như vậy, bằng cách kết hợp hàm INDEX và MATCH, ta đã được một hàm tìm kiếm và liệt kê có điều kiện thực hiện các lệnh:
- Đầu tiên hàm MATCH(A13,B3:B8,0): tìm vị trí (thứ tự) của giá trị “Trứng Gà” trong dãy các giá trị từ B3 đến B8. Kết quả là 3, sau đó gán kết quả này cho Row_num của hàm INDEX.
- Tiếp theo hàm MATCH(C12,C3:E3,0): tìm vị trí( thứ tự) của giá trị “THÁNG 1” trong dãy các giá trị từ C3 đến E3. Kết quả là 1, sau đó gán kết quả này cho Column_num của hàm INDEX.
- Cuối cùng hàm INDEX(C3:E8,MATCH(A13,B3:B8,0),MATCH(C12,C3:E3,0)) sẽ lấy giá trị là giao của Row_num=3 và Column_num=1 trong bảng B3:E8.
- Kết quả cuối cùng ta được giá trị của ô ở hàng thứ 3, cột 1 trong bảng B3:E8 là 70
3.2 Hàm Tham Chiếu Ngược (VLOOKUP Ngược)
Bài tập 2: Cho bảng Số Lượng Các Mặt Hàng Bán Ra Trong Quý 1/2019
Yêu cầu: Tìm vị trí kho của 2 mặt hàng cho trước là Trứng Gà và Bột Mì bằng cách sử dụng hàm INDEX MATCH.
Trong trường hợp này, chúng ta sẽ không dùng được hàm VLOOKUP vì hàm này có một nhược điểm là chỉ tìm từ trái sang phải.
Vì vậy, chúng ta sẽ dùng một phương án thay thế, đó là thể kết hợp hàm INDEX và MATCH để thực hiện tìm kiếm từ phải sang trái như một lệnh “VLOOKUP ngược”
Công thức kết hợp hàm INDEX và MATCH để tìm kiếm từ phải sang trái:
=INDEX(A4:A8,MATCH(A13,B4:B8,0))
Giải thích:
- A4:A8: là phạm vi ô chứa giá trị cần tìm. Ở đây giá trị cần tìm là Kho của mặt hàng
- MATCH(A13,B4:B8,0): là thứ tự của mặt hàng Trứng Gà, được kết quả là 2. Đồng thời cũng là Row_num cho hàm INDEX
- Cuối cùng, hàm INDEX sẽ tìm giá trị của ô ở hàng thứ 2 trong phạm vi ô từ A4:A8.
Như vậy, bằng cách kết hợp hàm INDEX và MATCH trong Excel, ta được một hàm VLOOKUP ngược cho ra kết quả “Sài Gòn” là giá trị cho ô C13 trong hình
4. Một Số Lỗi Thường Gặp Khi Kết Hợp Hàm INDEX và MATCH:
Có 3 trường hợp lỗi phổ biến khi thường gặp khi sử dụng hàm INDEX MATCH làm hàm kết hợp trong Excel:
Trường Hợp 1: Lỗi N/A Khi Dùng Hàm INDEX MATCH
Trong Excel, N/A là viết tắt của lỗi “No Answer” hoặc “Not Available” hoặc, có nghĩa là không có câu trả lời phù hợp cho hàm mà bạn sử dụng
Trường Hợp 2: Lỗi #REF hoặc #VALUE Khi Dùng Hàm INDEX MATCH
Trường Hợp 3: Lỗi Kết Quả Sai Khi Sử Dụng Hàm INDEX MATCH
Khi gặp lỗi này, tuy công thức hàm kết hợp của bạn đã đúng, hàm vẫn chạy. Nhưng khi kết quả ra thì lại sai so với giá trị mà bạn mong muốn
5. Cách Sửa Lỗi Khi Kết Hợp Hàm INDEX MATCH
Cách 1: Đổi Match_type Của Hàm INDEX Match
Trong công thức hàm MATCH, bạn có thể chọn 3 loại match_type: -1 (greater than); 0 (exact match) và match_type mặc định là 1 (less than)
Nếu để mặc định không điền thì match_type trong công thức hàm MATCH sẽ bằng 1.
Và với match_type này, các giá trị trong lookup_array của bạn phải được sắp xếp theo thứ tự tăng dần.
Trong ví dụ này, nếu hàm MATCH có match_type là 1 mà dữ liệu lại được sắp xếp theo thứ tự ngẫu nhiên thì sẽ xuất hiện lỗi N/A khi kết hợp hàm MATCH INDEX
-
Cách sửa lỗi khi kết hợp hàm INDEX và MATCH trong trường hợp này là đổi match_type sang 0. Với match_type bằng 0 thì các giá trị trong bảng có thể ở bất kì thứ tự nào
Cách 2: Đổi vị trí của Column_num và Row_num trong Hàm INDEX Match
Trong công thức của hàm INDEX, Row_num sẽ ở vị trí thứ 2 sau dấu phẩy, Column_num ở vị trí thứ 3. Vậy nên nếu bạn khai báo Column_num trước Row_num khi dùng hàm INDEX MATCH thì sẽ gặp phải lỗi N/A hoặc #VALUE trong excel
Xem ví dụ một bài tập kết hợp hàm INDEX MATCH dưới đây:
Trong ví dụ này, với Row_num là thứ tự của Bình trong dãy Tên, và Column_num là thứ tự của Điểm Toán trong dãy các môn học (Toán, Văn, Anh) thì chúng ta phải dùng hàm MATCH để tìm tên của Bình trước (Row_num trước), sau đó mới dùng tiếp hàm MATCH để tìm môn Toán (Column_num sau).
-
Cách sửa lỗi kết hợp hàm INDEX MATCH trong trường hợp này là đổi vị trí để Row_num ra trước và Column_num sau. Hay nói cách khác là đổi hàm MATCH thứ nhất và thứ hai với nhau
Cách 3: Sửa giá trị của Lookup_array hoặc Array Trong Hàm INDEX Match
Trong công thức hàm MATCH, nếu lookup_array tham chiếu không hết phạm vi hàng (hoặc cột) đã khai báo trong Array của hàm INDEX thì cũng sẽ xuất hiện lỗi NA #Ref hoặc #Value trong Excel
Xem ví dụ một bài tập kết hợp hàm MATCH INDEX dưới đây:
Trong ví dụ này, trong khi Array của hàm INDEX có phạm vi từ B3:E8 thì lookup_array chỉ từ B4:B8 (đúng phải là B3:B9)
-
Cách sửa lỗi khi kết hợp hàm INDEX và MATCH trong trường hợp này là sửa lại lookup_array để có thể tham chiếu hết các hàng đã khai báo trong Array của hàm INDEX
6. Kết Hợp Hàm INDEX và MATCH So Với Hàm VLOOKUP
Ưu Điểm Của Kết Hợp Hàm INDEX, MATCH so với Hàm VLookup
Việc kết hợp hàm INDEX MATCH có rất nhiều ưu điểm so với hàm VLOOKUP. Ngoài việc có công thức đơn giản hơn, hàm INDEX MATCH còn xử lý được những trường hợp mà VLOOKUP sẽ không làm được. Cụ thể bạn có thể tham khảo bảng so sánh bên dưới đây:
Trên đây là bài hướng dẫn cách dùng kết hợp hàm MATCH và INDEX trong Excel. Hi vọng rằng, qua bài viết này của hoctin.vn, các bạn các thông tin hữu ích về cách sử dụng cũng như những lỗi thường gặp của hàm MATCH INDEX
Ngoài ra, để ứng dụng hiệu quả hơn hàm MATCH và hàm INDEX trong Excel, bạn có thể tham khảo thêm những bài viết sau đây của hoctin.vn
Hàm MATCH
Hàm INDEX
Xin chào hoctin.vn ạ. Dạo này mình đang tìm hiểu Excel và có sử dụng hàm vlookup, hlookup cũng như index + match. Nhưng mình gặp một vấn đề là nếu như trong lookup_value mình có cả số và chữ và mình muốn lấy phần số thì ở table_array không thể xác định được. Mình đã thử thay đổi định dạng của số và chữ nhưng vẫn không thể được. Hy vọng tinhoc.vn giúp mình với ạ!
Mail của mình là [email protected]
Hy vọng hoctin.vn sẽ phản hồi. Mình cám ơn ạ