Hàm XLOOKUP tùy chỉnh trong Excel

Posted on: 2020年11月12日 2020年11月13日
Tags: lookup-formulas

Chức năng XLOOKUP dành riêng cho chương trình nội bộ của office 365.

Hàm LOOKUP có nhiều chức năng khắc phục được nhiều điểm yếu của hàm VLOOKUP và HLOOKUP nhưng đáng buồn là hiện tại nó không có sẵn cho chúng ta. Nhưng đừng lo, chúng ta có thể tạo một hàm XLOOKUP hoạt động giống hệt như hàm XLOOKUP sắp tới trong MS Excel. Chúng tôi sẽ thêm từng chức năng cho nó.

Mã VBA của hàm XLOOKUP Hàm tra cứu UDF dưới đây sẽ giải quyết nhiều vấn đề. Sao chép nó hoặc tải xuống tệp bổ trợ xl bên dưới.

Function XLOOKUP(lk As Variant, lCol As Range, rCol As Range)

XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0))

End Function

Giải thích:

XLOOKUP Add-In]

chúng ta hãy xem nó hoạt động như thế nào trên trang tính excel.

Cú pháp của XLOOKUP

=XLOOKUP(lookup_value, lookup_array, result_array)

lookup_value: Đây là giá trị mà bạn muốn tìm kiếm trong

lookup_array.

lookup_array: Là phạm vi một chiều mà bạn muốn tìm kiếm lookup_value. result_array: Nó cũng là một phạm vi một chiều. Đây là phạm vi mà bạn muốn truy xuất giá trị.

Hãy xem hàm XLOOKUP này hoạt động.

XLOOKUP Ví dụ:

Ở đây, tôi có một bảng dữ liệu trong excel. Hãy khám phá một số chức năng bằng cách sử dụng bảng dữ liệu này.

Chức năng 1.ExactLookup ở bên trái và bên phải của giá trị tra cứu. Như chúng ta biết rằng hàm VLOOKUP trong Excel không thể truy xuất các giá trị từ bên trái của giá trị tra cứu. Để làm được điều đó, bạn phải sử dụng kết hợp INDEX-MATCH phức tạp. Nhưng không còn nữa.

Giả sử rằng chúng ta cần truy xuất tất cả các thông tin có sẵn trong bảng của một số cuộn. Trong trường hợp đó, bạn cũng sẽ phải truy xuất vùng nằm ở bên trái của cột số cuộn.

Viết công thức này, I2:

=XLOOKUP(H2,$B$2:$B$14,$A$2:$A$14)

Chúng tôi nhận được kết quả North cho cuộn số 112. Sao chép hoặc kéo xuống công thức trong các ô bên dưới để điền chúng với các vùng tương ứng.

Nó hoạt động như thế nào?

Cơ chế rất đơn giản. Hàm này tìm kiếm lookup_value trong lookup_array và trả về chỉ mục của một đối sánh chính xác đầu tiên. Sau đó, sử dụng chỉ mục đó để lấy giá trị từ result_array. Chức năng này hoạt động hoàn hảo với các phạm vi được đặt tên.

Tương tự, sử dụng công thức này để lấy giá trị từ mỗi cột.

Chức năng 2. ExactHorizontalLookup trên và dưới giá trị tra cứu. XLOOKUP cũng hoạt động như một hàm HLOOKUP chính xác. Hàm HLOOKUP có cùng một giới hạn như hàm VLOOKUP có. Nó không thể tìm nạp giá trị từ phía trên giá trị tra cứu. Nhưng XLOOKUP không chỉ hoạt động như HLOOKUP, nó còn khắc phục được điểm yếu đó. Hãy xem làm thế nào.

Theo giả thuyết, nếu bạn muốn so sánh hai bản ghi. Bản ghi tra cứu bạn đã có. Bản ghi mà bạn muốn so sánh nằm trên lookup_range. Sử dụng công thức này trong trường hợp đó.

=XLOOKUP(H7,$A$9:$E$9,$A$2:$E$2)

kéo công thức xuống và bạn có toàn bộ hồ sơ so sánh hàng.

Chức năng 3. Không cần số cột và khớp chính xác mặc định.

Khi bạn sử dụng hàm VLOOKUP, bạn phải cho biết số cột mà bạn muốn tìm nạp các giá trị. Muốn vậy, bạn phải đếm các cột hoặc sử dụng một số thủ thuật, nhờ sự trợ giúp của các chức năng khác. Với UDF XLOOKUP này, bạn không cần phải làm điều đó.

Nếu bạn đang sử dụng hàm VLOOKUP để chỉ tìm nạp một số giá trị từ một cột hoặc để kiểm tra xem một giá trị có tồn tại trong cột hay không, thì theo tôi đây là giải pháp tốt nhất.

Chức năng 4. Thay thế hàm INDEX-MATCH, VLOOKUP, HLOOKUP

Đối với các tác vụ đơn giản, hàm XLOOKUP của chúng tôi thay thế các hàm được đề cập ở trên.

Hạn chế của XLOOKUP:

Khi nói đến các công thức phức tạp, như //lookup-formulas/vlookup-with-dynamic-col-index.html[VLOOKUP with Dynamic Col Index] `trong đó VLOOKUP chúng tôi xác định cột tra cứu bằng các tiêu đề, XLOOKUP này sẽ không thành công.

Hiện tại, chúng tôi chưa thêm chức năng gần đúng, vì vậy tất nhiên, bạn không thể nhận được kết quả gần đúng. Chúng tôi sẽ bổ sung điều đó quá sớm.

Nếu hàm XLOOKUP không tìm thấy giá trị tra cứu, nó sẽ trả về lỗi #VALUE không phải # N / A.

Vâng, đây là cách bạn sử dụng XLOOKUP để truy xuất, tìm kiếm và xác thực các giá trị trong bảng excel. Bạn có thể sử dụng chức năng do người dùng xác định này để tra cứu dễ dàng ở bên trái hoặc phía trên của giá trị tra cứu. Nếu bạn vẫn có bất kỳ nghi ngờ nào hoặc bất kỳ yêu cầu cụ thể nào liên quan đến chức năng này hoặc truy vấn liên quan đến EXCEL 2010/2013/2016/2019/365 hoặc VBA, hãy hỏi nó trong phần nhận xét bên dưới. Bạn chắc chắn sẽ nhận được hồi âm.

Bài viết phổ biến:

Tải xuống tệp làm việc.