Công thức hàm INDEX trong Excel và cách sử dụng kèm ví dụ

Hàm INDEX trong Excel là một trong những hàm thuộc chủ đề hàm dò tìm, hàm tìm kiếm rất đa năng và mạnh mẽ, hàm này chủ yếu để tham chiếu tới một ô dựa vào số thứ tự hàng và cột cho sẵn. Hàm INDEX được rất nhiều người sử dụng nhưng lại ít người biết về cách sử dụng thực tế của nó. Bài viết này, Tin Học Thành Luân sẽ giới thiệu tới các bạn hàm INDEX, cách sử dụng và các ví dụ của nó một cách chi tiết nhất.

Đối với nhiều người đã biết về hàm INDEX nhưng lại chỉ biết về dạng tham chiếu 1 mảng, ngoài 1 mảng ra nó còn cho phép bạn dò tìm trong nhiều vùng và cho phép tùy chọn vùng dò tìm dựa vào thành phần cuối cùng. Hàm INDEX được chia làm 2 dạng: Dạng mảng và dạng tham chiếu nên khi bạn sử dụng hàm này cần tùy theo yêu cầu bài toán để tùy cơ ứng biến.

Đọc thêm:

• Hàm VLOOKUP trong Excel
• Hàm HLOOKUP trong Excel

Giới thiệu hàm INDEX trong Excel

Định nghĩa

Hàm INDEX trong Excel là một trong những công cụ mạnh mẽ và hữu ích trong việc truy xuất dữ liệu từ một mảng hay bảng dữ liệu. Hàm này cho phép bạn tham chiếu tới một giá trị cụ thể trong mảng dữ liệu của bạn dựa trên vị trí hàng và cột của nó. Hàm INDEX có 2 dạng: Dạng mảng và dạng tham chiếu.

Hàm INDEX dạng mảng trả về giá trị của một thành phần trong bảng hoặc mảng, được chọn bởi chỉ mục số hàng và cột. Dùng dạng mảng nếu đối số thứ nhất của hàm INDEX là một hằng số mảng.

Hàm INDEX dạng tham chiếu trả về tham chiếu của ô nằm ở giao cắt của một hàng và cột cụ thể. Nếu tham chiếu được tạo thành từ các vùng chọn không liền kề, bạn có thể chọn vùng chọn để tìm trong đó.

Cú pháp hàm INDEX dạng mảng

Để sử dụng hàm INDEX dạng mảng, bạn cần biết cú pháp cơ bản của nó. Cú pháp của hàm INDEX như sau: INDEX(array, row_num, [column_num]), trong đó:

• array là mảng dữ liệu mà bạn muốn truy cập.
• row_num là số thứ tự của hàng mà bạn muốn truy xuất giá trị.
• [column_num] là số thứ tự của cột mà bạn muốn truy xuất giá trị. Đây là đối số tùy chọn, nếu bỏ qua, giá trị mặc định là 1.

Hàm INDEX cũng có thể được sử dụng để truy xuất dữ liệu từ các hàng hoặc cột không liền kề trong mảng. Bằng cách kết hợp với các hàm khác như ROW và COLUMN, bạn có thể tùy chỉnh công thức INDEX để truy xuất đến vị trí cụ thể trong mảng dữ liệu của bạn.

Cú pháp hàm INDEX dạng tham chiếu

Cú pháp hàm INDEX dạng tham chiếu như sau: =INDEX(reference, row_num, [column_num], [area_num])

Trong đó:

• reference: 1 vùng dữ liệu hoặc nhiều vùng dữ liệu ngăn cách với nhau bởi dấu phẩy và đóng mở ngoặc các vùng trong thành phần đầu tiên
• row_num: Số hàng trong tham chiếu từ đó trả về một tham chiếu.
• [column_num]: Số cột trong tham chiếu từ đó trả về một tham chiếu.
• [area_num]: Chọn một vùng dữ liệu trong reference. Nếu area_num bỏ qua, hàm INDEX dùng vùng 1.  Các khu vực liệt kê ở đây đều phải được đặt trên một trang tính.  Nếu bạn chỉ định khu vực không trên cùng trang tính với nhau, thì nó trả về lỗi #VALUE! .

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

Hàm INDEX thường được sử dụng để tham chiếu dữ liệu tới 1 ô trong một bảng Excel. Ví dụ, giả sử bạn có một bảng dữ liệu gồm các cột “Họ và tên”, “Tuổi” và “Địa chỉ”. Bạn muốn lấy thông tin của người thứ 5 trong bảng này. Bạn có thể sử dụng hàm INDEX như sau: INDEX(A1:C5, 5, 1). Công thức này sẽ trả về giá trị của ô A5, tức là thông tin về người thứ 5 trong bảng.

Ngoài ra, bạn cũng có thể sử dụng hàm INDEX để truy cập vào các phạm vi dữ liệu không phải là một bảng hoặc một vùng đặc biệt trong bảng. Ví dụ, giả sử bạn có một danh sách các số trong cột A và bạn muốn lấy giá trị tại chỉ mục thứ 3 và thứ 6. Bạn có thể sử dụng hàm INDEX như sau: INDEX(A:A, 3) và INDEX(A:A, 6). Công thức sẽ trả về giá trị của ô thứ 3 và thứ 6 trong cột A.

Trong các trường hợp hàm VLOOKUP hoặc hàm HLOOKUP không thể dò tìm được hoặc không thể áp dụng công thức thì hàm INDEX sẽ thay thế. Do hàm INDEX rất linh động trong việc áp dụng hàng cột.

Tuy nhiên, hàm INDEX cũng có thể được sử dụng kết hợp với một số hàm khác như hàm IF hoặc MATCH để thực hiện các tính toán phức tạp hơn. Bằng cách kết hợp hàm INDEX với hàm IF, bạn có thể thiết lập các điều kiện để lấy dữ liệu từ một phạm vi khác nhau dựa trên kết quả của một điều kiện. Nào, bây giờ chúng ta sẽ đi tìm hiểu cách sử dụng hàm INDEX thông qua các ví dụ dưới đây nhé!

Ví dụ hàm INDEX đơn giản dạng mảng

Giả sử ta có bảng Thông tin các vận động viên đua xe, và chúng ta sẽ sử dụng hàm INDEX để lấy ra tên vận động viên có tên “Lê Đức Công”.

Ở đây, địa chỉ ô vận động viên “Lê Đức Công” là ô D7. Nghĩa là số thứ tự hàng là 7 và số thứ tự cột là 4, do cột D là cột số 4 đây là cách xác định số thứ tự cột hàng và cột để sau đó chúng ta điền vào công thức hoàn chỉnh.

Ta có công thức như sau: =INDEX(A1:E13, 7, 4)

Trong đó:

• A1:E13: Vùng dữ liệu để xác định vị trí tên “Lê Đức Công“
• 7: Số thứ tự hàng
• 4: Số thứ tự cột

Trong công thức trên, do mình chọn phạm vi dữ liệu là ngoài bảng Thông tin vận động viên nên chúng ta để thứ tự hàng là 7 và cột là 4. Vậy bây giờ mình chọn phạm vi bảng Thông tin vận động viên thì các chỉ số sẽ thay đổi như thế nào? Rất đơn giản, khi bạn chọn phạm vi bảng Thông tin vận động viên bắt đầu từ ô “STT” thì khi bạn điền số thứ tự cột bạn lấy ô “STT” đó làm mốc rồi bắt đầu đếm tới hàng chứa giá trị “Lê Đức Công” kia và tương tự bạn sẽ đếm được số thứ tự cột lấy mốc là ô “STT“.

Chúng ta sẽ có công thức như sau:

Nhìn vào ảnh trên chắc các bạn có thể hiểu cách sử dụng hàm INDEX rồi phải không nào? Bạn chỉ cần xác định được số thứ tự cột và hàng dựa vào phạm vi vùng dữ liệu. Trong các ví dụ phía dưới đây, bạn sẽ không cần phải đêm số thứ tự cột hay hàng nữa mà thay vào đó bạn cần kết hợp với 1 hàm nào đó có thể trả về kiểu dữ liệu dạng NUMBER là được.

Ví dụ hàm INDEX đơn giản dạng tham chiếu

Ví dụ trên chúng ta đã biết cách sử dụng hàm INDEX với 1 phạm vi dữ liệu, ở ví dụ này mình sẽ hướng dẫn các bạn cách sử dụng hàm INDEX dạng tham chiếu trên nhiều phạm vi vùng.

Giả sử chúng ta có 3 bảng thông tin vận động viên cần tra cứu tên vận động viên ngẫu nhiên 1 trong 3 bảng thông tin này.

Khi chúng ta tra cứu thông tin vận động viên trên 3 bảng này hay nói cách khác là tra cứu trên 3 vùng dữ liệu chính là dạng tham chiếu của hàm INDEX. Cú pháp hàm INDEX dạng tham chiếu này có tới 4 thành phần. Trong ví dụ trên, giả sử chúng ta tra cứu tới vận động viên “Trần Anh Thư” của bảng số 3, chúng ta sẽ có công thức như sau:

Công thức: =INDEX((B3:E5, G3:J6, B8:E11), 4, 3, 3)

• B3:E5: Vùng dữ liệu bảng 1
• G3:J6: Vùng dữ liệu bảng 2
• B8:E11: Vùng dữ liệu bảng 3
• 4: Số thứ tự hàng tính từ ô “STT”
• 3: Số thứ tự cột tính từ ô “STT”
• 3: Chỉ số vùng dữ liệu chọn, ở đây là bảng số 3

Lưu ý: Đóng mở ngoặc các vùng dữ liệu ngăn cách bởi dấu phẩy đối với các bảng như: (B3:E5, G3:J6, B8:E11)

Hàm INDEX kết hợp hàm MATCH

Một trong những hàm hay được kết hợp với hàm INDEX nhất đó chính là hàm MATCH, giống với hàm INDEX về chức năng dò tìm, khác về phạm vi chỉ áp dụng trên cột hoặc hàng. Khi hàm INDEX kết hợp với MATCH có thể giải quyết được bài toán tìm giá trị dựa trên điều kiện nào đó. Chúng ta sẽ cùng nhau tìm hiểu ví dụ dưới đây.

Giả sử ta có Bảng Số Lượng tháng 10/2023 và 1 Bảng Đơn Giá, yêu cầu điền Đơn Giá vào cột Đơn Giá dựa vào Bảng Đơn Giá.

Ở đây, bạn có thể dễ dàng thấy được Tên Đơn Vị chính là hàng đầu tiên xuất hiện trong Bảng Đơn Giá, còn Tên Phân Xưởng chính là cột dọc đầu tiên xuất hiện trong Bảng Đơn Giá.

Phân tích bài toán, chúng ta cần xác định được số thứ tự cột trong Bảng Đơn Giá đối với Tên Đơn Vị và số thứ tự hàng đối với Tên Phân Xưởng.

Xác định số thứ tự cột Tên Đơn Vị trong Bảng Đơn Giá bằng cách sử dụng hàm MATCH như sau: =MATCH(C4, J5:K5, 0)

Trong đó:

• C4: Tên Đơn Vị cần tra cứu ở trong Bảng Đơn Giá
• J5:K5: Phạm vi hàng Tên Đơn Vị trong Bảng Đơn Giá
• 0: Chỉ số tìm kiếm chính xác

Tương tự, ta sẽ có công thức xác định số thứ tự hàng Tên Phân Xưởng trong Bảng Đơn Giá như sau: =MATCH(D4,I6:I9,0)

Sau khi chúng ta đã xác định được chỉ số hàng và cột như trên, ta sẽ tiến hành ghép vào công thức hàm INDEX hoàn chỉnh như sau:

Công thức: =INDEX($J$8:$K$11,MATCH(D4,$I$8:$I$11,0),MATCH(C4,$J$7:$K$7,0))

Giải thích:

• $J$8:$K$11: Vùng dữ liệu cần tra cứu trong Bảng Đơn Giá
• MATCH(D4,$I$8:$I$11,0): Công thức hàm MATCH xác định ví trí hàng Tên Phân Xưởng trong Bảng Đơn Giá
• MATCH(C4,$J$7:$K$7,0): Công thức hàm MATCH xác định ví trí cột Tên Đơn Vị trong Bảng Đơn Giá

Và kết quả cuối cùng như sau:

Hàm INDEX kết hợp hàm IF

Hàm IF trong Excel là hàm cho phép so sánh các điều kiện logic với nhau, có 2 kết quả trả về đúng hoặc sai tùy vào kết quả so sánh điều kiện. Khi hàm INDEX kết hợp với hàm IF, hàm IF sẽ đóng vai trò xử lý điều kiện trả về kết quả là số hàng hoặc số cột. Để hiểu rõ hơn về cách sử dụng hàm INDEX kết hợp hàm IF, chúng ta sẽ cùng nhau tìm hiểu ví dụ dưới đây nhé!

Chúng ta vẫn lấy ví dụ hàm INDEX kết hợp hàm MATCH ở phía trên nhưng thay vì dùng hàm MATCH để lấy ra số thứ tự cột Tên Đơn Vị thì chúng ta sẽ dùng hàm IF để trả về số thứ tự cột Tên Đơn Vị. Chúng ta chỉ nên thay hàm IF vào công thức trả về số cột vì số cột Tên Đơn Vị trong Bảng Đơn Giá là 2 trong khi đó số hàng Tên Phân Xưởng trong Bảng Đơn Giá là 4.

Nếu dùng hàm IF lấy số cột Tên Đơn Vị chỉ cần 1 lệnh IF là đủ, còn dùng hàm IF lấy số hàng Tên Phân Xưởng sẽ là 3 câu lệnh IF do có 4 trường hợp.

Công thức: =INDEX($J$8:$K$11, MATCH(D4, $I$8:$I$11, 0), IF(C4 = “SX”, 1, 2))

Giải thích:

• $J$8:$K$11: Vùng dữ liệu cần tra cứu trong Bảng Đơn Giá
• MATCH(D4, $I$8:$I$11, 0): Công thức hàm MATCH xác định ví trí hàng Tên Phân Xưởng trong Bảng Đơn Giá
• IF(C4=”SX”, 1, 2): Nếu cột Tên Đơn Vị là “SX” thì sẽ trả về số thứ tự cột là 1 ngược lại nếu là “QL” sẽ là 2.

Hàm INDEX kết hợp hàm LEFT, RIGHT, MID

Ngoài các hàm kết hợp được với hàm INDEX trong Excel ở trên thì còn có thể kết hợp được với các hàm xử lý chuỗi như hàm LEFT, hàm RIGHT, hàm MID. Các hàm xử lý chuỗi này làm nhiệm vụ tách các ký tự như mã hàng, mã sản phẩm,… để làm giá trị dò tìm trong vùng dữ liệu khác. Thường các hàm xử lý chuỗi này vẫn sẽ phải kết hợp với một hàm khác nữa để có thể trả về giá trị dạng NUMBER.

Trong ví dụ này, mình sẽ lấy ví dụ hàm INDEX kết hợp hàm LEFT, RIGHT và hàm MATCH, hàm MATCH xuất hiện ở ví dụ này nhằm tra cứu dựa trên kết quả tách ra từ hàm LEFT, hàm RIGHT.

Giả sử chúng ta cũng có bảng như các ví dụ trên chỉ khác ở cột Tên Đơn Vị bao gồm Tên Đơn Vị và Tên Phân Xưởng. Yêu cầu là điền Đơn Giá từ Bảng Đơn Giá dựa vào Tên Đơn Vị và Tên Phân Xưởng.

Ở ví dụ này, chúng ta cần tách 2 ký tự đầu và 3 ký tự cuối của cột Tên Đơn Vị để tra cứu trong Bảng Đơn Giá. Bằng cách sử dụng hàm LEFT để tách 2 ký tự đầu và hàm RIGHT để tách 3 ký tự cuối. Chúng ta sẽ tách như sau:

• Tách 2 ký tự đầu: =LEFT(Tên Đơn Vị, 2)
• Tách 3 ký tự cuối: =RIGHT(Tên Đơn Vị, 3)

Sau khi tách xong thì cần phải ghép vào công thức hàm MATCH để lấy được số thứ tự dòng và cột trong Bảng Đơn Giá. Từ đây chúng ta mới tiếp tục ghép thành công thức hoàn chỉnh được. Chúng ta sẽ có công thức kết hợp hàm MATCH như sau:

• Tra cứu theo Cột: =MATCH(LEFT(Tên Đơn Vị, 2), Hàng đầu tiên của Bảng Đơn Giá, 0)
• Tra cứu theo Hàng: =MATCH(RIGHT(Tên Đơn Vị, 3), Cột đầu tiên của Bảng Đơn Giá, 0)

Và chúng ta ghép lại thành 1 công thức hàm IF kết hợp hàm LEFT, RIGHT, MID hoàn chỉnh như sau:

Công thức: =INDEX($I$8:$J$11, MATCH(LEFT(C4, 2), $I$7:$J$7, 0), MATCH(RIGHT(C4, 3), $H$8:$H$11, 0))

Giải thích:

• $J$8:$K$11: Vùng dữ liệu cần tra cứu trong Bảng Đơn Giá
• MATCH(LEFT(C4, 2), $I$7:$J$7, 0): Lấy số thứ tự cột Tên Đơn Vị trong Bảng Đơn Giá
• MATCH(RIGHT(C4, 3), $H$8:$H$11, 0): Lấy số thứ tự hàng Tên Phân Xưởng trong Bảng Đơn Giá

Như vậy, hàm INDEX trong Excel là một hàm cho phép chúng ta truy xuất dữ liệu trong 1 ô dựa vào chỉ số cột và hàng cho trước và nó có thể kết hợp với nhiều hàm khác nhau. Mình hi vọng các bạn có thể hiểu hơn về cách sử dụng hàm INDEX và có thể áp dụng hàm INDEX một cách phù hợp và hiệu quả nhất. Các bạn có thể đọc nhiều bài viết chất lượng hơn tại mục Excel cơ bản hoặc tham gia ngay khóa học Excel online để học nhiều hàm hơn nhé!