Cách sử dụng INDEX và MATCH trong Excel

Trong Excel, INDEX và MATCH là hai hàm tra cứu dữ liệu rất mạnh. Khi kết hợp với nhau, chúng có thể thực hiện nhiều công việc mà trước đây người dùng thường sử dụng VLOOKUP hoặc HLOOKUP.

Đặc biệt, INDEX + MATCH hữu ích khi bảng dữ liệu có nhiều cột, cần tra cứu theo cả hàng và cột, hoặc cột chứa giá trị cần tìm không nằm bên trái cột kết quả.

INDEX và MATCH là gì?

Hàm INDEX

Hàm INDEX dùng để trả về giá trị nằm tại một vị trí xác định trong một vùng dữ liệu.

Cú pháp:


=INDEX(array, row_num, [column_num])

Trong đó:

array: vùng dữ liệu cần lấy kết quả.
row_num: số thứ tự của hàng cần lấy.
column_num: số thứ tự của cột cần lấy.

Ví dụ, có bảng:

A	B	C
Mã SP	Sản phẩm	Giá
SP01	Laptop	15000000
SP02	Điện thoại	10000000
SP03	Máy tính bảng	7000000

Công thức:


=INDEX(C2:C4,2)

Kết quả là:

10000000

Bởi vì giá trị thứ hai trong vùng C2:C4 là 10.000.000.

Hàm MATCH

Hàm MATCH dùng để tìm vị trí của một giá trị trong một vùng dữ liệu.

Cú pháp:


=MATCH(lookup_value, lookup_array, [match_type])

Trong đó:

lookup_value: giá trị cần tìm.
lookup_array: vùng chứa giá trị cần tìm.
match_type: kiểu tìm kiếm.

Thông thường, khi cần tìm chính xác, sử dụng:

Ví dụ:


=MATCH("SP02",A2:A4,0)

Kết quả:

Điều này có nghĩa SP02 nằm ở vị trí thứ 2 trong vùng A2:A4.

Kết hợp INDEX và MATCH

Điểm mạnh nhất xuất hiện khi sử dụng hai hàm cùng nhau.

Giả sử bảng dữ liệu:

Mã SP	Sản phẩm	Giá
SP01	Laptop	15000000
SP02	Điện thoại	10000000
SP03	Máy tính bảng	7000000
SP04	Màn hình	5000000

Ô E2 chứa mã sản phẩm cần tìm:


SP03

Muốn tìm giá của SP03, có thể sử dụng:


=INDEX(C2:C5,MATCH(E2,A2:A5,0))

Excel sẽ thực hiện theo hai bước.

MATCH tìm vị trí của SP03:


=MATCH(E2,A2:A5,0)

→ Kết quả là 3.

Sau đó INDEX lấy giá trị thứ 3 trong vùng C2:C5:


=INDEX(C2:C5,3)

→ Kết quả:

Như vậy:


=INDEX(C2:C5,MATCH(E2,A2:A5,0))

chính là công thức hoàn chỉnh để tra cứu.

INDEX MATCH so với VLOOKUP

Một trong những lý do INDEX + MATCH được sử dụng nhiều là tính linh hoạt cao hơn VLOOKUP.

Ví dụ:


=VLOOKUP(E2,A2:C5,3,FALSE)

cũng có thể tìm giá của SP03.

Tuy nhiên, INDEX + MATCH có một số ưu điểm đáng chú ý.

Có thể tra cứu sang bên trái

VLOOKUP thông thường yêu cầu cột chứa giá trị tìm kiếm nằm ở bên trái cột kết quả.

Trong khi đó, INDEX + MATCH không có hạn chế này.

Ví dụ bảng:

Sản phẩm	Mã SP	Giá
Laptop	SP01	15000000
Điện thoại	SP02	10000000
Máy tính bảng	SP03	7000000

Muốn nhập mã SP ở E2 và tìm tên sản phẩm, có thể dùng:


=INDEX(A2:A4,MATCH(E2,B2:B4,0))

Nếu E2 = SP03, kết quả sẽ là:


Máy tính bảng

Đây là trường hợp VLOOKUP truyền thống không xử lý trực tiếp được nếu không sắp xếp lại bảng.

Tra cứu theo hai điều kiện

INDEX + MATCH còn có thể được sử dụng để tra cứu dựa trên nhiều điều kiện.

Ví dụ:

Nhân viên	Sản phẩm	Doanh số
An	Laptop	20.000.000
Bình	Điện thoại	15.000.000
An	Điện thoại	18.000.000
Bình	Laptop	25.000.000

Giả sử:

E2: Nhân viên cần tìm
F2: Sản phẩm cần tìm

Có thể sử dụng:


=INDEX(C2:C5,MATCH(1,(A2:A5=E2)*(B2:B5=F2),0))

Công thức này tìm dòng thỏa mãn đồng thời hai điều kiện.

Trong các phiên bản Excel mới hỗ trợ Dynamic Arrays, công thức có thể hoạt động trực tiếp. Với một số phiên bản Excel cũ, có thể cần xác nhận công thức bằng Ctrl + Shift + Enter.

INDEX + MATCH theo cả hàng và cột

Một ứng dụng rất hữu ích khác là tìm dữ liệu theo cả hàng và cột.

Ví dụ:

	Tháng 1	Tháng 2	Tháng 3
Laptop	20	25	30
Điện thoại	15	18	22
Máy tính bảng	10	12	15

Nếu muốn tìm doanh số của Điện thoại trong Tháng 2, có thể sử dụng:


=INDEX(B2:D4,MATCH("Điện thoại",A2:A4,0),MATCH("Tháng 2",B1:D1,0))

Công thức hoạt động theo nguyên tắc:


MATCH thứ nhất → xác định dòng
MATCH thứ hai → xác định cột
INDEX → trả về giá trị tại giao điểm

Kết quả là:

Đây là một trong những ứng dụng mạnh nhất của INDEX + MATCH trong Excel.

Xử lý lỗi khi không tìm thấy dữ liệu

Nếu giá trị cần tìm không tồn tại, công thức INDEX + MATCH có thể trả về lỗi #N/A.

Để hiển thị thông báo dễ hiểu hơn, có thể kết hợp với IFERROR:


=IFERROR(INDEX(C2:C5,MATCH(E2,A2:A5,0)),"Không tìm thấy")

Khi tìm thấy dữ liệu, Excel trả về kết quả bình thường.

Nếu không tìm thấy, Excel sẽ hiển thị:


Không tìm thấy

thay vì:


#N/A

INDEX + MATCH hay XLOOKUP?

Với những phiên bản Excel mới, XLOOKUP thường là lựa chọn đơn giản và dễ đọc hơn:


=XLOOKUP(E2,A2:A5,C2:C5)

Trong khi INDEX + MATCH sử dụng:


=INDEX(C2:C5,MATCH(E2,A2:A5,0))

Tuy nhiên, INDEX + MATCH vẫn rất đáng học vì:

Có tính linh hoạt cao.
Hoạt động tốt với nhiều cấu trúc bảng.
Có thể xử lý các bài toán tra cứu phức tạp.
Phù hợp với nhiều file Excel cũ.
Giúp người dùng hiểu rõ hơn về cách Excel xác định vị trí và trả về dữ liệu.

Một số công thức INDEX + MATCH quan trọng

Tra cứu một giá trị:


=INDEX(C2:C10,MATCH(E2,A2:A10,0))

Tra cứu sang bên trái:


=INDEX(A2:A10,MATCH(E2,B2:B10,0))

Tra cứu với hai điều kiện:


=INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0))

Tra cứu hai chiều:


=INDEX(B2:D10,MATCH(G2,A2:A10,0),MATCH(H2,B1:D1,0))

Kết hợp IFERROR để xử lý lỗi:


=IFERROR(INDEX(C2:C10,MATCH(E2,A2:A10,0)),"Không tìm thấy")

Lời khuyên

Nếu bạn mới học Excel, nên nắm chắc MATCH → INDEX → INDEX + MATCH → INDEX + MATCH nhiều điều kiện → INDEX + MATCH hai chiều. Sau đó học XLOOKUP để biết khi nào nên dùng công thức hiện đại, ngắn gọn hơn.

Nếu bạn đang xây dựng nhóm bài mẹo Excel/tin học văn phòng cho website, chủ đề này rất phù hợp để đặt cạnh các bài VLOOKUP, XLOOKUP, IF, COUNTIF, SUMIF và FILTER, vì người đọc thường tìm các hàm này theo cùng một nhu cầu tra cứu và xử lý dữ liệu.

Mẫu Đơn.

TECHHOME.INFO

Tìm kiếm Blog này