Có vô số lý do tại sao bạn nên biết sự khác biệt giữa VLOOKUP và XLOOKUP trong Google Sheets. Hãy cùng khám phá những điểm khác biệt quan trọng nhất dưới đây:
Chọn hàm đúng
Hiểu sự khác biệt giữa VLOOKUP và XLOOKUP sẽ giúp bạn chọn được hàm phù hợp nhất với nhu cầu cụ thể của bạn.
Khắc phục vấn đề công thức
Hiểu biết về sự khác biệt giữa VLOOKUP và XLOOKUP có thể giúp bạn khắc phục các vấn đề liên quan đến công thức của mình.
Đưa ra quyết định có căn cứ
Bằng việc hiểu rõ ưu điểm và nhược điểm của mỗi hàm, bạn có thể đưa ra những quyết định có căn cứ hơn về việc sử dụng hàm nào trong bảng tính của mình.
Trước khi chúng ta tiếp tục, cần lưu ý rằng sự khác biệt giữa VLOOKUP và XLOOKUP có thể thay đổi trong Microsoft Excel và các ứng dụng bảng tính khác. Trong bài hướng dẫn này, chúng ta sẽ tập trung chỉ vào cách chúng khác nhau trong Google Sheets.
Mục đích của các hàm tìm kiếm trong bảng tính
Mục đích của tất cả các hàm tìm kiếm là tìm kiếm các mục trong một phạm vi. VLOOKUP chỉ dành riêng cho việc tìm kiếm theo chiều dọc, trong khi XLOOKUP có thể xử lý cả tìm kiếm theo chiều dọc và chiều ngang. Trong bài viết này, chúng ta sẽ tập trung vào sự khác biệt giữa chúng khi được sử dụng cho việc tìm kiếm theo chiều dọc.
VLOOKUP và XLOOKUP: Quan sát sự khác biệt về đối số
Hàm VLOOKUP đã có sẵn trong Google Sheets kể từ khi ra mắt ban đầu vào năm 2006, trong khi XLOOKUP là một thêm mới hơn được giới thiệu vào năm 2022. Mặc dù cả hai hàm đều có thể thực hiện tìm kiếm, XLOOKUP mạnh mẽ hơn và cung cấp cú pháp linh hoạt hơn.
Dưới đây là cú pháp của cả hai hàm:
VLOOKUP Syntax: VLOOKUP(search_key, range, index, [is_sorted])
XLOOKUP Syntax: XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
Đối số chung duy nhất giữa hai hàm này là search_key. Các đối số range và index của VLOOKUP đã được thay thế bằng lookup_range và result_range trong XLOOKUP. Ngoài ra, is_sorted đã được thay thế bằng match_mode và search_mode, và XLOOKUP có một đối số missing_value bổ sung.
Hãy khám phá cách những thay đổi này ảnh hưởng đến quá trình tìm kiếm khi làm việc với dữ liệu trong Google Sheets.
Lợi ích của việc thay thế Range và Index trong VLOOKUP bằng Lookup_range và Result_range trong XLOOKUP
Đây là một sự khác biệt quan trọng giữa VLOOKUP và XLOOKUP, một sự khác biệt mà bạn cần hiểu để hiểu rõ hơn về các hàm này.
Trong bảng trên, giả sử chúng ta muốn tìm kiếm dân số của Tuvalu, một trong những quốc gia ít dân nhất trên thế giới. Chúng ta có thể sử dụng công thức XLOOKUP sau:
=VLOOKUP(“Tuvalu”,B3:D7,2,FALSE)
Hàm VLOOKUP tìm kiếm giá trị “Tuvalu” trong cột đầu tiên của phạm vi B3:D7 và trả về dân số từ cột thứ hai (index 2) của hàng phù hợp. Vì cột đầu tiên không được sắp xếp, chúng ta chỉ định FALSE.
Bây giờ, hãy xem cách XLOOKUP xử lý cùng kịch bản này:
=XLOOKUP(“Tuvalu”,B3:B7,C3:C7)
Ở đây, chúng ta thay thế range bằng lookup_range và index bằng result_range. Vì XLOOKUP mặc định không được sắp xếp và thực hiện một sự kết hợp chính xác, chúng ta không cần chỉ định FALSE như chúng ta đã làm trong VLOOKUP.
XLOOKUP có những ưu điểm gì so với VLOOKUP ở đây?
XLOOKUP cho phép bạn sử dụng bất kỳ cột nào là mảng tìm kiếm, không chỉ cột bên trái nhất. Linh hoạt này giúp việc sử dụng XLOOKUP dễ dàng hơn trong các tình huống khác nhau.
Ví dụ, hãy giả sử bạn có một bảng dữ liệu với tên các quốc gia ở cột B và dân số của chúng ở cột C (tham khảo hình ảnh ở trên). Với XLOOKUP, bạn có thể tìm tên quốc gia cho một dân số cụ thể bằng công thức sau:
=XLOOKUP(12829,C3:C7,B3:B7)
Mặt khác, VLOOKUP chỉ cho phép bạn sử dụng cột bên trái nhất làm phạm vi tìm kiếm. Vì vậy, để tìm tên quốc gia cho một dân số cụ thể bằng VLOOKUP, bạn sẽ cần sử dụng công thức sau:
=VLOOKUP(12829,{C3:C7,B3:B7},2,FALSE)
Trong công thức này, chúng ta sử dụng dấu ngoặc nhọn để kết hợp các cột trong phạm vi. Vì VLOOKUP chỉ có thể tìm kiếm xuống cột bên trái nhất, chúng ta cần chỉ định nơi nó nên tìm kiếm giá trị mà chúng ta đang tìm kiếm.
Ngoài khả năng không tìm kiếm các giá trị bên trái, VLOOKUP còn có một nhược điểm khác. Khi bạn chèn các cột vào phạm vi, có khả năng gãy công thức vì số cột chỉ số vẫn cố định và không điều chỉnh tự động. XLOOKUP không có vấn đề này do đối số result_range của nó, cho phép công thức thích ứng với thay đổi vị trí cột.
Xử lý lỗi N/A trong VLOOKUP và XLOOKUP
Một trong những ưu điểm của XLOOKUP là tích hợp sẵn chức năng IFNA, đây là sự khác biệt thứ hai giữa VLOOKUP và XLOOKUP.
Hàm VLOOKUP trả về lỗi #N/A khi khóa tìm kiếm không được tìm thấy trong cột đầu tiên của phạm vi. Thông thường, chúng ta sử dụng một bọc IFNA để loại bỏ lỗi này. Tuy nhiên, XLOOKUP đã tích hợp sẵn chức năng IFNA.
Vui lòng tham khảo hình ảnh ở dưới. Cả trong VLOOKUP và XLOOKUP, khóa tìm kiếm “Australia” không được tìm thấy trong cột đầu tiên của phạm vi hoặc phạm vi tìm kiếm, tương ứng. Do đó, cả hai hàm đều trả về giá trị lỗi #N/A. Tôi đã thay thế giá trị lỗi này bằng văn bản tùy chỉnh “Không tìm thấy!”.
VLOOKUP so với XLOOKUP trong một phạm vi không được sắp xếp: Những khác biệt và điểm tương đồng
Theo kiến thức của tôi, sự tiến bộ thực sự trong hàm XLOOKUP nằm ở việc thay thế đối số is_sorted bằng đối số match_mode và search_mode. Hãy đơn giản hóa chủ đề phức tạp này cho bạn.
Các chế độ khớp XLOOKUP và chế độ khớp mặc định của VLOOKUP
Hàm VLOOKUP trong Google Sheets không cung cấp khớp gần đúng trong một phạm vi không được sắp xếp.
Trong các công thức VLOOKUP được đề cập ở trên, chúng ta đặt đối số is_sorted thành FALSE để thực hiện khớp chính xác trong một phạm vi không được sắp xếp.
Với XLOOKUP, chúng ta có thể chỉ định giá trị 0 hoặc bỏ qua bất kỳ giá trị nào trong match_mode để thực hiện khớp chính xác. Đó là điều chúng ta đã làm trong các ví dụ ở trên.
Một điểm tương đồng khác giữa VLOOKUP và XLOOKUP trong một phạm vi không được sắp xếp là khớp ký tự đại diện. Trong XLOOKUP, chúng ta có thể chỉ định match_mode #2 để thực hiện khớp ký tự đại diện. VLOOKUP hỗ trợ khớp ký tự đại diện theo mặc định.
Ví dụ (VLOOKUP): =VLOOKUP(“Vatican*”,B3:D7,2,FALSE)
Ví dụ (XLOOKUP): =XLOOKUP(“Vatican*”,B3:B7,C3:C7,,2)
Cả hai công thức đều sử dụng khớp ký tự đại diện và sẽ khớp “Vatican City” trong bảng trên, trả về dân số của nó.
Bây giờ, hãy khám phá sự khác biệt giữa VLOOKUP và XLOOKUP trong một phạm vi không được sắp xếp.
Ngoài con số 0 (khớp chính xác) và 2 (khớp ký tự đại diện), XLOOKUP có hai chế độ khớp khác có thể sử dụng trong các phạm vi tìm kiếm không được sắp xếp. Đó là 1 (giá trị lớn nhất tiếp theo) và -1 (giá trị nhỏ nhất tiếp theo).
Sau đây là một số ví dụ:
Công thức #1 (B9): =XLOOKUP(A9,A2:A6,B2:B6,,1)
Khóa tìm kiếm (ngày trong A9) không được tìm thấy trong phạm vi tìm kiếm. Vì chế độ khớp là 1, công thức khớp giá trị ngày tiếp theo lớn nhất.
Công thức #2 (B11): =XLOOKUP(A9,A2:A6,B2:B6,,-1)
Khóa tìm kiếm (ngày trong A9) không được tìm thấy trong phạm vi tìm kiếm. Vì chế độ khớp là -1, công thức khớp giá trị ngày tiếp theo nhỏ nhất.
Công thức #3 (B13): =VLOOKUP(A9,A2:B6,2,FALSE)
Không cần phải nói, hàm VLOOKUP trả về giá trị #N/A vì nó không hỗ trợ khớp gần đúng trong các phạm vi không được sắp xếp.
Không có tìm kiếm từ dưới lên trong VLOOKUP
Sự khác biệt chính khác giữa VLOOKUP và XLOOKUP là VLOOKUP chỉ có thể tìm kiếm từ trên xuống dưới, trong khi XLOOKUP có thể tìm kiếm cả từ trên xuống dưới và từ dưới lên trên.
Giả sử bạn có nhiều trường hợp xuất hiện của các khóa tìm kiếm trong cột đầu tiên của phạm vi. VLOOKUP chỉ khớp với trường hợp xuất hiện đầu tiên, nhưng XLOOKUP có thể khớp với trường hợp xuất hiện đầu tiên hoặc cuối cùng dựa trên giá trị của đối số search_mode. Đối số search_mode có thể là 1 (từ trên xuống dưới) hoặc -1 (từ dưới lên trên).
VLOOKUP so với XLOOKUP trong một phạm vi được sắp xếp: Khác biệt và điểm tương đồng
Nếu bạn chỉ định is_sorted là TRUE trong VLOOKUP, bạn phải sắp xếp dữ liệu của bạn theo thứ tự tăng dần theo cột 1 trong phạm vi. Nói cách khác, cột đầu tiên của phạm vi của bạn phải được sắp xếp theo thứ tự A-Z để tránh kết quả không chính xác.
Ngược lại, XLOOKUP hoạt động với một lookup_range được sắp xếp theo thứ tự tăng dần hoặc giảm dần. Bạn có thể chỉ định cả tìm kiếm nhị phân 2 (thứ tự A-Z) hoặc -2 (thứ tự Z-A) trong đối số search_mode. Đây là một sự khác biệt quan trọng khác giữa các hàm VLOOKUP và XLOOKUP.
Trong phạm vi đã sắp xếp, cả VLOOKUP và XLOOKUP đều có thể thực hiện khớp chính xác hoặc khớp xấp xỉ.
Trong phạm vi được sắp xếp theo thứ tự A-Z, VLOOKUP khớp với trường hợp xuất hiện cuối cùng trong khớp chính xác, trong khi XLOOKUP khớp với trường hợp xuất hiện đầu tiên.
Dữ liệu đã sắp xếp theo thứ tự tăng dần:
Trong phạm vi đã sắp xếp tăng dần, cả VLOOKUP và XLOOKUP đều khớp với giá trị tiếp theo nhỏ nhất trong khớp xấp xỉ. Cả hai hàm đều trả về kết quả khớp gần nhất. Tuy nhiên, XLOOKUP cũng có thể trả về giá trị tiếp theo lớn nhất.
Dữ liệu đã sắp xếp theo thứ tự tăng dần:
Trong tất cả các trường hợp, nếu có nhiều khớp xảy ra, kết quả sẽ là từ hàng gần nhất với khóa tìm kiếm.
Đây là những sự khác biệt chính và điểm tương đồng giữa VLOOKUP và XLOOKUP trong Google Sheets.
Có lý do nào để sử dụng VLOOKUP thay cho XLOOKUP không?
Sau khi đi qua một số ví dụ công thức, rõ ràng rằng XLOOKUP vượt trội hơn VLOOKUP ở gần như mọi khía cạnh. Tuy nhiên, có một tình huống mà VLOOKUP có lợi thế – nó có thể trả về kết quả mảng 2D, trong khi XLOOKUP không thể (ít nhất là chưa).
Hãy xem xét ví dụ sau đây, trong đó VLOOKUP lấy hai giá trị tìm kiếm (khóa tìm kiếm) và trả về kết quả mảng 2D, sử dụng hai số chỉ số:
=ARRAYFORMULA(VLOOKUP(A11:A12,B3:D7,{2,3},FALSE))
Hình ảnh dưới đây minh hoạ kết quả cho kịch bản này:
Mảng kết quả 2D:
Mảng trả về của XLOOKUP là một chiều. Do đó, nếu bạn cố gắng sử dụng XLOOKUP để trả về một mảng 2D, bạn chỉ nhận được kết quả từ cột đầu tiên hoặc hàng đầu tiên của phạm vi kết quả.
Công thức sau sẽ trả về tất cả các giá trị từ cột đầu tiên của phạm vi kết quả từ các hàng khớp tìm thấy:
=ARRAYFORMULA(XLOOKUP(A11:A12,B3:B7,C3:D7))
Kết quả: {10873;38786}
Tuy nhiên, tôi có một cách giải quyết cho vấn đề này bằng cách sử dụng các hàm XMATCH và CHOOSEROWS, mà tôi sẽ trình bày trong hướng dẫn của mình có tiêu đề “Hàm CHOOSEROWS trong Google Sheets”.
Kết luận
Để tóm lại, đây là những sự khác biệt chính giữa VLOOKUP và XLOOKUP trong Google Sheets:
-
XLOOKUP sử dụng một phạm vi tìm kiếm và một phạm vi kết quả, cung cấp linh hoạt hơn so với phạm vi và chỉ số của VLOOKUP, chỉ cho phép tìm kiếm xuống cột bên trái nhất.
-
VLOOKUP yêu cầu một bọc IFNA để loại bỏ lỗi #N/A, trong khi XLOOKUP tích hợp tính năng này.
-
XLOOKUP hỗ trợ khớp gần đúng của khóa tìm kiếm trong phạm vi không được sắp xếp, trong khi VLOOKUP thiếu chức năng này.
-
VLOOKUP tìm kiếm từ trên xuống dưới, trong khi XLOOKUP có thể tìm kiếm từ trên xuống dưới và từ dưới lên trên.
-
XLOOKUP có thể khớp gần đúng trong cả phạm vi tìm kiếm được sắp xếp tăng dần (A-Z) và sắp xếp giảm dần (Z-A). VLOOKUP chỉ khớp với trường hợp xuất hiện cuối cùng trong phạm vi tìm kiếm được sắp xếp tăng dần.
-
Trong một phạm vi tìm kiếm được sắp xếp, cả VLOOKUP và XLOOKUP đều có thể thực hiện khớp chính xác hoặc khớp xấp xỉ.
-
XLOOKUP có thể thay thế HLOOKUP và VLOOKUP.
Dựa trên so sánh này, rõ ràng XLOOKUP là lựa chọn tốt hơn cho việc tìm kiếm trong Google Sheets.
