Hướng dẫn cách sử dụng hàm INDEX trong Google Sheet để trả về giá trị nhanh chóng

Hàm INDEX là một trong những hàm nâng cao trong Google Sheet giúp cho người dùng được thuận tiện hơn trong việc xử lý số liệu. Vậy hôm nay, hãy cùng chúng mình xem bài viết hướng dẫn cách sử dụng hàm INDEX trong Google Sheet cực đơn giản và dễ hiểu bạn nhé!

Hàm INDEX là hàm gì? Hàm INDEX trong Google Sheet có ứng dụng thế nào? 

Hàm INDEX là hàm gì?

Hàm INDEX là một hàm trả về mảng, giúp bạn lấy được các giá trị tại một ô trong bảng.

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

=INDEX(Array;Row_num;[Column_num])

Trong đó:

+ Array: Là một vùng ô hoặc một hàng số mảng nào đó, đây là dữ liệu bắt buộc.

+ Row_num: Là hàng được chọn từ trong mảng từ đó để trả về một giá trị.

+ Column_num: Là chọn một cột trong mảng từ đó trả về một giá trị.

Ví dụ cơ bản về hàm INDEX

Cho bảng sau đây bao gồm các trường như: Tên học sinh, Lớp, Điểm trung bình, Kết quả. Bạn hãy dùng hàm INDEX này để lấy ra giá trị "Nguyễn Thị B" có trong bảng dữ liệu. 

Cách thực hiện: Để thực hiện yêu cầu đề bài, ta cần lấy giá trị hàng thứ 2 và cột thứ 1 trong bảng.

Công thức của hàm INDEX: =INDEX(A2:D7;2;1)

Ví dụ về hàm INDEX như hình trên

Ứng dụng của hàm INDEX trong Google Sheet 

+ Hàm INDEX là hàm dùng để lấy giá trị trong một bảng dữ liệu.

+ Hàm INDEX có thể kết hợp với các hàm khác để người dùng có thể tính toán trên dữ liệu một cách nhanh chóng.

Các cách sử dụng hàm INDEX cơ bản trong Google Sheet

Các cách sử dụng hàm INDEX cơ bản

Cho bảng dữ liệu như sau:

Bạn có thể thực hành với hàm INDEX cơ bản như sau:

Hàm INDEX kết hợp với hàm MATCH

Vẫn sử dụng lại bảng dữ liệu trên, bạn hãy thực hiện việc kết hợp hàm INDEX và hàm MATCH như ví dụ bên dưới đây.

Công thức của hàm: =INDEX(A13:D18;MATCH("Hoàng Văn D";A13:A18;0);4)

Ý nghĩa của công thức: Lấy ra giá trị kết quả của bạn có tên là Hoàng Văn D.

Kết hợp INDEX với MATCH như hình trên

Hàm INDEX kết hợp với hàm VLOOKUP

Công thức của hàm: =VLOOKUP(INDEX(A13:D18;1;1);A12:D18;3;FALSE)

Ý nghĩa của công thức: Hãy lấy giá trị điểm trung bình của bạn đầu tiên trong danh sách.

Kết hợp INDEX với VLOOKUP như hình trên

Hàm INDEX kết hợp với hàm IMPORTRANGE

Công thức của hàm: =INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1XdJPmZFgEX6zGevYxAZsyJ4_1cMUT9HY-ZsBhZAvgcQ/fdfdedit#gid=0";"Trang tính1!A4:B9");1;1)

Ý nghĩa của công thức: Bạn hãy lấy số báo danh của bạn có tên Nguyễn Văn A ở cột 1 hàng 1 trong trang tính Số báo danh.

Kết hợp INDEX với IMPORTRANGE như hình trên

Hàm INDEX kết hợp với hàm COUNTIF

Công thức của hàm: =SUMIF(B13:B18;INDEX(A13:D18;1;2);C13:C18)/COUNTIF(B13:B18;INDEX(A13:D18;1;2))

Ý nghĩa của công thức: Bạn hãy tính điểm trung bình của các bạn học sinh ở lớp 7A.

Kết hợp INDEX với COUNTIF như hình trên

Hàm INDEX kết hợp với hàm SUM

Công thức của hàm: =SUM(INDEX(A13:D18;1;3);INDEX(A13:D18;2;3))

Ý nghĩa của công thức: Bạn hãy tính tổng điểm của bạn ở hàng 1 và hàng 2.

Kết hợp INDEX với SUM như hình trên

Hàm INDEX kết hợp với hàm AVERAGE

Công thức của hàm:  =AVERAGE(INDEX(A12:D18;2;3);INDEX(A12:D18;3;3))

Ý nghĩa của công thức: Bạn hãy tính điểm trung bình của bạn ở vị trí thứ 1 và thứ 2.

Kết hợp INDEX với AVERAGE như hình trên

Hàm INDEX kết hợp với hàm VLOOKUP, hàm MATCH

Công thức của hàm: =VLOOKUP(index(A2:F9;match(G28;A2:A9;0);2);B2:F9;5;false)

Ý nghĩa của công thức: Bạn hãy tính trung bình doanh số của nhân viên dựa theo mã số 155.

Kết hợp nhiều điều kiện với INDEX như hình trên

Các lưu ý quan trọng khi sử dụng hàm INDEX trong Google Sheet

- Hàm INDEX là hàm chỉ mạnh khi kết hợp cùng với hàm MATCH.

- Bạn có thể sử dụng hàm VLOOKUP khi giá trị cần tra cứu nằm ở bên trái của thuộc tính mà bạn mong muốn trả về. Bạn còn có thể dùng hàm INDEX và hàm MATCH bất kể vị trí của giá trị tra cứu trong tập dữ liệu là ở đâu.

- Nếu bạn đặt hàng hoặc cột thành 0, hàm INDEX này sẽ trả lại mảng giá trị cho toàn bộ cột hoặc hàng tương ứng.

Một số lỗi thường gặp phải khi sử dụng hàm INDEX

Lỗi #N/A

- Giải thích: Lỗi #N/A là lỗi xuất hiện khi hàm không tìm được giá trị cần tìm.

- Cách khắc phục lỗi #N/A:

+ Để khắc phục lỗi #N/A, bạn cần kiểm tra lại các bảng dữ liệu đối chiếu trong các hàm tìm kiếm và sắp xếp dữ liệu so sánh trong bảng theo giá trị được xếp từ nhỏ đến lớn.

+ Bạn cũng cần đảm bảo dữ liệu tìm kiếm và đối chiếu này phải cùng một kiểu định dạng dữ liệu đồng thời giá trị của dữ liệu tìm kiếm không được nhỏ hơn giá trị nhỏ nhất của dữ liệu dùng để đối chiếu.

+ Bạn cũng nên lồng các hàm tìm kiếm vào với hàm xử lý lỗi IFNA hoặc IFERROR để giá trị trả về không bị báo lỗi #N/A.

- Ví dụ sửa lỗi cơ bản:

+ Dùng hàm IFNA: Chúng ta sẽ dùng cú pháp như sau để phát hiện lỗi.

=IFNA(index(A2:F9;match(B2;A2:F9;0);1;5);"bị lỗi NA") 

Dùng IFNA phát hiện lỗi như hình trên

+ Dùng hàm IFERRON: Chúng ta sẽ dùng cú pháp sau đây để phát hiện lỗi.

=IFERROR(index(A2:F9;match(B2;A2:F9;0);1;5);"bị lỗi NA") 

Dùng IFERRON nhận biết lỗi như hình trên

Lỗi #ERROR!

- Giải thích: Lỗi #ERROR! xảy ra do cú pháp hàm mà bạn nhập bị sai.

- Cách khắc phục: Để khắc phục lỗi này, bạn cần kiểm tra lại cú pháp hàm mà bạn đã nhập. 

- Ví dụ: Trong bài viết, khi bạn xảy ra nhầm lẫn giữa dấu chấm phẩy  ";" và dấu phẩy "," thì Google Sheet sẽ báo lỗi #ERROR!. Lúc này, bạn cần sửa lại cú pháp hàm thành dấu chấm phẩy ";" thì sẽ không còn bị lỗi nữa.

Cách sửa lỗi #ERROR! như hình trên

Lỗi #NUM!

- Giải thích: Lỗi #NUM! thường xuất hiện trong các hàm sau đây: Phép nhân lũy thừa, hàm căn bậc 2, hàm LOGARIT, hàm IRR, hàm lãi suất RATE và giá trị nằm ngoài vùng chọn.

- Cách khắc phục lỗi: Bạn cần chú ý điều kiện sử dụng số (số nguyên, số âm hay số dương,…) và vùng cần chọn của giá trị.

- Ví dụ như sau: Trong bảng, ta thấy chỉ quét vùng chọn có 4 cột, nhưng trong công thức lại chọn đến cột 5, điều này đã dẫn tới lỗi #NUM!, lúc này ta cần phải sửa lại giá trị cột thành 3 thì sẽ hết lỗi.

Cách sửa lỗi #NUM! như hình trên

Các bài tập sử dụng hàm INDEX cơ bản trong Google Sheet 

Cho bảng sau đây: Bảng có bao gồm các trường dữ liệu như: ID, TEN_NV, DOANH_SO. Bạn hãy dùng hàm INDEX cho 1 số ví dụ sau đây để hiểu rõ cách sử dụng cơ bản nhé!

Câu hỏi 1: Dựa vào kiến thức hàm đã học, bạn hãy tìm xem ai là người đứng thứ 3 trong danh sách nhân viên kế bên?

Trả lời: =index(A2:C9;3;2)

Giải thích: Vì yêu cầu lấy người thứ 3 nên giá trị hàng sẽ là 3 và giá trị của cột là cột chứ tên là 2.

Hướng dẫn và kết quả câu hỏi 1 như hình trên

Câu hỏi 2: Dựa vào kiến thức hàm đã học, bạn hãy tìm xem doanh số của Tố là bao nhiêu?

Trả lời: =index(A2:C9;5;3)

Giải thích: Ta cần tìm thấy Tố ở hàng thứ 5 và cột doanh số ở cột 3.

Hướng dẫn và kết quả câu hỏi 2 như hình trên

Câu hỏi 3: Bạn hãy tìm ID của nhân viên có tên là Vy?

Trả lời: =index(A2:C9;7;1)

Giải thích: Ta sẽ lấy hàng chứa người có  tên Vy là hàng 7 và cột ID sẽ là cột 1.

Hướng dẫn và kết quả câu hỏi 3 như hình trên

Câu 4: Bạn hãy lấy ra tên của tất cả các nhân viên trong danh sách kế bên?

Trả lời: =index(B2:B9)

Giải thích: Dựa theo yêu cầu đề bài, lúc này chúng ta sẽ lấy vùng giá trị từ B2 đến B9.

Hướng dẫn và kết quả câu hỏi 4 như hình trên

Các câu hỏi thường gặp khi sử dụng hàm INDEX trong Google Sheet 

Tại sao sử dụng hàm INDEX và hàm MATCH trong Google Sheets tốt hơn hàm VLOOKUP?

- Trả lời:

+ Hàm VLOOKUP là hàm có công thức 1 chiều. Ví dụ khi bạn cần tìm các giá trị bên trái nhưng ô điều kiện bên phải thì bạn không thể dùng hàm VLOOKUP này để tìm ra giá trị được.

+ Hàm VLOOKUP sẽ bị sai nếu chúng ta thêm hoặc bớt cột vào trong bảng tính. Bởi khi ta dùng hàm VLOOKUP, ta sẽ phải chỉ ra cột cần lấy giá trị là cột nào, nên khi thêm bớt cột sẽ có thể dẫn đến số liệu bị sai lệch, với hàm INDEX, bạn sẽ được điều chỉnh theo nên sẽ giúp hạn chế sai sót.