Công thức & hàm

XLOOKUP

Sử dụng hàm XLOOKUP để tìm nội dung trong một bảng hoặc dải ô theo hàng. Ví dụ: tra cứu giá của linh kiện ô tô theo số linh kiện hoặc tìm tên nhân viên dựa trên ID nhân viên của họ. Với hàm XLOOKUP, bạn có thể tìm từ tìm kiếm trong một cột và trả về kết quả từ cùng một hàng trong cột khác, bất kể cột trả về đang ở bên nào.

Lưu ý: Tuy nhiên, XLOOKUP không sẵn dùng trong Excel 2016 và Excel 2019, tuy nhiên, bạn có thể gặp phải tình huống khi sử dụng sổ làm việc trong Excel 2016 hoặc Excel 2019 bằng hàm XLOOKUP trong đó do người khác tạo bằng phiên bản Excel mới hơn.

Trình duyệt của bạn không hỗ trợ video. Hãy cài đặt Microsoft Silverlight, Adobe Flash Player hoặc Internet Explorer 9.

Cú pháp

Hàm XLOOKUP tìm kiếm một dải ô hoặc một mảng, rồi trả về mục tương ứng với giá trị khớp đầu tiên tìm được. Nếu không tồn tại kết quả khớp nào thì hàm XLOOKUP có thể trả về kết quả khớp gần nhất (gần đúng). 

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

Đối số

Mô tả

lookup_value

Bắt buộc*

Giá trị cần tìm kiếm *Nếu bỏ qua, hàm XLOOKUP trả về các ô trống tìm thấy trong lookup_array.   

mảng tìm kiếm

Bắt buộc

Mảng hoặc dải ô cần tìm kiếm

return_array

Bắt buộc

Mảng hoặc phạm vi cần trả về

Tôi không if_not_found tôi.

Tùy chọn

Trong trường hợp không tìm thấy kết quả khớp hợp lệ, trả về văn bản [if_not_found] mà bạn cung cấp.

Nếu không tìm thấy kết quả khớp hợp lệ và [if_not_found] bị thiếu, thì kết quả #N/A được trả về.

Tôi không match_mode tôi.

Tùy chọn

Xác định kiểu khớp:

0 - Kết quả khớp chính xác. Nếu không tìm thấy, hãy trả #N/A. Đây là tùy chọn mặc định.

-1 - Trận đấu chính xác. Nếu không tìm thấy, hãy trả về mục nhỏ tiếp theo.

1 - Trận đấu chính xác. Nếu không tìm thấy, hãy trả về mục lớn hơn tiếp theo.

2 - Một kết quả khớp ký tự đại diện trong đó *, ?, và ~ có ý nghĩa đặc biệt.

Tôi không search_mode tôi.

Tùy chọn

Chỉ định chế độ tìm kiếm cần sử dụng:

1 - Thực hiện tìm kiếm bắt đầu từ mục đầu tiên. Đây là tùy chọn mặc định.

-1 - Thực hiện tìm kiếm đảo ngược bắt đầu từ mục cuối cùng.

2 - Thực hiện tìm kiếm nhị phân dựa trên lookup_array sắp xếp theo thứ tự tăng dần . Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về.

-2 - Thực hiện một tìm kiếm nhị phân dựa trên lookup_array được sắp xếp theo thứ tự giảm dần . Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về.

Ví dụ

Ví dụ 1    dùng XLOOKUP để tra cứu tên quốc gia trong một dải ô, rồi trả về mã quốc gia điện thoại của quốc gia đó. Nó bao gồm các lookup_value (ô F2), lookup_array (phạm vi B2:B11) và các đối return_array (phạm vi D2:D11). Hàm này không bao gồm tham đối match_mode vì XLOOKUP cho kết quả khớp chính xác theo mặc định.

Ví dụ về hàm XLOOKUP dùng để trả về Tên nhân viên và Phòng ban dựa trên ID Nhân viên. Công thức là =XLOOKUP(B2,B5:B14,C5:C14)

Lưu ý: XLOOKUP dùng mảng tra cứu và mảng trả về, trong khi VLOOKUP dùng một mảng bảng duy nhất theo sau là số chỉ mục cột. Công thức VLOOKUP tương đương trong trường hợp này sẽ là: =VLOOKUP(F2,B2:D11,3,FALSE)

———————————————————————————

Ví dụ 2    tra cứu thông tin nhân viên dựa trên số ID nhân viên. Không giống như hàm VLOOKUP, XLOOKUP có thể trả về mảng có nhiều mục, vì vậy một công thức duy nhất có thể trả về cả tên nhân viên và phòng ban từ các ô C5:D14.

Ví dụ về hàm XLOOKUP dùng để trả về Tên nhân viên và Phòng ban dựa trên IDt nhân viên. Công thức là: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

Ví dụ 3    thêm một if_not_found đối số vào ví dụ trước đó.

Ví dụ về hàm XLOOKUP dùng để trả về Tên Nhân viên và Phòng ban dựa trên ID Nhân viên với đối if_not_found nhân viên. Công thức là =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Không tìm thấy nhân viên")

———————————————————————————

Ví dụ 4    trong cột C cho thu nhập cá nhân được nhập vào ô E2 và tìm thuế suất khớp ở cột B. Nó đặt tham đối if_not_found trả về 0 (không) nếu không tìm thấy gì. Tham match_mode được đặt thành 1, nghĩa là hàm sẽ tìm kiếm kết quả khớp chính xác và nếu không tìm thấy kết quả khớp, nó trả về mục lớn hơn tiếp theo. Cuối cùng, tham search_mode được đặt thành 1, có nghĩa là hàm sẽ tìm kiếm từ mục đầu tiên đến mục cuối cùng.

Hình ảnh hàm XLOOKUP dùng để trả về thuế suất dựa trên thu nhập tối đa. Đây là kết quả gần khớp. Công thức là: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Lưu ý: Cột lookup_array của XARRAY nằm ở bên phải cột return_array, trong khi VLOOKUP chỉ có thể nhìn từ trái sang phải.

———————————————————————————

Ví dụ 5    dùng hàm XLOOKUP được lồng để thực hiện khớp dọc và ngang. Trước tiên, nó tìm Kiếm Lợi nhuận Gộp trong cột B, sau đó tìm Q1 trong hàng trên cùng của bảng (phạm vi C5:F5) và cuối cùng trả về giá trị tại giao điểm của hai bảng. Điều này tương tự như sử dụng các hàm INDEXvà MATCH cùng nhau.

Mẹo: Bạn cũng có thể dùng hàm XLOOKUP để thay thế hàm HLOOKUP .

Hình ảnh của hàm XLOOKUP dùng để trả về dữ liệu ngang từ bảng bằng cách lồng 2 XLOOKUP. Công thức là: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

Lưu ý: Công thức trong các ô D3:F3 là: =XLOOKUP(D2,$B 6:$B 17;XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17)).

———————————————————————————

Ví dụ 6    dùng hàm SUM và hai hàm XLOOKUP lồng nhau để tính tổng tất cả các giá trị giữa hai dải ô. Trong trường hợp này, chúng ta muốn tính tổng các giá trị cho nho, chuối và bao gồm lê, nằm giữa hai giá trị này.

Dùng hàm XLOOKUP với hàm SUM để tính tổng một phạm vi giá trị nằm giữa hai vùng chọn

Công thức trong ô E3 là: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Cách thức hoạt động? Hàm XLOOKUP trả về một phạm vi, vì vậy khi tính toán, công thức sẽ trông giống như thế này: =SUM($E$7:$E$9). Bạn có thể tự mình xem cách hoạt động bằng cách chọn một ô có công thức XLOOKUP tương tự như công thức này, rồi chọn Công thức > Kiểm định Công thức >Đánh giá Công thức, rồi chọn Đánh giá để thực hiện từng bước tính toán. 

Lưu ý: Nhờ MVP Microsoft Excel, Bill Jelen, đã đề xuất ví dụ này.

———————————————————————————

Xem thêm

Bạn luôn có thể hỏi chuyên gia trong Cộng đồng kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.

Hàm XMATCH

Các hàm Excel (theo thứ tự bảng chữ cái)

Các hàm Excel (theo thể loại)

Bạn cần thêm trợ giúp?

Bạn muốn xem các tùy chọn khác?

Khám phá các lợi ích của gói đăng ký, xem qua các khóa đào tạo, tìm hiểu cách bảo mật thiết bị của bạn và hơn thế nữa.

Cộng đồng giúp bạn đặt và trả lời các câu hỏi, cung cấp phản hồi và lắng nghe ý kiến từ các chuyên gia có kiến thức phong phú.