Hàm INDEX MATCH, Cách Dùng Và Lỗi Thường Gặp Trong Excel

Hàm INDEX MATCH, Cách Dùng Và Lỗi Thường Gặp Trong Excel

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)

Công thức hàm INDEX
Công thức hàm INDEX

 

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í Dụ Cách Dùng Hàm INDEX Dạng Mảng Trong Excel

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 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 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)
Công Thức Hàm MATCH Trong Excel
Công thức hàm MATCH

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ụ Cách Dùng Hàm Match Trong Excel

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. 

Hàm INDEX MATCH Tìm Kiếm Nhiều Điều Kiện

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:

Bài Tập Hàm INDEX MATCH Tìm Kiếm Nhiều Đ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ò 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=3Column_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àBột Mì bằng cách sử dụng hàm INDEX MATCH.

Hàm Tham Chiếu Ngược Trong Excel

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”
Hàm VLOOKUP Ngược Trong Excel

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

Khi kết hợp hàm INDEX và MATCH trong Excel, nếu Excel thông báo lỗi #VALUE hoặc #REF thì có nghĩa là đã xảy ra lỗi với cách nhập công thức của bạn hoặc đã xảy ra lỗi với các ô bạn đang tham chiếu tới

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.  

Tham khảo thêm: Công Thức Hàm Match Trong Excel

Xem ví dụ một bài tập kết hợp hàm INDEX MATCH dưới đây:
Lỗi N/A Hàm MATCH INDEX Trong Excel

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:

Lỗi Kết Quả Sai Trong Hàm INDEX MATCH

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:

Lỗi NA Khi Kết Hợp Hàm MATCH INDEX Trong Excel

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:

Kết hợp hàm INDEX MATCH

Hàm VLOOKUP

Có thể tham chiếu ngược từ phải sang trái

Chỉ có thể tham chiếu từ trái sang phải

lookup_value không giới hạn

lookup_value giới hạn trong 255 kí tự

Không yêu cầu phải sắp xếp dữ liệu

Phải sắp xếp theo thứ tự tăng dần trong kiểu tìm kiếm gần đúng

Tốc độ tìm kiếm nhanh hơn hàm VLOOKUP

Càng kết hợp nhiều hàm VLOOKUP, tốc độ tìm càng chậm

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

1 thought on “Hàm INDEX MATCH, Cách Dùng Và Lỗi Thường Gặp Trong Excel”

Leave a Comment