Mục lục
Nếu bạn thường xuyên sử dụng hàm VLOOKUP và HLOOKUP trong Excel để dò tìm dữ liệu, có lẽ bạn đã trải qua không ít rắc rối vì những nhược điểm khó chịu của các hàm tham chiếu này. Tin mừng cho bạn là trong phiên bản cập nhật Office 365 Insiders, Excel đã cho ra mắt hàm XLOOKUP để bù đắp cho sự bất tiện khi dùng hàm VLOOKUP và HLOOKUP.
Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ
Trong bài viết ngày hôm nay, bạn hãy cùng Gitiho đi tìm hiểu cách sử dụng hàm XLOOKUP thay thế các hàm LOOKUP cũ qua 11 ví dụ cụ thể tương ứng với 11 cách ứng dụng nhé.
Xem thêm: Giới thiệu các hàm trong Excel và các ví dụ minh họa dễ hiểu
Nếu bạn chưa biết thì tên gọi của hàm tìm kiếm trong Excel mới này được đặt dựa vào khả năng tìm kiếm đa chiều của nó - X - hay còn được miêu tả là khả năng tham chiếu dữ liệu theo cả chiều ngang và chiều dọc. Với khả năng đặc biệt này, hàm XLOOKUP hoàn toàn có thể thay thế cả hàm VLOOKUP và HLOOKUP.
Cho đến hiện tại, hàm XLOOKUP chỉ được thiết lập trên phiên bản Excel 365. Do đó, nếu bạn chưa cài đặt Office 365 cho hệ thống của mình thì hãy tham khảo bài viết dưới đây nhé.
Xem thêm: Hướng dẫn các ưu điểm của Microsoft Office 365 và cách cài đặt
Hàm XLOOKUP trong Excel có cú pháp như sau:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],[match_mode], [search_mode])
Trong đó:
Các tham số cơ bản và bắt buộc của hàm XLOOKUP là:
Các tham số nâng cao và tùy chọn của hàm XLOOKUP là:
Nếu bạn đang băn khoăn tại sao Excel cập nhật hàm XLOOKUP mới trong khi không hề có khiếu nại từ người dùng về hàm VLOOKUP và HLOOKUP, hãy nhìn vào nhược điểm của các hàm LOOKUP cũ mà bạn có thể khắc phục bằng hàm tìm kiếm trong Excel mới mang tên XLOOKUP.
1. Hàm VLOOKUP và HLOOKUP mặc định tìm kiếm xấp xỉ
Thay vì tìm kiếm đối tượng chính xác, hàm VLOOKUP và HLOOKUP mặc định chức năng tìm kiếm xấp xỉ. Đó chính là lý do bạn nhất định phải thiết lập tham số thứ tư của hàm VLOOKUP và HLOOKUP là False nếu không muốn Excel trả về kết quả sai.
2. Hàm VLOOKUP và HLOOKUP dừng hoạt động sau khi thêm bớt cột/hàng
Tham số thứ ba trong công thức hàm VLOOKUP và hàm HLOOKUP thể hiện số thứ tự của cột/hàng trả về giá trị. Vì tham số này liên kết đến một vị trí cố định trên bảng tính Excel, bạn sẽ không thể sử dụng công thức hàm sau khi thay đổi cấu trúc cột/hàng. Thay vào đó, bạn cần viết lại một hàm LOOKUP mới.
3. Hàm VLOOKUP không thể tìm kiếm bên trái
Một trong những nhược điểm lớn nhất của hàm VLOOKUP chính là nó không thể tham chiếu giá trị cần tìm từ phải qua trái. Nghĩa là hàm của bạn sẽ không hoạt động với toàn bộ vùng dữ liệu nằm bên trái cột chứa giá trị cần khớp.
4. Hàm VLOOKUP và HLOOKUP không tìm kiếm theo trình tự ngược
Nếu bạn không tự sắp xếp dữ liệu của mình theo trình tự ngược lại từ thứ tự lớn dần sang thứ tự nhỏ dần, hàm VLOOKUP và HLOOKUP sẽ không thể tự thay đổi kiểu tìm kiếm.
5. Hàm VLOOKUP và HLOOKUP không thể tìm giá trị lớn hơn kế tiếp
Khi bạn tìm kiếm giá trị xấp xỉ với hàm LOOKUP, bạn bắt buộc phải sắp xếp dữ liệu theo trình tự yêu cầu. Ngay cả khi đã thực hiện bước này, bạn cũng chỉ có thể nhận được giá trị nhỏ hơn gần nhất với giá trị cần tìm.
6. Hàm VLOOKUP và HLOOKUP tham chiếu tới các ô không cần thiết
Tham số thứ hai của hàm VLOOKUP và HLOOKUP - table_array - yêu cầu bạn điền toàn bộ phạm vi bảng dữ liệu, nghĩa là trong đó chắc chắn có những ô hoàn toàn có thể bỏ qua trong quá trình tìm kiếm. Việc kéo dài thao tác tìm kiếm sẽ làm chậm bảng tính của bạn.
Ngược lại với các hàm LOOKUP cũ, hàm XLOOKUP được thiết kế với sự linh hoạt và mạnh mẽ hơn rất nhiều. Nhờ đó, nó có thể vượt qua mọi nhược điểm của những người tiền nhiệm.
Một phương pháp sử dụng hàm tìm kiếm trong Excel hoàn hảo để thay thế hàm VLOOKUP và HLOOKUP chính là hàm INDEX MATCH. Không chỉ giải quyết nhanh gọn các vấn đề của hàm LOOKUP, hàm INDEX MATCH còn có rất nhiều ứng dụng khác. Bạn có thể tham khảo bài viết dưới đây để tìm hiểu về giải pháp hữu hiệu này.
Xem thêm: Cách dùng hàm MATCH - trợ thủ đắc lực cho hàm tìm kiếm trong Excel
Trong phần này, chúng ta sẽ khám phá sức mạnh của hàm tìm kiếm trong Excel mang tên XLOOKUP. Qua các ví dụ cụ thể, chúng ta có thể hiểu hơn về sự khác biệt của hàm so với các hàm LOOKUP cũ.
Đầu tiên, chúng ta sẽ áp dụng hàm XLOOKUP vào một thao tác tìm kiếm dữ liệu đơn giản với một bảng điểm như sau:
Yêu cầu: Tìm điểm Toán của học sinh tên Đạt và điền vào ô G3.
Cách làm:
Chúng ta sẽ sử dụng hàm XLOOKUP với công thức hàm tìm kiếm trong Excel như sau:
=XLOOKUP(G2,A2:A14,B2:B14)
Trong đó:
Như vậy, công thức hàm XLOOKUP cơ bản phía trên trả về kết quả là 4.7, tương ứng với giá trị tại ô B5 trong bảng điểm. Ví dụ này không có gì khó hiểu đúng không nào?
Qua ví dụ này, bạn có thể thấy một điểm khác biệt giữa hàm XLOOKUP và hàm VLOOKUP trong phương thức hoạt động với các vùng dữ liệu. Nếu sử dụng hàm VLOOKUP, bạn sẽ phải chọn toàn bộ bảng điểm (A1:D14). Trong khi đó, hàm XLOOKUP chỉ cần bạn trực tiếp chọn ra cột chứa dữ liệu cần khớp (cột A) và cột chứa giá trị tương ứng cần trả về (cột B).
Một lợi thế rõ ràng của việc có 2 tham số lookup_array và return_array riêng biệt chính là hàm có thể tìm kiếm bên trái. Giả sử chúng ta đổi cột Học sinh sang bên phải cột Điểm Toán. Bạn sẽ thấy rằng công thức hàm tìm kiếm trong Excel vẫn hoạt động ngon lành mà không gặp phải bất kỳ lỗi gì.
=XLOOKUP(G2,D2:D14,A2:A14)
Ngoài ra, hàm XLOOKUP còn khắc phục thêm một nhược điểm nữa của hàm VLOOKUP và HLOOKUP. Hãy nhìn vào hình dưới đây với công thức:
=XLOOKUP(F2,C2:C14,A2:A14)
Hàm XLOOKUP hoàn toàn không gặp khó khăn khi tự điều chỉnh các tham số dựa vào thay đổi thêm bớt hàng trong bảng dữ liệu đang tham chiếu. Qua ví dụ này, ta có thể thấy sự linh hoạt đáng kể với công thức hàm tìm kiếm trong Excel mới này.
Yêu cầu: Tìm các đầu điểm của học sinh Đạt và điền vào các ô G3:I3.
Cách làm:
Công thức hàm tìm kiếm trong Excel được sử dụng tại ô G3 dưới đây sẽ trả về cả 3 đầu điểm của học sinh Đạt vào lần lượt các ô G3, H3 và I3:
=XLOOKUP(F3,A2:A14,B2:D14)
Trong công thức hàm XLOOKUP trên, tham số return_array chứa nhiều hơn một cột. Vì vậy, khi tìm thấy giá trị cần khớp tại cột A, Excel sẽ trả về toàn bộ hàng tương ứng với giá trị đó.
Một điểm bạn cần lưu ý khi áp dụng hàm XLOOKUP trong trường hợp này chính là việc tất cả các ô hiển thị kết quả đều được áp dụng hàm, cho dù bạn không hề nhập hàm trực tiếp vào các ô này. Giả sử với ô H3 và ô I3 trong ví dụ: Khi bạn nhấn vào một trong hai ô sẽ thấy thanh công thức mờ đì, nghĩa là bạn không thể thay đổi công thức này.
Tất nhiên khi bạn xóa công thức đã nhập tại ô G2, toàn bộ kết quả hiển thị trong phạm vi ô này sẽ biến mất. Thao tác tiện lợi này chính là một bước tiến so việc bạn phải tạo từng công thức riêng khi sử dụng hàm VLOOKUP.
Yêu cầu: Tìm điểm Toán của học sinh tên Đạt và điền vào ô G3.
Cách làm:
Thay vì sử dụng công thức hàm XLOOKUP thông thường, chúng ta sẽ xây dựng công thức hàm tìm kiếm trong Excel theo 2 chiều, trong đó chúng ta vừa tìm giá trị tên học sinh ở cột A và tên môn học ở dòng 1. Việc sử dụng phương pháp tìm kiếm 2 chiều sẽ cho ta kết quả không phụ thuộc vào tên học sinh và tên môn học. Điều này nghĩa là chúng ta hoàn toàn có thể tùy ý thay đổi tên môn học hoặc tên học sinh mà hàm vẫn trả về kết quả đúng.
Công thức hàm tìm kiếm trong Excel 2 chiều với hàm XLOOKUP như sau:
=XLOOKUP(F3,B1:D1,XLOOKUP(G2,A2:A14,B2:D14))
Trong công thức trên, chúng ta sử dụng 2 hàm XLOOKUP. Hàm bên trong sẽ trả về kết quả là mảng {4.7,7.3,8.3} tương đương với tất cả các đầu điểm của học sinh Đạt. Kết quả này được sử dụng làm tham số return_array cho hàm XLOOKUP bên ngoài. Vì chúng ta đang tìm kiếm giá trị tương ứng với giá trị Điểm Toán (ô F3) nên hàm này lọc ra trong mảng return_array một giá trị để trả về ô G3.
Kết quả, chúng ta thu được con số 4.7 như trong hình.
Hàm XLOOKUP đã được cập nhật tính năng xử lý lỗi với tham số thứ tư [if_not_found], cho phép bạn nhập một giá trị yêu cầu Excel trả về trong trường hợp hàm không tìm thấy giá trị cần khớp. Chúng ta sẽ thử tính năng này nhé.
Yêu cầu: Tìm điểm Toán của học sinh tên Mai và điền vào ô G3.
Cách làm:
Tại ô G3, chúng ta sẽ điền công thức hàm XLOOKUP như sau:
=XLOOKUP(G2,A2:A14,B2:B14,"Không tồn tại")
Như bạn thấy, chúng ta có thể xác định giá trị hàm trả về trong công thức hàm, thay vì lỗi #N/A trong trường hợp tương tự với các hàm VLOOKUP và HLOOKUP.
Lợi ích của tham số [if_not_found] trong hàm XLOOKUP đặc biệt hữu ích khi bạn sử dụng hàm lồng nhau như trong ví dụ 3. Chúng ta hãy cùng tìm hiểu cách sử dụng hàm tìm kiếm trong Excel với nhiều hơn một bảng dữ liệu để thấy rõ điều này nhé.
Yêu cầu: Tìm điểm Toán của học sinh Uyên và điền vào ô B11.
Cách làm:
Để tham chiếu trong 2 bảng khác nhau, chúng ta sẽ cần đến công thức hàm XLOOKUP lồng nhau như dưới đây:
=XLOOKUP(B10,A2:A8,B2:B8,XLOOKUP(B10,F2:F8,G2:G8))
Trong công thức hàm tìm kiếm trong Excel trên, chúng ta đã sử dụng một công thức XLOOKUP cho tham số [if_not_found]. Điều này nghĩa là nếu hàm không tìm thấy giá trị cần khớp ở bảng 1, hàm sẽ tự động chuyển sang tìm kiếm với thao tác tương tự ở bảng 2.
Với hàm XLOOKUP, chúng ta không còn phải tìm kiếm các công thức phức tạp để tìm giá trị cuối cùng trong bảng dữ liệu khớp với giá trị cần tìm nữa.
Yêu cầu: Tìm nhân viên mới nhất của công ty, ngày gia nhập tương ứng và điền thông tin vào bảng.
Cách làm:
Để tìm các nhân viên mới nhất của bộ phận kinh doanh, chúng ta sẽ điền công thức sau vào ô F3:
=XLOOKUP(F2,$B$2:$B$14,$A$2:$A$14,,,-1)
Để tìm ngày gia nhập của nhân viên mới nhất trong bộ phận kinh doanh, chúng ta sẽ sử dụng công thức dưới đây cho ô F4:
=XLOOKUP(F2,$B$2:$B$14,$C$2:$C$14,,,-1)
Vì hàm XLOOKUP được thiết kế với tính năng xác định chiều tìm kiếm (từ đầu đến cuối hoặc ngược từ cuối lên đầu), do đó chúng ta không hề gặp khó khăn khi tìm kiếm dữ liệu cuối cùng khớp với giá trị cần tham chiếu. Với dữ liệu theo chiều dọc, hàm VLOOKUP và HLOOKUP sẽ luôn luôn tìm kiếm từ đầu xuống cuối. Rõ ràng chúng ta có thể thấy được sự nổi trội của hàm XLOOKUP trong trường hợp này.
Xem thêm: Hướng dẫn cách dùng hàm VLOOKUP trong Excel qua các ví dụ từ cơ bản đến nâng cao
Một điểm cải tiến khác trong cấu trúc hàm XLOOKUP là số kiểu khớp giá trị cần tìm. Trong khi hàm VLOOKUP và HLOOKUP chỉ có 2 kiểu, hàm MATCH có 3 kiểu, thì hàm XLOOKUP lại sở hữu đến 4 kiểu khớp như mình đã đề cập trong phần cú pháp của hàm.
4 loại match_mode của hàm tìm kiếm trong Excel XLOOKUP bao gồm:
Yêu cầu: Tìm điểm Toán của học sinh Uyên và điền vào ô B7.
Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản
0 Bình luận