Hướng dẫn cách sử dụng hàm INDEX MATCH trong Google Sheets từ A – Z

Hàm INDEX MATCH trong Google Sheets là một bộ đôi quyền năng giúp tra cứu dữ liệu linh hoạt và hiệu quả. Trong bài viết này, các bạn hãy cùng GCS Việt Nam đi vào tìm hiểu những kiến thức từ cơ bản đến nâng cao về công thức hàm này chi tiết. Cùng tham khảo ngay nhé. 

Hàm INDEX và cách sử dụng

Hàm INDEX trong Google Sheets là một hàm cho phép trả về giá trị của một ô tại vị trí cụ thể trong một phạm vi hoặc một mảng. Khác với nhiều hàm tìm kiếm khác, hàm INDEX không yêu cầu dữ liệu phải được sắp xếp theo một thứ tự cụ thể nào, mà hàm này lại rất linh hoạt. 

Cú pháp của hàm INDEX là: 

=INDEX(phạm_vi, [số_thứ_tự_hàng], [số_thứ_tự_cột])

Trong đó:

• phạm_vi: là vùng dữ liệu mà bạn muốn trích xuất giá trị. Nó có thể là một cột, một hàng, hoặc một bảng dữ liệu lớn hơn.
• số_thứ_tự_hàng (tùy chọn): là số thứ tự của hàng trong phạm_vi mà bạn muốn lấy giá trị. Nếu phạm_vi là một cột đơn, bạn vẫn cần cung cấp số_thứ_tự_hàng để chỉ định vị trí trong cột đó.
• số_thứ_tự_cột (tùy chọn): là số thứ tự của cột trong phạm_vi mà bạn muốn lấy giá trị. Nếu phạm_vi là một hàng đơn, bạn vẫn cần cung cấp số_thứ_tự_cột để chỉ định vị trí trong hàng đó.

*Ví dụ:

Có bảng dữ liệu:

Yêu cầu: Bạn muốn tìm Giá của sản phẩm Máy tính bảng

Công thức:  =INDEX(A2:E5; 3;4)

Trong đó: 

• A2:E5 là phạm_vi dữ liệu của chúng ta (không bao gồm hàng tiêu đề).
• 3 là số_thứ_tự_hàng (vì “Máy tính bảng” nằm ở hàng thứ 3 trong phạm vi A2:E5).
• 4 là số_thứ_tự_cột (vì cột “Giá (VNĐ)” là cột thứ 4 trong phạm vi A2:E5).

*Kết quả:

Hàm MATCH và cách sử dụng

Nếu hàm INDEX giúp tìm một giá trị tại một địa chỉ cụ thể. Hàm MATCH lại giúp tìm ra một địa chỉ của một giá trị trong danh sách một cột hoặc một hàng. Đồng thời, hàm MATCH còn cho biết một mục mà bạn muốn tìm nằm ở vị trí thứ mấy trong phạm vi chỉ định. 

Cú pháp của hàm MATCH là:

=MATCH(giá_trị_tìm_kiếm, phạm_vi_tìm_kiếm, [loại_tìm_kiếm])

Trong đó:

• giá_trị_tìm_kiếm: là giá trị mà bạn muốn tìm vị trí của nó. Nó có thể là một số, văn bản, hoặc một tham chiếu ô.
• phạm_vi_tìm_kiếm: là phạm vi một hàng hoặc một cột mà hàm MATCH sẽ tìm kiếm giá_trị_tìm_kiếm trong đó. Lưu ý quan trọng phạm_vi_tìm_kiếm phải là một mảng 1 chiều (chỉ một hàng hoặc một cột).
• loại_tìm_kiếm (tùy chọn): là loại tìm kiếm mà bạn muốn thực hiện. Có ba giá trị phổ biến:
  • 0: Tìm kiếm chính xác. Nếu không tìm thấy giá trị tương ứng, hàm sẽ trả về lỗi #N/A.
  • 1: Tìm kiếm lớn hơn hoặc bằng. Phạm vi tìm kiếm phải được sắp xếp tăng dần. Hàm sẽ trả về vị trí của giá trị lớn nhất nhỏ hơn hoặc bằng giá_trị_tìm_kiếm.
  • -1: Tìm kiếm nhỏ hơn hoặc bằng. Phạm vi tìm kiếm phải được sắp xếp giảm dần. Hàm sẽ trả về vị trí của giá trị nhỏ nhất lớn hơn hoặc bằng giá_trị_tìm_kiếm.

*Ví dụ: Từ bảng dữ liệu trên, bạn hãy tìm kiếm vị trí của Máy tính bảng trong cột Sản phẩm 

Công thức: =MATCH(“Máy tính bảng”; B2:B5; 0)

Trong đó:

• “Máy tính bảng” là giá_trị_tìm_kiếm.
• B2:B5 là phạm_vi_tìm_kiếm (chỉ bao gồm các tên sản phẩm).
• 0 là loại tìm kiếm chính xác.

*Kết quả: 

Nên kết hợp hàm INDEX và MATCH khi nào?

Có thể kết hợp hàm INDEX và MATCH được không? Sự kết hợp giữa hàm INDEX MATCH trong Google Sheets sẽ tạo nên một bộ đôi tìm kiếm dữ liệu cực kỳ linh hoạt. Đặc biệt, sự kết hợp này có thể thay thế mạnh mẽ cho việc sử dụng hàm VLOOKUP trong nhiều trường hợp.

• Tìm kiếm dữ liệu không giới hạn về vị trí cột trả về

Hàm VLOOKUP có sự hạn chế lớn về tìm kiếm giá trị trong cột đầu tiên của dải dữ liệu và trả về giá trị ở các cột nằm bên phải cột tìm kiếm. Còn nếu bạn muốn trả về dữ liệu nằm ở bên trái cột tìm kiếm, hàm VLOOKUP sẽ không hoạt động. Lúc này, bạn có thể thay thế bằng cách sử dụng hàm INDEX MATCH Google Sheets. Bởi hàm INDEX MATCH trong Google Sheets giúp tìm kiếm ở bất kỳ cột nào và trả về giá trị từ bất kỳ cột nào dù nó nằm bên trái hay bên phải. 

• Tìm kiếm dữ liệu dựa trên nhiều điều kiện

Sử dụng hàm INDEX MATCH nhiều điều kiện trong Google Sheets sẽ giúp xử lý nhiều trường hợp. Bởi với cách lồng hàm MATCH có khả năng tìm kiếm khá đặc biệt, trong một địa chỉ của một giá trị ở danh sách.  

• Tìm kiếm theo cả hàng và cột

Nếu bạn cần tìm một giá trị dựa trên cả điều kiện hàng và cột, có thể sử dụng hàm INDEX MATCH. Hàm MATCH sẽ giúp tìm vị trí hàng và vị trí cột dễ dàng. 

Công thức hàm INDEX MATCH trong Google Sheets

Khi kết hợp hàm INDEX MATCH trong Google Sheets, chúng ta sẽ có một công cụ tìm kiếm dữ liệu hai chiều. Hàm này sẽ vượt trội hơn rất nhiều so với việc sử dụng hàm VLOOKUP ở nhiều khía cạnh. Bởi về cơ bản, hàm MATCH sẽ giúp xác định vị trí (số thứ tự hàng hoặc cột) của giá trị cần tìm. Còn hàm INDEX sẽ sử dụng các vị trí đó để trả về giá trị tương ứng. 

Có hai dạng công thức phổ biến khi kết hợp hàm INDEX và MATCH trong Google Sheets. Đó là tìm kiếm một chiều ( theo hàng hoặc theo cột) và tìm kiếm hai chiều ( theo cả hàng và cột).

Công thức tìm kiếm một chiều

Với trường hợp này, bạn sẽ tìm một giá trị dựa trên tiêu chí tìm kiếm trong một cột  cụ thể và trả về giá trị từ một cột khác trong cùng một hàng. 

Cú pháp:

=INDEX(cột_muốn_trả_về, MATCH(giá_trị_tìm_kiếm, cột_tìm_kiếm, 0))

Trong đó: 

• cột_muốn_trả_về: là phạm vi (một cột) chứa các giá trị mà bạn muốn hàm INDEX trả về. 
• MATCH(giá_trị_tìm_kiếm, cột_tìm_kiếm, 0): là hàm MATCH lồng bên trong.
  • giá_trị_tìm_kiếm: Giá trị cụ thể mà bạn đang tìm kiếm 
  • cột_tìm_kiếm: Phạm vi (một cột) mà hàm MATCH sẽ tìm giá_trị_tìm_kiếm trong đó. 
  • 0: Loại tìm kiếm chính xác, đảm bảo MATCH tìm thấy giá trị giống hệt.

Công thức tìm kiếm hai chiều 

Trường hợp này, bạn sẽ tìm kiếm được một giá trị tại giao điểm của một hàng hoặc một cột, dựa trên hai tiêu chí tìm kiếm khác nhau.

Cú pháp:

=INDEX(phạm_vi_dữ_liệu_lớn, MATCH(giá_trị_tìm_kiếm_hàng, cột_tìm_kiếm_hàng, 0), MATCH(giá_trị_tìm_kiếm_cột, hàng_tìm_kiếm_cột, 0))

Trong đó: 

• phạm_vi_dữ_liệu_lớn: là toàn bộ bảng hoặc phạm vi dữ liệu mà bạn muốn tìm kiếm. 
• MATCH(giá_trị_tìm_kiếm_hàng, cột_tìm_kiếm_hàng, 0): hàm MATCH đầu tiên này sẽ tìm vị trí hàng.
  • giá_trị_tìm_kiếm_hàng: Giá trị bạn muốn tìm để xác định hàng 
  • cột_tìm_kiếm_hàng: Cột mà hàm MATCH sẽ tìm giá_trị_tìm_kiếm_hàng trong đó.

• MATCH(giá_trị_tìm_kiếm_cột, hàng_tìm_kiếm_cột, 0): hàm MATCH thứ hai này sẽ tìm vị trí cột.

• giá_trị_tìm_kiếm_cột: Giá trị bạn muốn tìm để xác định cột 
• hàng_tìm_kiếm_cột: Hàng mà hàm MATCH sẽ tìm giá_trị_tìm_kiếm_cột trong đó (thường là hàng tiêu đề).

Cách sử dụng hàm INDEX MATCH trong Google Sheets cụ thể trong từng tình huống

Hiểu công thức là một chuyện, nhưng làm sao để ứng dụng công thức hàm INDEX MATCH trong Google Sheets vào từng ví dụ để tính toán lại là chuyện khác. Dưới đây là những ví dụ chi tiết từ cơ bản đến nâng cao sẽ giúp cho bạn hình dung được rõ hơn về tính năng của bộ đôi hàm này trong Google Sheets. Cùng tham khảo ngay nhé:

Ví dụ 1: Kết hợp hàm INDEX và MATCH cơ bản

Với sự kết hợp giữa hàm INDEX và MATCH, bạn có thể tìm kiếm một giá trị dựa trên một tiêu chí duy nhất và trả về một giá trị tương ứng từ một cột khác. 

*Ví dụ: Từ bảng dữ liệu trên, bạn muốn tìm kiếm Thương hiệu của sản phẩm Điện thoại

Công thức: =INDEX(C2:C5; MATCH(“Điện thoại”; B2:B5; 0)

Trong đó:

• MATCH(“Điện thoại”, B2:B5, 0): tìm kiếm chuỗi văn bản “Điện thoại”.
  • B2:B5, tức là phạm vi cột “Sản phẩm” của bảng dữ liệu
  • 0 : tìm kiếm một sự trùng khớp chính xác.
  • “Điện thoại” nằm ở hàng thứ 2 trong phạm vi B2:B5.
• INDEX(C2:C5, 2): 
  • 2 : đối số số_thứ_tự_hàng cho hàm INDEX.
  • C2:C5 : tức là phạm vi cột “Thương hiệu” của bảng dữ liệu.

*Kết quả: 

Ví dụ 2: Kết hợp hàm INDEX và MATCH nhiều điều kiện

Hàm INDEX MATCH trong Google Sheets có thể giúp người dùng tra cứu dữ liệu dựa trên nhiều tiêu chí cùng lúc. Với hàm INDEX MATCH nhiều điều kiện trong Google Sheets, bạn có thể xử lý các tình huống nhanh chóng bằng cách tạo ra một chuỗi kết hợp từ các điều kiện và phạm vi tìm kiếm.

*Ví dụ: Từ bảng dữ liệu trên, bạn muốn tìm Giá (VNĐ) của sản phẩm “Laptop” có Thương hiệu là “Dell”.

Công thức: =INDEX(D2:D5; MATCH(1; (B2:B5=”Laptop”) * (C2:C5=”Dell”); 0))

Trong đó: 

• D2:D5: Vùng chứa Giá (VNĐ) bạn muốn lấy dữ liệu.
• (B2:B5=”Laptop”): Tạo mảng TRUE/FALSE xác định hàng nào là “Laptop”.
• (C2:C5=”Dell”): Tạo mảng TRUE/FALSE xác định hàng nào có thương hiệu “Dell”.
• (B2:B5=”Laptop”) * (C2:C5=”Dell”): Nhân 2 mảng TRUE/FALSE để lọc ra hàng thỏa đồng thời 2 điều kiện. Chỉ hàng nào có cả 2 TRUE sẽ trả về 1.
• MATCH(1, …, 0): Tìm vị trí của giá trị 1 trong mảng kết quả phía trên.
• INDEX(D2:D5, …): Lấy giá trị tương ứng từ cột Giá.

*Kết quả: 

Ví dụ 3: Kết hợp hàm INDEX MATCH và hàm VLOOKUP

Trong hầu hết các trường hợp tìm kiếm, hàm INDEX MATCH thường được coi là mạnh hơn hàm VLOOKUP. Đặc biệt là khi cần tra cứu ngược hoặc nhiều điều kiện. Tuy nhiên, ở trong một số tình huống phức tạp thì bạn còn có thể kết hợp cả hai hàm này vào với nhau. 

*Ví dụ: Từ bảng dữ liệu trên; bạn muốn tìm Thương hiệu của sản phẩm có Số lượng tồn là 15.

Công thức: =INDEX(C2:C5; MATCH(VLOOKUP(15; E2:E5; 1; FALSE); E2:E5; 0))

Trong đó:

• VLOOKUP(15; E2:E5; 1; FALSE): là bước tìm giá trị 15 trong vùng E2:E5
• MATCH(15; E2:E5; 0): tìm vị trí của giá trị 15 trong vùng E2:E5.
• INDEX(C2:C5; 3): truy xuất dòng thứ 3 của vùng C2:C5 (giả sử là cột “Giá”).

*Kết quả: 

Ví dụ 4: Kết hợp hàm INDEX MATCH với hàm IF

Việc kết hợp hàm INDEX MATCH trong Google Sheets với hàm IF giúp đưa ra một thông báo tùy chỉnh hoặc thực hiện hành động nào đó theo yêu cầu.

*Ví dụ: Từ bảng dữ liệu trên, bạn muốn tìm Giá (VNĐ) của sản phẩm “Smartwatch”. Nếu “Smartwatch” không có trong danh sách, hiển thị thông báo “Sản phẩm không có trong kho” thay vì lỗi #N/A.

Công thức:  =IF(ISNA(MATCH(“Smartwatch”, B2:B5, 0)), “Sản phẩm không có trong kho”, INDEX(D2:D5, MATCH(“Smartwatch”, B2:B5, 0)))

Trong đó:

• MATCH(“Smartwatch”, B2:B5, 0): Tìm vị trí của “Smartwatch” trong cột Sản phẩm (B2:B5).
• ISNA(…): Kiểm tra xem kết quả MATCH có trả về lỗi #N/A (tức là không tìm thấy không).
• IF(ISNA(…), “Sản phẩm không có trong kho”, …): Nếu không tìm thấy → trả về thông báo “Sản phẩm không có trong kho”.
• INDEX(D2:D5, MATCH(…)) : Nếu tìm thấy → trả về giá từ cột Giá (VNĐ) ứng với dòng tìm được.

*Kết quả: 

Lỗi thường gặp khi sử dụng hàm INDEX MATCH trong Google Sheets

Khi sử dụng hàm INDEX MATCH trong Google Sheets, đôi khi bạn cũng sẽ gặp phải các lỗi. Do đó, để xử lý hiệu quả thì trước tiên bạn phải hiểu rõ nguyên nhân, sau đó mới có thể khắc phục được từng lỗi. 

Lỗi #N/A

Lỗi #N/A thường xảy ra khi hàm MATCH không tìm thấy giá trị mà bạn đang tìm kiếm trong phần phạm vị được chỉ định. Nguyên nhân chủ yếu dẫn đến lỗi này là do:

• Giá trị tìm kiếm không tồn tại, do nhập vào cho giá_trị_tìm_kiếm trong hàm MATCH không có trong cột hoặc hàng.
• Giá trị tìm kiếm có thể bị sai chính tả, có khoảng trắng thừa ở đầu hoặc cuối, hoặc có định dạng khác biệt.
• Phạm vi cung cấp cho hàm MATCH (phạm_vi_tìm_kiếm) không bao gồm ô chứa giá trị.
• Loại tìm kiếm không chính xác, khi sử dụng 1 hoặc -1 cho đối số loại_tìm_kiếm của hàm MATCH nhưng dữ liệu không được sắp xếp đúng thứ tự.

Cách khắc phục nhanh chóng lỗi #N/A như sau:

• Đảm bảo rằng giá trị bạn cung cấp cho giá_trị_tìm_kiếm hoàn toàn chính xác.
• Kiểm tra lại phạm_vi_tìm_kiếm của hàm MATCH bao gồm tất cả các ô có thể chứa giá trị bạn muốn tìm.
• Sử dụng 0 cho loại_tìm_kiếm.

Lỗi #REF

Lỗi #REF! xuất hiện khi có công thức tham chiếu đến một ô không hợp lệ. Nguyên nhân chính gây ra lỗi này chủ yếu là do:

• Đã xóa một hàng, cột hoặc thậm chí một Sheet mà công thức đang dựa vào.
• Đã đổi tên hoặc xóa Sheet mà công thức tham chiếu.

Cách khắc phục của lỗi #REF như sau:

• Nếu bạn vừa mới xóa thứ gì đó, hãy thử hoàn tác hành động đó ngay lập tức (Ctrl + Z hoặc sử dụng nút hoàn tác trên thanh công cụ).
• Bạn có thể đặt tên cho dải ô đó, bởi khi di chuyển hoặc chèn/xóa hàng/cột, Google Sheets sẽ tự động điều chỉnh Tên Phạm vi.

Lỗi #ERROR

Lỗi #ERROR! thường cho biết có vấn đề về cú pháp hoặc một lỗi không xác định khác trong công thức. Nguyên nhân chính khiến lỗi này xuất hiện là do:

• Cú pháp thiếu một dấu ngoặc đơn, một dấu phẩy, hoặc một ký tự cần thiết khác trong công thức.
• Tham số không đúng kiểu dữ liệu hoặc không đúng định dạng cho một hàm.

Cách khắc phục của lỗi #ERROR như sau:

• Đảm bảo cú pháp của hàm INDEX MATCH có các dấu ngoặc đơn và dấu phẩy đều được đặt đúng vị trí và không bị thiếu.
• Đảm bảo phạm_vi_tìm_kiếm trong MATCH phải là một hàng hoặc một cột duy nhất.

Lỗi #VALUE

Lỗi #VALUE! xảy ra khi một hàm nhận được một đối số có kiểu dữ liệu không mong muốn hoặc khi có vấn đề trong việc chuyển đổi kiểu dữ liệu. 

Nguyên nhân chủ yếu là do:

• Phép toán không hợp lệ trên kiểu dữ liệu, ví dụ như thực hiện phép cộng hoặc trừ trên một ô chứa văn bản mà không thể chuyển đổi thành số.
• Hàm MATCH luôn trả về số dương, nhưng nếu lồng các hàm khác và dẫn đến việc cung cấp một số âm cho số_thứ_tự_hàng hoặc số_thứ_tự_cột của INDEX, lỗi này có thể xuất hiện.

Cách khắc phục lỗi #VALUE như sau:

• Kiểm tra định dạng dữ liệu nhất quan, đặc biệt sử dụng Định dạng -> Số để kiểm tra và điều chỉnh.
• Đảm bảo kết quả của MATCH là số nguyên dương. 

Lưu ý quan trọng khi kết hợp  hàm INDEX và MATCH trong Google Sheets

Để biết cách tận dụng được tối đa tiềm năng của hàm INDEX MATCH trong Google Sheets, người dùng cần phải lưu ý một số điểm quan trọng sau đây:

• MATCH chỉ hoạt động với phạm vi 1 chiều (hàng hoặc cột)

Hàm MATCH giúp tìm kiếm một giá trị trong một danh sách và chỉ chấp nhận phạm_vi_tìm_kiếm là một hàng duy nhất hoặc một cột duy nhất. Nếu bạn cung cấp một phạm vi 2 chiều cho hàm MATCH, nó sẽ trả về lỗi #N/A hoặc #ERROR!.

• Luôn sử dụng 0 cho loại_tìm_kiếm trong hàm MATCH để tìm kiếm chính xác

Để đảm bảo luôn tìm kiếm một giá trị khớp chính xác với giá_trị_tìm_kiếm, hãy luôn đặt đối số loại_tìm_kiếm của hàm MATCH là 0. Nếu muốn bỏ qua đối số này hoặc sử dụng 1 hoặc -1, hàm MATCH sẽ thực hiện tìm kiếm tương đối và yêu cầu dữ liệu sắp xếp tăng dần hoặc giảm dần. Tuy nhiên, dữ liệu không được sắp xếp đúng cách sẽ dẫn đến việc kết quả sai lệch.

• Đảm bảo phạm vi của INDEX và MATCH nhất quán về kích thước (khi tìm kiếm 2 chiều)

Khi sử dụng hàm INDEX MATCH trong Google Sheets để tìm kiếm 2 chiều. Bạn phải đảm bảo sử dụng đúng công thức là =INDEX(phạm_vi_dữ_liệu_lớn, MATCH_cho_hàng, MATCH_cho_cột).

Lời kết

Như vậy, bài viết đã cung cấp một cái nhìn toàn diện hàm INDEX MATCH trong Google Sheets. Từ những khái niệm cơ bản nhất cho đến cách kết hợp chúng trong các tình huống phức tạp như tìm kiếm nhiều điều kiện. Nếu bạn còn bất cứ thắc mắc nào liên quan đến hàm INDEX MATCH, hoặc cần hỗ trợ về các vấn đề khác trong Google Sheets, đừng ngần ngại liên hệ với GCS Việt Nam để được tư vấn và hỗ trợ từ các chuyên gia chuyên nghiệp nhất!

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777