Cách sử dụng hàm index, hàm match và cách kết hợp của hai hàm

Nếu các bạn đã dùng hay đang dùng Excel thì chắc hẳn sẽ biết rằng trong Excel có rất nhiều hàm để mọi người có thể thao tác nhanh hơn. Hàm tìm kiếm thuộc một nhóm hàm cơ bản trong Exel. Có rất nhiều hàm dùng để tìm kiếm nhưng ở bài viết này chúng ta sẽ tìm hiểu về hàm Index và Match. 

Hàm Index

Hàm Index trong excel là một loại hàm có 2 dạng, gồm hàm Index dạng mảng và hàm Index dạng tham chiếu.

Hàm Index dạng mảng sẽ trả về giá trị của một ô dữ liệu có chỉ số hàng và số cột đang tìm kiếm. Công thức của hàm Index dạng mảng là:

INDEX(Array, Row_num, [Column_num])

Trong đó:

• Array: Mảng dữ liệu tham chiếu.
• Row_num: Hàng chứa giá trị cần lấy.
• Column_num: Cột chứa giá trị cần lấy.

Hàm Index dạng tham chiếu sẽ trả về giá trị của một ô có chỉ số hàng và cột đang tìm kiếm. Công thức của hàm Index dạng tham chiếu:

INDEX (Reference, Row_num, [Column_num], [Area_num]

Trong đó:

• Reference: Vùng tham chiếu chứa giá trị cần tìm.
• Row_num: Chỉ số hàng chứa giá trị cần tìm.
• Column_num: Chỉ số cột chứa giá trị cần tìm.
• Area_num: Số vùng trả về, để trống mặc định là 1.

Tùy theo mục đích sử dụng mà bạn sẽ dùng công thức hàm Index dạng mảng hay hàm Index dạng tham chiếu.

Hàm Match

Hàm Match trong excel là hàm trả về số thứ tự của giá trị cần tìm trong bảng. Công thức của hàm Match:

MATCH (Lookup_Value, Lookup_array, [Match_type])

Trong đó:

• Lookup_Value: Giá trị cần tìm kiếm.
• Lookup_array: Mảng chứa giá trị cần tìm.
• Match_type: Kiểu tìm kiếm. Có 3 kiểu tìm kiếm:

Tìm kiếm giá trị nhỏ hơn giá trị cần tìm kiếm khi match_type =1.

Tìm kiếm giá trị bằng giá trị cần tìm kiếm khi match_type =0.

Tìm kiếm giá trị lớn hơn giá trị cần tìm kiếm khi match_type = -1.

Kết hợp hàm Index và hàm Match

Hàm Index và hàm Match bạn có thể kết hợp lại với nhau để thực hiện yêu cầu dò tìm dữ liệu, nếu như những yêu cầu dò dữ liệu qua rắc rối những hàm khác không thể làm được. Công thức khi kết hợp 2 hàm:

INDEX(cột cần tra cứu giá trị, (MATCH (giá trị dùng để tra cứu, cột chứa giá trị, 0))

Những cách sửa lỗi khi dùng hàm Index và Match bị lỗi

Một trong những lỗi thường báo khi dùng hàm Index và Match là #N/A. Khi báo lỗi này thì trong hàm đó đã gặp 1 trong những vấn đề dưới đây:

• Không có dữ liệu trùng khớp

Khi sử dụng hàm nhưng không tìm thấy giá trị tra cứu ở trong mảng tra cứu thì sẽ báo lỗi. Lí do dẫn đến tình trạng này là do trong ô có chứa ký tự không phù hợp hoặc dấu cách đã bị ẩn hay trong ô có thể không được định dạng theo kiểu dữ liệu chính xác, ví dụ như ô có giá trị số, nhưng lại định dạng dưới dạng văn bản.

• Không nhất quán trong các giá trị kết hợp và thứ tự sắp xếp của dữ liệu

Khi bạn sử dụng hàm Match, cần có sự nhất quán giữa giá trị trong đối số match_type và thứ tự sắp xếp các giá trị trong phạm vi mà bạn muốn dò tìm.

Một số ví dụ về hàm Index, hàm Match và kết hợp giữa hai hàm 

DẠNG 1: DÙNG HÀM MATCH ĐỂ TÌM VỊ TRÍ THEO DÒNG, CỘT

Có bảng tính sau:

Với thông tin như bảng trên nếu bạn muốn biết thông tin của người có tên là “Trần Thiên Thu” thì bạn sẽ thao tác như thế nào? Chúng ta sẽ giải quyết vấn đề này bằng cách là phải xác định được vị trí của người đó trong bảng tính.

Để có thể biết được vị trí của “Trần Thiên Thu” thì chúng ta dùng hàm Match. Công thức sẽ là MATCH(“Lê Thị Thủy”, cột Họ tên, số 0)

Hàm Match đã cho chúng ta kết quả là 4 tương đương với dòng thứ 4 trên bảng tính.

DẠNG 2: TÌM EMAIL THEO TÊN BẰNG SỰ KẾT HỢP GIỮA HÀM INDEX VÀ MATCH

Khi đọc dạng bài tập thì chắc chắn bạn cũng đã biết được chúng ta sẽ sử dụng cột HỌ VÀ TÊN và cột Email. Công thức sẽ là:

INDEX(Cột Email,MATCH(Tên,Cột Họ và tên,0))

Ở công thức này:

• Hàm Index sẽ có vai trò chỉ ra cột chứa giá trị cần tìm là cột Email, nằm trong vùng cột Email.
• Hàm Match sẽ chỉ ra dòng chứa giá trị cần tìm.

DẠNG 3: TÌM DỮ LIỆU TỪ PHẢI QUA TRÁI VỚI SỰ KẾT HỢP CỦA HÀM INDEX VÀ HÀM MATCH

Bình thường thì chúng ta sẽ tìm kiếm dữ liệu từ trái sáng phải bằng các nhóm hàm thông thường. Với sự kết hợp của hàm Index và hàm Match thì chúng ta hoàn toàn có thể tìm dữ liệu từ phải qua trái.

Dựa theo bảng tính chúng ta sẽ thử đi tìm Họ và tên thông qua Email. Theo công thức:

INDEX(cột Họ và tên,MATCH(Email,vùng chứa Email,0))

Trong công thức này sẽ được thực hiện theo thứ tự như sau:

• Hàm Match để tìm vị trí dòng của Email trong cột Email.
• Hàm INDEX giúp chỉ ra vị trí cột chứa kết quả cần tìm, là cột Họ tên, nằm trong vùng của cột Họ và tên.

DẠNG 4: XÁC ĐỊNH DỮ LIỆU CÓ NHIỀU ĐIỀU KIỆN BẰNG CÁCH KẾT HỢP HÀM INDEX VÀ HÀM MATCH

Có bảng dữ liệu sau:

Yêu cầu: Xác định đơn giá của LOẠI PHÒNG A, ở DNA.

Với một yêu cầu phải tìm giá nhưng phải thỏa mãn 2 điều kiện là LOẠI PHÒNG A và ở DNA. Chúng ta sẽ áp dụng công thức với sự kết hợp của hàm Index và hàm Match là:

Bước 1: Xác định vị trí MÃ KHU VỰC trên cột thứ mấy 

Chúng ta xác định vị trí của MÃ KHU VỰC bằng hàm Match. Theo công thức:

MATCH(MÃ KHU VỰC,DÒNG CÓ CHỨA MÃ KHU VỰC,0)

Bước 2: Xác định vị trí LOẠI PHÒNG nằm ở dòng thứ mấy

Chúng ta cũng xác định vị trí của LOẠI PHÒNG bằng hàm Match. Theo công thức:

MATCH(LOẠI PHÒNG,Cột LOẠI PHÒNG,0)

Bước 3: Xác định kết quả đơn giá cần tìm 

Xác định đơn giá cần tìm thì chúng ta sẽ dùng hàm Index. Theo công thức:

INDEX(BẢNG ĐƠN GIÁ CÁC LOẠI PHÒNG,vị trí dòng của LOẠI PHÒNG,vị trí cột của MÃ KHU VỰC)

Bài viết này hướng dẫn cách sử dụng hàm Index, hàm Match và sự kết hợp của cả 2 hàm. Mong rằng sau khi đọc qua bài viết này bạn đã biết thêm cách sử dụng thêm loại hàm trong Excel.

Tham khảo các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel

• Thay đổi nguồn và cập nhật Pivot Table
• PivotTable là gì? 3 điểm cần lưu ý khi tạo PivotTable
  
• 40 phím tắt Excel hữu ích nhất cho dân văn phòng

Team Content Uniace