Hàm XLOOKUP

Sử dụng hàm Xlookup khi bạn cần tìm mọi thứ trong bảng hoặc một phạm vi theo hàng. Ví dụ, tra cứu giá của một phần ô tô bằng số phầ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 XLOOKUP, bạn có thể nhìn vào một cột cho thuật ngữ tìm kiếm và trả về kết quả từ cùng một hàng trong một cột khác, bất kể cột trả về bên nào.

Gây ấn tượng mạnh bằng Thông báo

Lưu ý: Hàm này hiện sẵn dùng với người đăng ký Microsoft 365 trong kênh hiện tại. Nó sẽ sẵn dùng để Microsoft 365 người đăng ký trong kênh bán hàng năm doanh nghiệp bắt đầu vào tháng bảy 2020. Để biết thêm thông tin về cách thực hiện các tính năng được triển khai cho người đăng ký Microsoft 365, hãy xem khi nào tôi có thể nhận được các tính năng mới nhất dành cho Microsoft 365.

Hàm XLOOKUP tìm kiếm một phạm vi hoặc một mảng và trả về một mục tương ứng với khớp đầu tiên nó tìm thấy. Nếu khớp không tồn tại, thì XLOOKUP có thể trả về kết quả khớp gần nhất (xấp xỉ). 

= 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ị tra cứu

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 dải ô cần trả về

[if_not_found]

Tùy chọn

Nếu không tìm thấy kết quả phù hợp hợp lệ, hãy trả về [if_not_found] văn bản mà bạn cung cấp.

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

[match_mode]

Tùy chọn

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

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

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

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

2-một ký tự đại diện trong đó *,?, và ~ có ý nghĩa đặc biệt.

[search_mode]

Tùy chọn

Xác định chế độ tìm kiếm để 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 ở mục cuối cùng.

2-thực hiện tìm kiếm nhị phân dựa trên lookup_array đang được 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 tìm kiếm nhị phân dựa trên lookup_array đang đượ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

Ví dụ này từ video ở trên, và sử dụng XLOOKUP đơn giản để tìm kiếm một tên quốc gia, sau đó trả về mã quốc gia điện thoại của nó. Nó chỉ bao gồm lookup_value (ô F2), lookup_array (phạm vi B2: B11) và return_array (phạm vi D2: D11) đối số. Tính năng này không bao gồm các đối số match_mode, như mặc định XLOOKUP cho kết quả khớp chính xác.

Ví dụ về hàm XLOOKUP được sử dụng để trả về một tên nhân viên và bộ phận dựa trên ID nhân viên. Công thức là = XLOOKUP (B2, B5: B14, C5: C14).

Lưu ý: XLOOKUP khác với hàm VLOOKUP trong đó nó dùng các mảng tra cứu và trả về riêng biệt, trong đó VLOOKUP sử dụng một mảng bảng đơn, theo sau là một số chỉ số 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

Trong ví dụ này, chúng tôi đang tìm kiếm thông tin nhân viên dựa trên số ID nhân viên. Không giống như VLOOKUP, XLOOKUP có thể trả về một mảng với nhiều mục, cho phép một công thức đơn để trả về cả tên nhân viên và bộ phận từ các ô C5: D14.

Ví dụ về hàm XLOOKUP được sử dụng để trả về một tên nhân viên và bộ phận dựa trên IDt của nhân viên. Công thức là: = XLOOKUP (B2, B5: B14, C5: D14, 0, 1)

Ví dụ 3

Ví dụ này sẽ thêm đối số if_not_found vào ví dụ ở trên.

Ví dụ về hàm XLOOKUP được sử dụng để trả về một tên nhân viên và bộ phận dựa trên ID nhân viên với đối số if_not_found. 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

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

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

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

Ví dụ 5

Tiếp theo, chúng tôi sẽ sử dụng hàm XLOOKUP lồng nhau để thực hiện cả khớp dọc và ngang. Trong trường hợp này, trước tiên, nó sẽ tìm kiếm lợi nhuận gộp trong cột B, sau đó tìm Qtr1 ở hàng trên cùng của bảng (phạm vi C5: F5) và trả về giá trị tại giao điểm của hai. Điều này tương tự như việc sử dụng các hàm indexmatch kết hợp . Bạn cũng có thể sử dụng XLOOKUP để thay thế hàm Hlookup .

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

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

Ví dụ này sử dụng hàm Sumvà hai hàm xlookup được lồng với nhau để tính tổng tất cả các giá trị giữa hai phạm vi. Trong trường hợp này, chúng tôi muốn tính tổng các giá trị cho nho, chuối và bao gồm lê, nằm giữa hai.

Sử dụng XLOOKUP với tính tổng cho một phạm vi giá trị nằm giữa hai lựa chọn

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

Tính năng này hoạt động như thế nào? XLOOKUP trả về một phạm vi, vì vậy khi tính năng này tính toán, công thức sẽ kết thúc việc trông như thế này: = SUM ($E $7: $E $9). Bạn có thể thấy cách làm việc này trên riêng của bạn bằng cách chọn một ô có công thức XLOOKUP tương tự như phần này, rồi đi đến công thức > kiểm nghiệm công thức > đánh giá công thứcvà nhấn nút đánh giá để bước qua tính toán.

Lưu ý: Nhờ Microsoft Excel MVP, Bill Jelen, cho thấy ví dụ này.

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

Bạn luôn có thể yêu cầu một chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm hỗ trợ trong Cộng đồng Giải đáp hay đề xuất tính năng hoặc cải tiến mới ở Excel User Voice.

Xem thêm

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)

Lưu ý:  Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Cho chúng tôi biết thông tin này có hữu ích hay không? Dưới đây là bài viết bằng tiếng Anh để bạn tham khảo..​

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×