Hàm INDEX trong Excel: cách dùng và ví dụ minh họa

Khi làm việc trên trang tính Excel, chúng ta đều đã rất quen với hàm HLOOKUP, VLOOKUP được sử dụng làm hàm tìm kiếm, tham chiếu dữ liệu. Tuy nhiên, liệu bạn đã biết đến hàm INDEX trong Excel với chức năng tương tự mà lại còn cực kì hữu dụng chưa? Nếu các bạn chưa biết thì hãy để Gitiho giới thiệu đến các bạn về hàm tham chiếu trong Excel này qua bài viết dưới đây nhé.

Cú pháp và cách sử dụng cơ bản của hàm INDEX trong Excel

Sử dụng hàm INDEX trong Excel với mảng dữ liệu

Dạng mảng của hàm INDEX trong Excel giúp người dùng tìm kiếm giá trị của một phần tử trong một mảng dựa trên số hàng và cột mà bạn đã chỉ định. 

Cấu trúc hàm INDEX trong Excel dạng mảng

=INDEX (array, row_num, [column_num])

Trong đó:

• array: Là một dải các ô, có tên dải hoặc bảng.
• row_num: Là số thứ tự của hàng trong mảng mà bạn muốn được trả về một giá trị. Nếu row_num bị bỏ qua, column_num là bắt buộc.
• column_num: Là số thứ tự của cột để Excel trả về một giá trị. Nếu column_num bị bỏ qua, row_num là bắt buộc.

Ví dụ, nếu bạn nhập công thức =INDEX(A1: D6,4,3), Excel sẽ trả về giá trị tại giao điểm của hàng thứ 4 và cột thứ 3 trong mảng A1: D6, tức là là giá trị trong ô C4.

Nếu bạn chưa rõ về cách sử dụng hàm tham chiếu trong Excel này, hãy tìm hiểu một ví dụ cụ thể:

 

Thay vì nhập tay số thứ tự của hàng và cột vào công thức, chúng ta còn một cách là cung cấp tham chiếu ô. Công thức hàm INDEX trong Excel được sử dụng như sau:

=INDEX($B$2:$D$6,G2,G1)

Lưu ý: Chúng ta nên sử dụng tham chiếu tuyệt đối ($B$2:$D$6) thay vì tham chiếu tương đối (B2:D6) để tham chiếu không bị thay đổi khi chúng ta copy và dán công thức với các ô khác trong trang tính Excel. Ngoài ra, bạn có thể chuyển đổi dải thành bảng bằng tổ hợp phím Ctrl + T và tham chiếu bằng tên bảng.

Một số lưu ý khi sử dụng hàm INDEX trong Excel dạng mảng

Chúng ta cần nhớ một số điều dưới đây khi sử dụng hàm INDEX dạng mảng làm hàm tham chiếu trong Excel.

1. Nếu bạn muốn thay đổi dữ liệu trong mảng chỉ bao gồm một hàng hoặc một cột, bạn có thể xác định hoặc không xác định đối số row_num hoặc column_num tương ứng.
2. Khi thay đổi dữ liệu trong mảng bao gồm nhiều hơn một dòng và row_num bị bỏ qua hoặc bằng 0 thì hàm INDEX trong Excel trả về một mảng của toàn bộ cột. Tương tự, nếu mảng bao gồm nhiều cột và đối số column_num bị bỏ qua hoặc bằng 0, hàm tham chiếu trong Excel sẽ trả về toàn bộ hàng.
3. Các đối số row_num và column_num phải tham chiếu đến một ô trong mảng dữ liệu. Nếu không, hàm INDEX trong Excel sẽ trả về lỗi #REF!.

Sử dụng hàm INDEX trong Excel với tham chiếu

Hàm INDEX ở định dạng tham chiếu có chức năng làm hàm tham chiếu trong Excel, trả về ô tham chiếu tại giao điểm của hàng và cột bạn đã xác định.

Cấu trúc hàm INDEX trong Excel dạng tham chiếu

=INDEX(reference, row_num, [column_num], [area_num])

Trong đó:

• reference: Là một hoặc nhiều dải.

Trong trường hợp có nhiều hơn một phạm vi, hãy tách các khoảng phạm vi bằng dấu phẩy và kèm theo đối số tham chiếu trong ngoặc đơn, ví dụ (A1:B5,D1:F5).

Nếu mỗi dãy trong vùng tham chiếu chỉ chứa một hàng hoặc một cột, đối số row-num hoặc column-num tương ứng là tùy chọn.

• row_num: Là số thứ tự của hàng nằm trong khoảng yêu cầu Excel trả về một ô tham chiếu, tương tự như dạng mảng.
• column_num: Là số thứ tự cột để trả về một ô tham chiếu, tương tự như dạng mảng.
• area_num: Là một tham số tùy chọn chỉ ra mảng từ đối số tham chiếu. Nếu bỏ qua, công thức hàm INDEX trong Excel sẽ trả lại kết quả là mảng đầu tiên được liệt kê trong danh sách tham chiếu.

Chúng ta tiếp tục với một ví dụ sau đây để hiểu rõ hơn cách thức hoạt động của hàm INDEX trong Excel dạng tham chiếu. Ví dụ, chúng ta có giá trị ô D7 được hàm INDEX trả về theo công thức =INDEX((A2:D3,A5:D7),3,4,2).

Trong công thức này, hàm tham chiếu trong Excel trả về giá trị tại giao điểm của hàng thứ 3 và cột thứ 4 trong khu vực thứ hai (A5: D7).

Một số lưu ý khi sử dụng hàm INDEX trong Excel dạng tham chiếu

1. Nếu đối số row_num hoặc column_num được để 0, thì một công thức hàm INDEX trong Excel sẽ trả về tham chiếu cho toàn bộ cột hoặc hàng tương ứng.
2. Nếu cả hai row_num và column_num bị bỏ qua, hàm INDEX trong Excel trả về vùng được chỉ định trong đối số area_num.
3. Tất cả các _num arguments (row_num, column_num và area_num) phải tham chiếu một ô trong phạm vi tham chiếu. Nếu không, hàm tham chiếu trong Excel sẽ trả về lỗi #REF!.

Xem thêm: Hướng dẫn giải bài tập Excel với hàm INDEX và hàm MATCH chi tiết

Cách sử dụng hàm INDEX trong Excel

Hàm INDEX trong Excel có thể trở nên vô cùng lợi hại khi kết hợp với các hàm khác trong Excel. Chúng ta hãy cùng tìm hiểu qua ví dụ dưới đây nhé.

Mặc dù việc đặt tên cho các bảng hoặc các mảng có thể làm khiến công thức dài hơn, nhưng nó giúp cho chúng linh hoạt và chúng ta dễ dàng xác định và đọc hơn. Để điều chỉnh bất kỳ công thức INDEX nào cho các bảng tính của bạn, bạn chỉ cần chỉnh sửa một cái tên duy nhất. Trong trường hợp này, chúng ta sẽ sử dụng bảng tên SourceData kết hợp với hàm INDEX trong Excel.

Lấy dữ liệu từ vị trí thứ N bằng hàm INDEX trong Excel

Đây là cách sử dụng hàm INDEX trong Excel đơn giản nhất. Để lấy dữ liệu ở một vị trí nhất định trong danh sách, bạn chỉ cần viết công thức hàm

 =INDEX(range n)

Trong đó: 

• range là một dải ô hoặc dải ô được đặt tên
• n là vị trí của dữ liệu mà bạn muốn nhận.

Khi làm việc với bảng Excel, bạn có thể dùng trỏ chuột chọn cột và Excel sẽ đưa tên của cột cùng với tên của bảng vào công thức hàm tham chiếu trong Excel như trong hình.

Nếu bạn muốn Excel trả về giá trị của ô nằm tại giao điểm của một hàng và một cột xác định, tương tự, bạn sử hàm INDEX trong Excel với cả hai thông số – số hàng và số cột.

Trong ví dụ phía trên của chúng ta, để tìm ra hành tinh lớn thứ 2 trong hệ mặt trời, bạn sắp xếp bảng theo cột Đường kính và sử dụng công thức hàm tham chiếu trong Excel như sau:

=INDEX(SourceData,2,3)

Trong đó:

• Array là tên bảng, hoặc một tham chiếu dải, tương ứng với SourceData.
• Row_num là 2 vì bạn đang tìm kiếm mục thứ hai trong danh sách
• Column_num là 3 vì Đường kính là cột thứ 3 trong bảng.

Trong trường hợp bạn muốn Excel trả tên của hành tinh thay vì đường kính, bạn chỉ cần thay đổi column_num thành 1. Ngoài ra, bạn có thể sử dụng một tham chiếu ô trong đối số row_num và/hoặc column_num để làm cho công thức INDEX của bạn linh hoạt hơn như sau:

Nhận tất cả giá trị trong một hàng hoặc một cột bằng hàm INDEX trong Excel

Không chỉ lấy ra giá trị từ một ô duy nhất, hàm INDEX trong Excel có thể trả về một dải các giá trị từ toàn bộ các hàng hoặc các cột. Để làm được điều này, tất cả những gì bạn phải làm là bỏ qua đối số row_num hoặc đặt nó là 0 để có giá trị toàn bộ cột và, tương tự, bạn bỏ qua hoặc 0 trong column_num để có giá trị toàn bộ hàng.

Tuy nhiên, cách sử dụng này của hàm INDEX trong Excel khó áp dụng thành công vì Excel không thể trả một dải các giá trị trong một ô duy nhất. Thay vào đó, sau khi chạy hàm tham chiếu trong Excel, bạn sẽ nhận được lỗi #VALUE!. Một cách giải quyết cho vấn đề này là sử dụng hàm INDEX kết hợp với các hàm trong Excel khác, chẳng hạn như SUM hoặc AVERAGE. Như vậy, bạn sẽ thu được kết quả tuyệt vời.

Ví dụ, bạn có thể sử dụng công thức hàm INDEX trong Excel sau để tính nhiệt độ trung bình của hành tinh trong hệ mặt trời:

=AVERAGE(INDEX (SourceData,, 4))

Trong đó,

• column_num là 4 vì Nhiệt độ là cột thứ 4 trong bảng Excel.
• row_num bị bỏ qua.

Tương tự như trên, chúng ta có thể tìm được nhiệt độ tối thiểu và tối đa theo công thức hàm INDEX trong Excel kết hợp hàm MAX và hàm MIN:

=MAX(INDEX(SourceData,, 4))

=MIN(INDEX(SourceData,, 4))

Bên cạnh đó, chúng ta còn có thể tính tổng khối lượng hành tinh với Khối lượng là cột thứ 2 trong bảng:

=SUM(INDEX(SourceData,, 2))

Trong thực tế, bạn không cần dùng đến hàm INDEX trong ví dụ trên mà chỉ cần viết =AVERAGE(dải) hoặc =SUM(dải) là nhận được kết quả chính xác. Khi làm việc với dữ liệu thực, hàm INDEX trong Excel mới trở nên hữu ích như một phần của các công thức phức tạp giúp phân tích dữ liệu.

Sử dụng hàm INDEX trong Excel với các hàm SUM, hàm AVERAGE, hàm MAX, hàm MIN

Từ các ví dụ trước, bạn có thể nghĩ rằng hàm INDEX trong Excel trả về các giá trị, nhưng thực tế là nó trả về một tham chiếu đến ô có chứa giá trị. Nếu bạn còn thấy khó hiểu thì hãy cùng tìm hiểu ví dụ sau đây/

Với kết quả của một công thức INDEX mang tính tham chiếu, chúng ta có thể sử dụng nó trong các hàm khác để tạo ra một dải động, tức là dải thay đổi theo giá trị ta nhập vào các ô. 

Giả sử bạn có một công thức =AVERAGE (A1: A10) trả về giá trị trung bình của các ô A1:A10. Thay vì viết dải trực tiếp vào trong công thức, chúng ta có thể thay thế A1 hoặc A10 hoặc cả hai bằng hàm INDEX trong Excel như sau:

=AVERAGE(A1: INDEX(A1: A20,10))

Trong công thức hàm INDEX:

• row-num là 10
• col-num bỏ qua

Cả hai công thức trên sẽ trả về kết quả như nhau vì hàm INDEX cũng trả về một tham chiếu đến ô A10 . Sự khác biệt là công thức hàm AVERAGE kết hợp hàm INDEX trong Excel sử dụng cho dải động và khi bạn thay đổi đối số row_num trong INDEX, thì dải mà được xử lý bởi hàm AVERAGE sẽ thay đổi và công thức sẽ trả về một kết quả khác.

Tuy cách thức công thức INDEX vận hành có vẻ quá phức tạp đối với phần lớn chúng ta, nhưng nó có các ứng dụng vô cùng thực tế. Cùng chúng mình tìm hiểu như trong các ví dụ sau đây nhé.

Tính trung bình các mục trong n mục đứng đầu danh sách bằng hàm AVERAGE kết hợp hàm INDEX trong Excel

Giả sử bạn muốn biết đường kính trung bình của n hành tinh lớn nhất trong hệ mặt trời. Bước đầu tiên, bạn sắp xếp bảng theo cột Đường kính từ lớn nhất đến nhỏ nhất. Sau đó, hãy sử dụng công thức hàm Average kết hợp hàm INDEX trong Excel như sau:

= AVERAGE(C5:INDEX(SouceData[Đường kính], B1))
 

Tính tổng các giá trị giữa 2 mục cụ thể bằng hàm SUM kết hợp hàm INDEX trong Excel

Trong trường hợp bạn muốn xác định giới hạn trên và giới hạn dưới trong công thức của mình, chỉ cần sử dụng hai hàm INDEX trong Excel để trả lại mục đầu tiên và mục cuối cùng bạn muốn. Vẫn sử dụng ví dụ trên, ta có thể nhập công thức sau để trả về tổng các giá trị trong cột Đường kính giữa hai dữ liệu được chỉ định trong ô B1 và ​​B2:

= SUM(INDEX(SourceData[Đường kính], B1):INDEX(SourceData[Đường kính], B2)) 

Xem thêm: Hướng dẫn tất tần tật cách dùng hàm INDEX trong Excel chuẩn nhất

Tạo các dải động và danh sách theo mục bằng hàm INDEX trong Excel

Khi bạn bắt đầu sắp xếp dữ liệu trong bảng tính Excel, bạn có thể không biết sẽ có tất cả bao nhiêu mục bạn. Bạn muốn tạo ra một dải được đặt tên – bao gồm tất cả các ô có dữ liệu, đối với một cột có số mục không xác định, ví dụ từ A1 đến An (hàng thứ n) và bạn muốn dải được điều chỉnh tự động khi bạn thêm mục mới hoặc xóa một số mục hiện có. Ví dụ bạn hiện có 10 mục, dải được đặt tên của bạn sẽ là A1: A10. Nếu bạn thêm một mục nhập mới, phạm vi tên sẽ tự động mở rộng thành A1: A11. Sau đó, nếu bạn xóa dữ liệu mới được thêm vào, phạm vi sẽ tự động quay trở lại A1: A10.

Bằng cách sử dụng hàm INDEX trong Excel, bạn không phải liên tục cập nhật tất cả các công thức trong bảng tính của bạn để đảm bảo hàm tham chiếu trong Excel trả về kết quả chính xác.

Sử dụng hàm INDEX trong Excel kết hợp hàm OFFSET

Chúng ta có công thức hàm như sau:

=OFFSET(Sheet_Name!$A$1,0,0,COUNTA(Sheet_Name!$A:$A),1)

Sử dụng hàm INDEX trong Excel kết hợp hàm COUNTA

Chúng ta có công thức hàm như sau:

=Sheet_Name!$A$1:INDEX(Sheet_Name!$A:$A,COUNTA(Sheet_Name!$A:$A))

Trong cả hai công thức trên, A1 là ô có chứa mục đầu tiên của danh sách và dải động được tạo ra bởi cả hai công thức sẽ giống hệt nhau. Sự khác biệt nằm ở cách tiếp cận. Hàm INDEX trong Excel tìm thấy một ô ở giao điểm của một hàng và cột cụ thể, sau đó hàm OFFSET di chuyển từ điểm làm mốc bởi một số hàng và / hoặc các cột nhất định. Còn hàm COUNTA, được sử dụng trong cả hai công thức, có nhiệm vụ tính số lượng các ô không rỗng trong cột và đảm bảo rằng chỉ những ô có dữ liệu được bao gồm trong dải đã đặt tên.

Cùng xem cách hàm INDEX trong Excel giúp bạn làm một danh sách tùy chọn như thế nào nhé.

Mẹo: Cách dễ nhất để tạo một danh sách tùy chọn được cập nhật tự động trong Excel là đặt tên cho danh sách và đảm bảo danh sách đó được liệt kê dựa trên một bảng. Như vậy, bạn sẽ không cần bất kỳ công thức phức tạp nào vì các bảng Excel là dải động.

Hàm INDEX trong Excel khi kết hợp với hàm VLOOKUP và hàm MATCH

Thực hiện các hàm Vlookup theo chiều dọc trong Excel là nơi mà hàm INDEX thực sự tỏa sáng. Nếu bạn đã từng thử sử dụng hàm VLOOKUP, bạn sẽ nhận thức được nhiều hạn chế của nó, bao gồm việc không có khả năng kéo giá trị từ các cột qua bên trái của cột tra cứu hoặc giới hạn 255 ký tự cho một giá trị tra cứu.

Hàm INDEX và hàm MATCH tốt hơn VLOOKUP ở nhiều khía cạnh như sau:

• Không có vấn đề với dò tìm bên trái.
• Không giới hạn kích thước giá trị tra cứu.
• Không yêu cầu phân loại (VLOOKUP với kết quả tương đối khi yêu cầu sắp xếp cột tra cứu theo thứ tự tăng dần).
• Bạn được tự do chèn và loại bỏ các cột trong một bảng mà không cần cập nhật mỗi công thức liên quan.
• Không làm chậm Excel.

Bạn sử dụng INDEX / MATCH theo cách sau:

=INDEX(column to return a value from,(MATCH (lookup value, column to lookup against, 0))

Quay trở lại với ví dụ về bảng tính hệ mặt trời, chúng ta mở bảng dữ liệu gốc với Hành tinh là cột đầu tiên bên phải. Theo đó, công thức hàm INDEX / MATCH vẫn lấy giá trị phù hợp từ cột bên trái mà không gặp bất kì trở ngại nào.

Sử dụng hàm INDEX trong Excel để lấy một dải từ danh sách các dải

Bên cạnh các tính năng trên, hàm INDEX trong Excel còn giúp bạn có được một dải từ một danh sách các dải. Giả sử trong ví dụ dưới đây bạn có nhiều danh sách với số lượng các mục khác nhau. Trước hết, bạn đặt tên cho mỗi dải trong mỗi danh sách: ĐKHành tinh và ĐKMặttrăng.

Trong ví dụ này, ĐKHành tinh là dải 1 còn ĐKMặttrăng là dải 2, ô B1 là nơi bạn đặt số dãy. Với dữ liệu như vậy, để tính giá trị trung bình của các giá trị trong dải ô được chọn, bạn có thể sử dụng công thức hàm INDEX trong Excel như sau:

=AVERAGE(INDEX((ĐKHànhtinh, ĐKMặttrăng),,,B1))

Chúng ta đang sử dụng định dạng tham chiếu của hàm INDEX trong Excel, và số trong đối số cuối cùng (area_num) cho công thức biết phạm vi dải nào được lựa chọn.

Trong hình bên dưới, area_num (cell B1) được đặt thành 2 trong công thức tính đường kính trung bình của Mặt trăng vì dải ĐKMặttrăng đứng thứ 2 trong đối số tham chiếu.

Nếu bạn làm việc với nhiều danh sách và không muốn phải nhớ tất cả dữ liệu, bạn có thể sử dụng thêm hàm IF để thêm điều kiện. Công thức trở thành:

=AVERAGE(INDEX((ĐKHànhtinh, ĐKMặttrăng),,,IF(B1=Hành tinh”,1,IF(B1=“Mặt trăng”, 2))))

Lưu ý: Để công thức làm việc chính xác, văn bản trong B1 phải chính xác (không phân biệt chữ hoa chữ thường) như trong các thông số của hàm IF, nếu không Excel sẽ trả về lỗi #VALUE!.

Nếu bạn muốn công thức hàm tham chiếu trong Excel của mình trở nên thân thiện hơn, bạn có thể sử dụng Excel Data Validation để tạo danh sách tùy chọn để tránh lỗi chính tả và lỗi trang in.

Bước cuối cùng cho một công thức hàm INDEX trong Excel hoàn hảo 100%, bạn hãy đặt nó trong hàm IFERROR để nhắc người dùng chọn một mục từ danh sách theo mục, nếu không có lựa chọn nào được đưa ra. Công thức cuối cùng như sau:

=IFERROR(AVERAGE(INDEX((ĐKHànhtinh, ĐKMặttrăng),,,IF(B1=“Hành tinh”,1,IF(B1=“Mặt trăng”,2)))),“Hãy chọn danh sách!”)

Xem thêm: Hướng dẫn cách kết hợp hàm INDEX và MATCH thay thế hàm Vlookup trong Excel

Tổng kết

Vậy là chúng ta đã hoàn thành việc tìm hiểu hàm INDEX trong Excel và các tính năng tuyệt vời của hàm như một hàm tham chiếu trong Excel. Hi vọng bài viết đã mang lại kiến thức hữu ích để nâng cao kĩ năng tin học văn phòng của các bạn. Để biết thêm nhiều kiến thức về Excel, các bạn hãy truy cập blog Gitiho để đón đọc các bài viết hàng ngày. Bên cạnh đó, Gitiho mời các bạn đăng kí khóa học Tuyệt đỉnh Excel với chúng mình để hoàn thiện kĩ năng sử dụng Excel nhé.

Chúc các bạn thành công!