Cách sử dụng hàm XLOOKUP trong Microsoft Excel

Rate this post

XLOOKUP mới của Excel sẽ thay thế hàm VLOOKUP, cung cấp sự thay thế mạnh mẽ cho một trong những hàm phổ biến nhất của Excel. Chức năng mới này giải quyết một số hạn chế của VLOOKUP và có thêm chức năng. Sau đây là hướng dẫn chi tiết cách sử dụng hàm Xlookup từ Followeek.

XLOOKUP là gì?

Hàm XLOOKUP mới có giải pháp cho một số hạn chế lớn nhất của hàm VLOOKUP. Ngoài ra, nó cũng thay thế HLOOKUP. Ví dụ: XLOOKUP có thể nhìn về bên trái, mặc định là đối sánh chính xác và cho phép bạn chỉ định một dải ô thay vì một số cột. VLOOKUP không dễ sử dụng hay đa năng. Chúng tôi sẽ chỉ cho bạn cách tất cả hoạt động.

Hiện tại, XLOOKUP chỉ có sẵn cho người dùng trên chương trình Người dùng nội bộ. Bất kỳ ai cũng có thể tham gia chương trình Insiders để truy cập các tính năng Excel mới nhất ngay khi chúng có sẵn. Microsoft sẽ sớm bắt đầu triển khai nó cho tất cả người dùng Office 365.

Cách sử dụng hàm XLOOKUP

Hãy đi thẳng vào với một ví dụ về XLOOKUP đang hoạt động. Lấy dữ liệu ví dụ bên dưới. Chúng tôi muốn trả lại bộ phận từ cột F cho mỗi ID trong cột A.

Đây là một ví dụ tra cứu đối sánh chính xác cổ điển. Hàm XLOOKUP chỉ yêu cầu ba phần thông tin.

Hình ảnh bên dưới cho thấy XLOOKUP với sáu đối số, nhưng chỉ ba đối số đầu tiên là cần thiết để có một đối sánh chính xác. Vì vậy, hãy tập trung vào chúng:

Lookup_value: Những gì bạn đang tìm kiếm.
Lookup_array: Tìm ở đâu.
Return_array: phạm vi chứa giá trị trả về.

Công thức sau sẽ hoạt động cho ví dụ này: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Bây giờ chúng ta hãy khám phá một số lợi thế mà XLOOKUP có so với VLOOKUP tại đây.

Không có thêm số chỉ mục cột

Đối số thứ ba khét tiếng của hàm VLOOKUP là chỉ định số cột của thông tin cần trả về từ một mảng bảng. Đây không còn là vấn đề vì XLOOKUP cho phép bạn chọn phạm vi để trả về (cột F trong ví dụ này).

Và đừng quên, XLOOKUP có thể xem dữ liệu bên trái của ô đã chọn, không giống như VLOOKUP. Thêm về điều này bên dưới.

Bạn cũng không còn gặp vấn đề về công thức bị hỏng khi các cột mới được chèn vào. Nếu điều đó xảy ra trong bảng tính của bạn, phạm vi trả về sẽ tự động điều chỉnh.

XLOOKUP có thể nhìn sang trái

Có thể chọn phạm vi tra cứu làm cho XLOOKUP linh hoạt hơn VLOOKUP. Với XLOOKUP, thứ tự của các cột trong bảng không quan trọng.

VLOOKUP bị hạn chế bằng cách tìm kiếm cột ngoài cùng bên trái của bảng và sau đó quay trở lại từ một số cột cụ thể ở bên phải.

Trong ví dụ dưới đây, chúng ta cần tra cứu ID (cột E) và trả về tên của người đó (cột D).

Công thức sau có thể đạt được điều này: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Sử dụng XLOOKUP để tra cứu phạm vi

Mặc dù không phổ biến như đối sánh chính xác, cách sử dụng công thức tra cứu rất hiệu quả là tìm kiếm giá trị trong phạm vi. Lấy ví dụ sau. Chúng tôi muốn trả lại chiết khấu tùy thuộc vào số tiền đã chi tiêu.

Lần này chúng tôi không tìm kiếm một giá trị cụ thể. Chúng ta cần biết vị trí các giá trị trong cột B nằm trong phạm vi ở cột E. Điều đó sẽ xác định chiết khấu kiếm được.

XLOOKUP có đối số thứ năm tùy chọn (hãy nhớ, nó được đặt mặc định là đối sánh chính xác) được đặt tên là chế độ đối sánh.

Bạn có thể thấy rằng XLOOKUP có nhiều khả năng hơn với các kết quả phù hợp gần đúng hơn so với VLOOKUP.

Có tùy chọn để tìm kết quả gần nhất nhỏ hơn (-1) hoặc gần nhất lớn hơn (1) giá trị được tìm kiếm. Ngoài ra còn có một tùy chọn để sử dụng các ký tự đại diện (2) chẳng hạn như? hoặc là *. Cài đặt này không được bật theo mặc định như với hàm VLOOKUP.

Công thức trong ví dụ này trả về giá trị gần nhất nhỏ hơn giá trị được tìm kiếm nếu không tìm thấy kết quả khớp chính xác: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Tuy nhiên, có một lỗi trong ô C7 nơi trả về lỗi # N / A (đối số ‘nếu không tìm thấy’ không được sử dụng). Điều này đáng lẽ phải trả lại chiết khấu 0% vì chi tiêu 64 không đạt được tiêu chí cho bất kỳ khoản chiết khấu nào.

Một ưu điểm khác của hàm XLOOKUP là nó không yêu cầu phạm vi tra cứu phải theo thứ tự tăng dần như hàm VLOOKUP.

Nhập một hàng mới ở cuối bảng tra cứu, sau đó mở công thức. Mở rộng phạm vi đã sử dụng bằng cách nhấp và kéo các góc.

Công thức sửa lỗi ngay lập tức. Không có vấn đề gì với việc có “0” ở dưới cùng của phạm vi.

Cá nhân tôi vẫn sắp xếp bảng theo cột tra cứu. Có “0” ở dưới cùng sẽ khiến tôi phát điên. Nhưng thực tế là công thức không bị phá vỡ là tuyệt vời.

XLOOKUP có thể nhìn từ dưới lên

Thông thường, bạn cần tìm kiếm một danh sách để tìm lần xuất hiện đầu tiên (thường là duy nhất) của một giá trị. XLOOKUP có đối số thứ sáu có tên là chế độ tìm kiếm. Điều này cho phép chúng tôi chuyển việc tra cứu sang bắt đầu ở cuối và tìm kiếm danh sách để tìm lần xuất hiện cuối cùng của một giá trị.

Trong ví dụ dưới đây, chúng tôi muốn tìm mức tồn kho cho mỗi sản phẩm trong cột A.

Bảng tra cứu theo thứ tự ngày và có nhiều lần kiểm tra kho cho mỗi sản phẩm. Chúng tôi muốn trả lại mức còn hàng từ lần cuối cùng nó được kiểm tra (lần xuất hiện cuối cùng của ID sản phẩm).