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 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 đó:.
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 đó:.
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 đó:.
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:.
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.
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:.
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:.
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.
Team Content Uniace.