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

Hàm XLOOKUP mới của Excel sẽ thay thế hàm VLOOKUP, mang đến sự thay thế hùng cường cho 1 trong số hàm thông dụng nhất của Excel. Hàm mới này xử lý một số có hạn của VLOOKUP và thêm chức năng khác. Bài viết này sẽ hướng dẫn các bạn cách dùng hàm XLOOKUP mới này.

• Cách sử dụng hàm Search trong Excel
• Cách phối hợp hàm Sumif và hàm Vlookup trên Excel
• Cách dùng hàm Lookup trong Excel

XLOOKUP là gì?

Hàm XLOOKUP mới là giải pháp cho một số hạn chế lớn của VLOOKUP. Ngoài ra nó còn thay thế hàm HLOOKUP. Ví dụ, XLOOKUP cũng có thể có thể nhìn sang trái, mặc định tìm kết quả chính xác và cấp phép chỉ định phạm vi ô thay vì số cột. VLOOKUP không dễ sử dụng và không linh hoạt.

Nói cách khác, hàm XLOOKUP cho phép bạn mau chóng tìm một giá trị trong một tập dữ liệu nhất định, theo cả chiều ngang và chiều dọc, sau đó trả về giá trị tương ứng của nó trong một hàng hoặc cột khác.

Có nhiều cách để sử dụng hàm XLOOKUP một cách tối đa.

Cách truy cập hàm XLOOKUP

Hiện nay, XLOOKUP chỉ có cho người sử dụng chương trình Insiders. Bất cứ ai tham gia chương trình Insider đều cũng đều có thể truy cập tính năng Excel mới nhất khi nó có sẵn. Microsoft sẽ sớm tung hàm này cho mọi thứ người sử dụng Office 365. Rất tiếc, XLOOKUP không khả dụng cho người sử dụng chạy những phiên bản Office 2010, 2013, 2016 và 2019.

Nếu bạn không sử dụng bộ Microsoft 365 trên máy tính của mình, bạn có thể cần phải cải tiến lên Microsoft 365 để xong xuôi được quyền truy cập vào chức năng XLOOKUP. Và nếu đang sử dụng Microsoft 365, thì bạn sẽ thấy tùy chọn đã được bật trong Excel. XLOOKUP cũng có thể có sẵn trên Office 365 Online.

Cú pháp hàm XLOOKUP

Cú pháp của hàm XLOOKUP tương tự như cú pháp của hàm VLOOKUP và HLOOKUP. Nếu đã từng sử dụng chúng, bạn sẽ thấy việc sử dụng XLOOKUP tiện lợi hơn. Đây là cú pháp cho hàm XLOOKUP trong Excel:

   =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])   

Hàm XLOOKUP hỗ trợ tối đa 6 đối số và này là giá trị của chúng.

1. lookup_value (bắt buộc): Giá trị mà bạn mong muốn tìm.

2. lookup_array (bắt buộc): Mảng mà bạn mong muốn kiếm tìm giá trị tra cứu.

3. return_array (bắt buộc): Mảng từ nơi bạn muốn tìm nạp và trả về các giá trị khi chi phí trị tra cứu được tìm thấy.

4. [if_not_found] (tùy chọn): Khi không tìm thấy kết quả phù hợp, này là giá trị được trả về.

5. [match_mode] (tùy chọn): Đối số này cho phép bạn chỉ định loại đối sánh mà mình đang tìm kiếm. Có các giá trị khác nhau để chỉ định nó:

• 0 – Tìm kiếm một kết quả khớp chính xác và giá trị phải khớp chuẩn xác với giá trị trong lookup_array. Giá trị này cũng được đặt làm mặc định lúc không được đề cập.
• -1 – Tìm kiếm kết quả phù hợp chuẩn xác và sau khi được tìm thấy, sẽ quay trở lại giá trị nhỏ hơn tiếp theo.
• 1 – Tìm kiếm kết quả thích hợp chuẩn xác và khi được tìm thấy sẽ trả về giá trị lớn hơn tiếp theo.
• 2 – Thực hiện đối sánh từng phần bằng cách dùng các ký tự đại diện trong đó *, ? và ~ có ý nghĩa đặc biệt.

6. [search_mode] (tùy chọn): Được sử dụng để chỉ định chế độ tìm kiếm của XLOOKUP trong lookup_array. Có các giá trị không giống nhau để chỉ định giống nhau:

• 1 – Thực hiện tìm kiếm bắt đầu từ mục đầu tiên. Giá trị này được đặt làm mặc định lúc không có gì được chỉ định.
• -1 – Thực hiện kiếm tìm ngược lại bắt đầu từ mục cuối cùng.
• 2 – Thực hiện kiếm tìm nhị phân trong lookup_array nơi dữ liệu cần sắp xếp theo thứ tự tăng dần. Nếu dữ liệu chưa được sắp xếp, nó cũng có thể có thể tạo ra lỗi hoặc kết quả sai.
• -2 – Thực hiện kiếm tìm nhị phân trong lookup_array nơi dữ liệu cần bố trí theo thứ tự giảm dần. Nếu dữ liệu chưa được sắp xếp, nó cũng có thể tạo nên lỗi hoặc kết quả sai.

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

Hãy xem thí dụ dưới đây để biết cách XLOOKUP hoạt động. Trong thí dụ này, chúng ta cần trả lại cơ quan từ cột F cho mỗi ID ở cột A.

Đây là ví dụ kiếm tìm kết quả chính xác, nhưng hàm XLOOKUP chỉ yêu cầu ba loại thông tin.

Hình ảnh bên dưới hiển thị XLOOKUP với 5 đối số, nhưng ba đối số trước mắt cần cho kết quả chính xác. Vì vậy hãy tập trung vào chúng:

• Lookup_value : Giá trị kiếm tìm
• Lookup_array : Phạm vi kiếm tìm
• Return_array : Phạm vi chứa giá trị cần trả về

Trong ví dụ này, chúng ta sẽ sử dụng công thức:

   =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)   

Bây giờ chúng ta cùng khám phá một số ưu điểm của XLOOKUP so với VLOOKUP.

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

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

Và đừng quên, XLOOKUP cũng có thể xem dữ liệu còn lại của ô đã chọn, không giống như VLOOKUP.

Bạn cũng không mắc phải vấn đề lỗi công thức khi thêm cột mới. Nếu điều này diễn ra trong bảng tính, độ rộng trả về sẽ có điều tiết tự động.

Khớp tuyệt đối theo mặc định

Khi sử dụng VLOOKUP, người sử dụng phải chỉ định khớp tuyệt đối nếu muốn. Nhưng với XLOOKUP mặc định là khớp tuyệt đối. Điều này giúp giảm đối số thứ tư và đảm bảo người mới dùng ít mắc lỗi hơn. Tóm lại, XLOOKUP yêu cầu ít câu hỏi hơn VLOOKUP và thân thiện với người dùng.

XLOOKUP có thể tìm về bên trái

Khả năng chọn độ rộng tra cứu giúp XLOOKUP linh hoạt hơn so với 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 phương pháp tìm kiếm cột ngoài cùng bên trái của bảng và sau đó trả về từ một số cột được chỉ định ở bên phải.

Trong thí dụ dưới đây, chúng ta cần tìm kiếm ID (cột E) và trả về tên người (cột D).

Sử dụng công thức dưới đây:

   =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)   

Sử dụng XLOOKUP để kiếm tìm phạm vi

Mặc dù không thông dụng như khớp tuyệt đối, nhưng bạn có thể sử dụng công thức kiếm tìm để tìm một giá trị trong phạm vi. Ví dụ, chúng ta muốn hoàn trả chiết khấu lệ thuộc vào số chi phí chi.

Lần này chúng ta không tìm một giá trị cụ thể và cần biết các giá trị trong cột B nằm ở phía trong phạm vi nào trong cột E để xác định mức chiết khấu.

XLOOKUP có một đối số thứ tư tùy chọn (hãy nhớ, nó mặc định là khớp tuyệt đối) được coi là match_mode.

Bạn cũng có thể thấy rằng XLOOKUP có thể tìm các kết quả gần đúng tốt hơn so với VLOOKUP.

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

Công thức trong tỉ dụ này trả về giá trị gần nhất nhỏ hơn giá trị tìm kiếm nếu không tìm thấy kết quả khớp tuyệt đối:

   =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)   

Tuy nhiên, đã diễn ra lỗi trong ô C7, đáng lẽ phải hoàn trả chiết khấu 0% vì chỉ chi 64, không đạt được tiêu chí để nhận chiết khấu.

Một ưu điểm khác của hàm XLOOKUP là nó không yêu cầu độ rộng kiếm tìm theo thứ tự nâng dần như VLOOKUP.

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

Công thức ngay tức thì sửa lỗi. Nó không có vấn đề với việc có 0 ở cuối bảng phạm vi.

XLOOKUP thay thế hàm HLOOKUP

Như đã đề cập, hàm XLOOKUP cũng cũng đều có thể thay thế hàm HLOOKUP. Một hàm có thể thay thế hai hàm, còn gì tuyệt hảo hơn.

Hàm HLOOKUP tra cứu theo phía ngang, được sử dụng để tìm kiếm theo hàng.

Hàm này không nổi tiếng như VLOOKUP nhưng hữu ích trong tình huống khi tiêu đề nằm ngay cột A và dữ liệu ở hàng 4, 5 như ví dụ bên dưới.

XLOOKUP có thể kiếm tìm ở cả hai chiều, dưới cột và dọc từ hàng.

Trong tỉ dụ này, công thức được sử dụng để trả về giá trị bán hàng liên quan đến tên trong cột A2. Nó nhìn theo hàng 4 để tìm tên và hoàn lại giá trị từ hàng 5:

   =XLOOKUP(A2,B4:E4,B5:E5)   

XLOOKUP cũng đều có thể nhìn từ dưới lên

Thông thường bạn phải nhìn từ trên xuống dưới trong 1 danh sách để tìm sự hiện diện đầu tiên của một giá trị. XLOOKUP có một đối số thứ năm mang tên search_mode. Đối số này cho phép chuyển hướng kiếm tìm để bắt đầu từ dưới lên trong một danh sách để tìm sự hiện hữu cuối cùng của một giá trị.

Trong thí dụ bên dưới, chúng ta cần tìm mức tồn kho của từng mặt hàng trong cột A.

Bảng tra cứu được sắp xếp theo thứ tự ngày tháng. Chúng ta muốn hoàn trả mức tồn kho từ lần cuối cùng kiểm tra (lần xuất hiện cuối cùng của Product ID).

Đối số thứ năm của hàm XLOOKUP cung cấp bốn tùy chọn. Ở đây chúng ta sẽ sử dụng tùy chọn Search last-to-first .

Công thức sử dụng trong ví dụ này:

   =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)   

Trong công thức này, đối số thứ tư bị bỏ qua. Nó là tùy chọn và chúng ta muốn mặc định khớp tuyệt đối.

Hàm XLOOKUP là sự kế thừa được mong đọi cho tất cả hai hàm VLOOKUP và HLOOKUP. Một loạt các tỉ dụ đã được dùng trong bài viết này để minh chứng những ưu điểm của hàm XLOOKUP.

Chúc các bạn thực hiện thành công!

hàm xlookup,xlookup,sử dụng hàm xlookup,so sánh xlookup và vlookup,hàm excel mới,hàm tìm kiếm,cách sử dụng hàm xlookup,xlookup và vlookup

Nội dung Cách sử dụng hàm XLOOKUP trong Excel được tổng hợp sưu tầm biên tập bởi: Tin Học Trường Tín. Mọi ý kiến vui lòng gửi Liên Hệ cho truongtin.top để điều chỉnh. truongtin.top tks.