Sự khác nhau giữa hàm XLOOKUP và VLOOKUP trong Excel

Hàm XLOOKUP & VLOOKUP trong Excel là gì? 

Các hàm XLOOKUP và VLOOKUP trong Excel đều được sử dụng để tìm hoặc 'tra cứu' một giá trị từ bảng hoặc danh sách rồi trả về kết quả có liên quan. 

Như hình ảnh bên dưới, bạn có một tập dữ liệu Excel ở bên trái với các thành viên đăng ký tham gia hội thảo, được gọi là tập dữ liệu Đăng ký hội thảo (Workshop Registrations). Bảng dữ liệu này rõ ràng thiếu một số thông tin về các thành viên, ví dụ như cột “Last Name” (họ). 
 

Thông tin về các thành viên có thể được tìm thấy trong một tập dữ liệu khác, được gọi là tập dữ liệu Members (thành viên) và có thể được truy xuất từ đó để hoàn thành các trường bắt buộc trong tập dữ liệu Workshop Registrations.

Để lấy thông tin từ một tập dữ liệu này để hoàn thành tập dữ liệu kia, bạn thường sử dụng hàm VLOOKUP. Nếu bạn có quyền truy cập vào Office 365, bạn có thể sử dụng hàm XLOOKUP thay thế cho VLOOKUP/ HLOOKUP để hoàn thành công việc.

Cách bạn tra cứu thông tin chi tiết của thành viên trong tập dữ liệu sẽ có một chút khác biệt đối với hàm XLOOKUP và VLOOKUP. Chúng ta hãy xem xét cú pháp và sự khác biệt của từng loại.

Cú pháp của hàm XLOOKUP và VLOOKUP

Cả hai công thức của hai hàm trên đều yêu cầu tối thiểu ba đối số. Dấu ngoặc vuông cho biết các đối số tùy chọn.

=vlookup(lookup_value, table_array, col_index_num, [range_lookup])=xlookup(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Bảng so sánh giữa hàm XLOOKUP và VLOOKUP

Lookup value

Đây là giá trị bạn muốn tìm trên danh sách mới (ví dụ: ID thành viên). Cả hàm VLOOKUP và XLOOKUP thường tra cứu (tìm kiếm) một giá trị duy nhất.  

Table array (VLOOKUP)/Lookup array (XLOOKUP)

Điều này thể hiện sự khác biệt lớn nhất giữa hàm VLOOKUP và XLOOKUP và trong ví dụ trên sẽ là tập dữ liệu của thành viên, nơi bạn sẽ tìm thấy cả thành viên (giá trị tra cứu- lookup value) và họ hoặc tên của thành viên (giá trị trả về- return value).

Trong đó hàm VLOOKUP sử dụng Table_array, khu vực này chỉ cột cho giá trị tra cứu và trải dài để bao gồm các cột cho giá trị trả về. Đối số tiếp theo (giá trị trả về) tham chiếu trở lại mảng này bằng cách chỉ ra số cột có liên quan của mảng bảng chứa giá trị trả về.

Excel được lập trình để tìm kiếm giá trị tra cứu trong cột bên trái của phạm vi này và trả về một giá trị ở bên phải của phạm vi này, được biểu thị bằng số cột từ đối số tiếp theo.

Hàm XLOOKUP thì tách mảng tra cứu và mảng trả về thành hai đối số. Mảng tra cứu phải chứa giá trị mà Excel sẽ tìm kiếm và đối số mảng trả về sẽ chứa giá trị được trả về. 

Việc tách các mảng giúp cho hàm XLOOKUP linh hoạt hơn nhiều so với hàm VLOOKUP trong sử dụng. Mảng tra cứu bây giờ có thể ở bên trái hoặc bên phải của cột giá trị trả về và XLOOKUP có thể hoạt động như một hàm VLOOKUP hoặc một hàm HLOOKUP.

Column index number (VLOOKUP) / Return array (XLOOKUP)

Đối số này chỉ ra phạm vi mà giá trị trả về có thể được tìm thấy. Trong ví dụ của bài viết này, đây sẽ là tên của thành viên.

Trong hàm VLOOKUP, đây là một số duy nhất tham chiếu trở lại mảng bảng (đối số trước đó chỉ ra toàn bộ phạm vi nơi giá trị tìm kiếm và giá trị trả về có thể được tìm thấy). 

Hàm VLOOKUP rất dễ bị 'phá vỡ' nếu số cột này được mã hóa cứng vào đối số và đây là một ưu điểm đối với hàm XLOOKUP.  

Hàm XLOOKUP thì yêu cầu toàn bộ phạm vi giá trị cho đối số này để chỉ ra mảng mà giá trị trả về có thể được tìm thấy. Phạm vi này phải được căn chỉnh với mảng tra cứu (đối số trước đó) về độ dài và vị trí để tránh lỗi #VALUE!.

Mảng trả về trong XLOOKUP cũng có thể chứa nhiều hơn một cột trả về trong mảng trả về, cột này sẽ trả về nhiều hơn một giá trị liên quan đến giá trị tra cứu. Bạn sẽ phải sử dụng hai hàm VLOOKUP để quản lý giống nhau.

Hai đối số (sự phân tách của các phạm vi) chỉ ra những lợi ích lớn nhất đối với XLOOKUP gồm:

- Mảng tra cứu có thể ở bên phải hoặc bên trái của mảng trả về

- XLOOKUP khó bị phá vỡ hơn vì hai mảng (tra cứu và trả về) là tương đối và không được mã hóa cứng vào công thức.

Đối số tùy chọn 

Nếu không tìm thấy (chỉ XLOOKUP)

Nếu XLOOKUP không thể tìm thấy giá trị, nó sẽ trả về một thông báo mặc định hoặc một thông báo tùy chỉnh. Thông báo có thể là, “The member you are looking for is not registered in our database” (Thành viên bạn đang tìm kiếm chưa được đăng ký trong cơ sở dữ liệu của chúng tôi).

Trừ khi bạn thêm hàm IFERROR thì hàm VLOOKUP sẽ trả về giá trị #N/A! (không khả dụng), có thể giúp bạn biết rằng đã làm sai điều gì đó và hàm VLOOKUP không hoạt động.

Phạm vi tra cứu (VLOOKUP)/Chế độ đối sánh (XLOOKUP)

Đối sánh chính xác (Fals/0) và gần đúng (True/1) trong VLOOKUP có các khả năng tương tự trong XLOOKUP. Bảng so sánh sự khác biệt dưới đây sẽ minh họa rõ hơn.

XLOOKUP mặc định là đối sánh chính xác trong khi đó VLOOKUP mặc định là đối sánh gần đúng. Vì sự kết hợp chính xác được sử dụng thường xuyên nhất nên tùy chọn này sẽ giúp cho hàm XLOOKUP hiệu quả hơn. Trên hết, XLOOKUP cung cấp một tùy chọn bổ sung đối sánh gần đúng trả về giá trị lớn hơn tiếp theo.

Chế độ tìm kiếm (XLOOKUP)

Hàm VLOOKUP được lập trình để tìm kiếm từ trên xuống dưới (từ đầu đến cuối) và sẽ trả về mục phù hợp đầu tiên cho các đối sánh chính xác và gần đúng.

Hàm XLOOKUP thì có bốn tùy chọn tìm kiếm có thể có:

- Đầu tiên đến cuối cùng (1) - mặc định

- Cuối cùng đến đầu tiên (-1)

- Tìm kiếm nhị phân - giả sử sắp xếp thứ tự tăng dần cho phạm vi tra cứu (2)

- Tìm kiếm nhị phân - giả sử sắp xếp thứ tự giảm dần cho phạm vi tra cứu (-2)

Các phương pháp tìm kiếm mở ra chức năng bổ sung cho hàm XLOOKUP, bạn có thể tìm kiếm lần xuất hiện cuối cùng của một mục trong một phạm vi. Trong khi phương pháp tìm kiếm nhị phân nhanh hơn trong các trang tính có số lượng lớn dữ liệu nhưng dựa trên thực tế là dữ liệu trong phạm vi tra cứu được sắp xếp.

Hàm VLOOKUP thì tìm kiếm từ đầu đến cuối và chỉ có thể tìm từ cuối đến đầu tiên khi sử dụng một số thủ thuật khác trong Excel. 

Sự khác biệt khác

Sắp xếp phạm vi tra cứu

Tìm kiếm gần đúng của hàm VLOOKUP (True) sẽ trả về giá trị không chính xác nếu phạm vi tìm kiếm không được sắp xếp theo thứ tự tăng dần. 

XLOOKUP bị hạn chế bởi việc sắp xếp khi nó tìm kiếm từ cuối cùng đến đầu tiên hoặc khi sử dụng tìm kiếm nhị phân. 

Xem thêm: Hướng dẫn cách dùng hàm VLOOKUP nâng cao

Khả năng tương thích với các phiên bản Excel cũ hơn

VLOOKUP không có vấn đề tương thích với các phiên bản Excel cũ hơn nhưng XLOOKUP chỉ có sẵn trên Office 365.  

Nếu bạn muốn chia sẻ bảng dữ liệu Excel có sử dụng hàm XLOOKUP với các cộng tác viên khác, hãy đảm bảo rằng họ cũng có quyền truy cập vào Office 365.

Tra cứu theo chiều ngang

XLOOKUP không chỉ cải thiện chức năng VLOOKUP mà còn thay thế HLOOKUP.  

Xử lý sự cố

Bao gồm các tiêu đề trong phạm vi tra cứu

Luôn luôn có rủi ro khi bao gồm các tiêu đề từ phạm vi tra cứu trong VLOOKUP hoặc XLOOKUP vì Excel có thể hiểu tiêu đề là một phần của dữ liệu và kết quả là có thể trả về các giá trị không chính xác.

Thông báo lỗi

VLOOKUP:

• #N/A - Giá trị được tìm kiếm không có sẵn. 

Ví dụ: không thể tìm thấy số thành viên trong tập dữ liệu thành viên. Với kiểu lỗi này, bạn có thể dễ dàng khắc phục lỗi #N/A khi sử dụng hàm VLOOKUP một cách đơn giản.

• #REF - Giá trị trả về nằm ngoài mảng bảng đã xác định. 

Ví dụ: Bạn đã chọn năm cột và muốn giá trị trả về từ cột 6.

XLOOKUP:

• #N/A - Giá trị được tìm kiếm không có sẵn. 
• Ví dụ: Không thể tìm thấy số thành viên trong tập dữ liệu thành viên (giống như hàm VLOOKUP).
• #REF - Workbook trong công thức không mở.
• #VALUE - Kích thước của mảng tra cứu và mảng trả về không được căn chỉnh về vị trí và độ dài. 
• Ví dụ: Mảng tra cứu dài năm hàng và mảng trả về dài bốn hàng hoặc mảng tra cứu nằm trong một hàng và mảng trả về nằm trong một cột.

Xem thêm: Hướng dẫn hàm VLOOKUP trong Excel cho người mới bắt đầu: Học với các ví dụ

Khi nào sử dụng XLOOKUP hoặc VLOOKUP

XLOOKUP có thể được sử dụng cho các tác vụ tương tự như VLOOKUP và sau đó có thêm một số tính năng và để hiểu rõ hơn bạn có thể xem qua các ví dụ cụ thể dưới đây.

Ví dụ 1 - Tìm một giá trị tồn tại và duy nhất

Ví dụ đầu tiên tìm kiếm một số thành viên tồn tại (khớp chính xác) trong tập dữ liệu thành viên và là duy nhất - một giá trị mà bạn mong đợi sẽ dễ dàng tìm thấy. 

B4: =VLOOKUP(A4,$I$4:$L$14,2,FALSE)

B4: =XLOOKUP(A4,$J$4:$J$14,$K$4:$L$14,"Membership number not found")

Lưu ý: Các giá trị tra cứu của hai hàm trên là giống nhau. Phạm vi của hàm VLOOKUP bao gồm toàn bộ cột, nhưng XLOOKUP tách tham chiếu dãy với hàng loạt để tìm kiếm và một để tìm giá trị trả lại. Cũng lưu ý rằng XLOOKUP đã sử dụng một công thức để trả về hai giá trị.

Ví dụ 2 - Tìm một giá trị không tồn tại

Ví dụ này yêu cầu Excel tìm một thành viên không tồn tại trong tập dữ liệu của thành viên đó để tạo ra tên.

VLOOKUP

Hàm VLOOKUP chính xác trả về giá trị #N/A – Được hiểu là không khả dụng.

XLOOKUP

Đối sánh chính xác XLOOKUP sẽ không tìm thấy gì nhưng sẽ trả về một thông báo tùy chỉnh.

Ví dụ 3 - Tìm một giá trị ở bên trái 

Trong ví dụ này, Excel phải tìm kiếm giá trị là 4 trong cột có tên '99' và trả về 'số' được liên kết ở bên trái.

Trong trường hợp này hàm VLOOKUP không thể thực hiện theo đúng yêu cầu, vì vậy có thể sử dụng chỉ mục và đối sánh thay thế. Nhưng so sánh đối với hàm XLOOKUP khá đơn giản.

D4: =XLOOKUP(A4,$J$4:$J$14,$I$4:$I$14)

Kết luận

Như vậy hàm XLOOKUP mới là một cải tiến rõ ràng đối với hàm VLOOKUP và thay thế rất nhiều tùy chọn bị hàm VLOOKUP “bỏ qua” để hoạt động trong một số trường hợp nhất định (INDEX, MATCH, IFERROR …). 

Tiếc rằng hàm XLOOKUP không có sẵn cho các phiên bản Excel cũ hơn và bạn sẽ phải ghi nhớ điều này khi chia sẻ tệp Excel với mọi người.

Tuy nhiên hàm VLOOKUP vẫn là một người bạn đồng hành cũ đáng tin cậy và có thể vẫn rất hữu ích trong một số trường hợp nhất định. Nhưng XLOOKUP chắc chắn là một cải tiến tuyệt vời và rất cần thiết.