Microsoft Excel là công cụ không thể thiếu trong xử lý dữ liệu, và hàm XLOOKUP chính là một trong những tính năng mạnh mẽ nhất, giúp người dùng linh hoạt hơn trong việc tra cứu thông tin. Mặc dù được biết đến nhiều nhất với khả năng truy xuất giá trị từ một hàng hoặc cột cụ thể, XLOOKUP còn có thể thực hiện dò tìm hai chiều (two-way lookup) — trả về giá trị tại giao điểm của một hàng và một cột được chỉ định. Đây là một bước tiến vượt trội so với các hàm dò tìm truyền thống như VLOOKUP hay HLOOKUP, mang lại hiệu quả cao hơn trong quản lý và phân tích dữ liệu phức tạp. Để khai thác tối đa sức mạnh của XLOOKUP, bạn cần sử dụng phiên bản Excel 2021 trở lên hoặc Excel cho Microsoft 365 trên máy tính (PC hoặc Mac), đồng thời hàm này cũng có sẵn trên Excel cho web và các ứng dụng di động/máy tính bảng.
XLOOKUP Là Gì Và Tại Sao Bạn Nên Dùng Nó Thay VLOOKUP/HLOOKUP?
XLOOKUP là hàm dò tìm dữ liệu hiện đại, được Microsoft giới thiệu nhằm thay thế và cải thiện các hàm VLOOKUP và HLOOKUP cũ kỹ. Với XLOOKUP, bạn không còn phải lo lắng về việc sắp xếp cột tra cứu ở bên trái hoặc phải, hay giới hạn dò tìm theo hàng/cột cố định. Nó cung cấp sự linh hoạt vượt trội, cho phép dò tìm cả theo chiều dọc và chiều ngang, với nhiều tùy chọn khớp và tìm kiếm nâng cao.
Những lợi ích chính của XLOOKUP bao gồm:
- Linh hoạt hơn: Không giới hạn hướng dò tìm (từ trái sang phải, từ phải sang trái, từ trên xuống dưới, từ dưới lên trên).
- Đơn giản hơn: Cú pháp trực quan, dễ hiểu hơn VLOOKUP/HLOOKUP, đặc biệt khi xử lý các trường hợp lỗi hoặc cần tìm kiếm gần đúng.
- Xử lý lỗi tốt hơn: Tích hợp đối số xử lý lỗi ngay trong hàm.
- Hỗ trợ khớp chính xác và gần đúng: Nhiều chế độ khớp cho các nhu cầu tìm kiếm khác nhau.
Để sử dụng hàm này, bạn cần đảm bảo mình đang dùng Excel 2021 hoặc Excel cho Microsoft 365.
Cách Sử Dụng XLOOKUP Cho Dò Tìm Một Chiều Trong Excel
Để hiểu cách XLOOKUP có thể được sử dụng cho dò tìm hai chiều, trước tiên bạn cần nắm vững cách hàm này hoạt động trong các kịch bản một chiều.
Hàm XLOOKUP có sáu đối số:
=XLOOKUP(a,b,c,d,e,f)Trong đó:
a(bắt buộc): Giá trị cần dò tìm.b(bắt buộc): Mảng (phạm vi) mà XLOOKUP sẽ tìm giá trịa.c(bắt buộc): Mảng (phạm vi) chứa giá trị cần trả về khi tìm thấya.d(tùy chọn): Văn bản hoặc giá trị trả về nếu giá trị dò tìm (a) không được tìm thấy trong mảng dò tìm (b). Nếu bỏ qua, Excel sẽ trả về 0 nếu không tìm thấy.e(tùy chọn): Chế độ khớp.0: Khớp chính xác (mặc định).-1: Khớp chính xác hoặc mục nhỏ hơn kế tiếp.1: Khớp chính xác hoặc mục lớn hơn kế tiếp.2: Khớp ký tự đại diện (wildcard match).
f(tùy chọn): Chế độ tìm kiếm.1: Tìm từ trên xuống dưới hoặc từ trái sang phải (mặc định).-1: Tìm từ dưới lên trên hoặc từ phải sang trái.2: Tìm kiếm nhị phân với mảng dò tìm (b) được sắp xếp theo thứ tự tăng dần.-2: Tìm kiếm nhị phân với mảng dò tìm (b) được sắp xếp theo thứ tự giảm dần.
Nếu đối số d bị bỏ qua và giá trị dò tìm không được tìm thấy, Excel sẽ trả về lỗi #N/A. Nếu đối số e và f bị bỏ qua, tùy chọn mặc định sẽ được áp dụng (khớp chính xác cho e và tìm kiếm từ trên xuống dưới hoặc từ trái sang phải cho f).
Ví dụ, nếu bạn nhập công thức sau vào ô G2:
=XLOOKUP(F2,A2:A100,D2:D100,"Mã ID không hợp lệ",0,1)Công thức này sẽ lấy ID trong ô F2 (a), tìm kiếm trong các ô A2 đến A100 (b), và trả về giá trị tương ứng từ các ô D2 đến D100 (c). Nếu ID trong ô F2 không có trong danh sách, công thức sẽ trả về “Mã ID không hợp lệ” (d). Đối số thứ năm (e) yêu cầu Excel chỉ trả về các kết quả khớp chính xác, và đối số thứ sáu (f) đảm bảo việc tìm kiếm chạy từ đầu đến cuối phạm vi.
Nâng Cao Hiệu Quả Với Dò Tìm Hai Chiều Bằng XLOOKUP Lồng Ghép
Mặc dù dò tìm một chiều rất hữu ích trong nhiều trường hợp, nhưng nó không cho phép bạn thay đổi biến mà bạn đang tra cứu. Ví dụ, công thức trên chỉ trả về điểm số dựa trên ID đã cho, nhưng nếu bạn muốn xem tuổi (Age) hoặc giới tính (Gender) thì sao? Đây là lúc dò tìm hai chiều (còn gọi là dò tìm hai chiều hoặc dò tìm ma trận) phát huy tác dụng.
Để thực hiện điều này, bạn cần lồng một hàm XLOOKUP vào bên trong hàm XLOOKUP khác:
=XLOOKUP(a¹,b¹,XLOOKUP(a²,b²,c))Trong đó:
a¹vàb¹: Là giá trị dò tìm và mảng cho biến đầu tiên (ví dụ: ID).a²vàb²: Là giá trị dò tìm và mảng cho biến thứ hai (ví dụ: tiêu đề cột như “Age”, “Score”, “Gender”).c: Là mảng chứa các giá trị cần trả về.
Bạn cũng có thể thêm các đối số d, e, và f cho mỗi hàm XLOOKUP theo cách tương tự như khi thực hiện dò tìm một chiều. Tuy nhiên, để giữ cho ví dụ đơn giản, chúng tôi sẽ bỏ qua các đối số này.
Dựa trên cấu trúc này, khi bạn nhập công thức sau vào ô G2:
=XLOOKUP(F2,A2:A100,XLOOKUP(G1,B1:D1,B2:D100))
Công thức XLOOKUP dò tìm hai chiều trả về điểm số 48 trong bảng tính Excel
Excel sẽ hoạt động như sau: đầu tiên, nó tìm kiếm ID đã chỉ định (a¹ – ô F2) trong các ô A2 đến A100 (b¹). Tiếp theo, hàm XLOOKUP lồng ghép sẽ tìm kiếm biến đã chỉ định (a² – ô G1, ví dụ: “Score”) trong các ô B1 đến D1 (b²). Cuối cùng, nó trả về kết quả tại giao điểm của hai lần dò tìm này trong phạm vi B2 đến D100 (c).
Kết quả là, nếu bạn thay đổi biến trong ô G1 thành “Age,” bạn sẽ nhận được kết quả là 41. Tương tự, nếu bạn thay đổi thành “Gender,” bạn sẽ nhận được kết quả là M.
Công thức XLOOKUP lồng ghép trong Excel trả về giới tính M dựa trên ID cụ thể
Trong các công thức XLOOKUP hai chiều, thứ tự của các hàm XLOOKUP không quan trọng. Trong ví dụ này, dò tìm ID được đặt trước và dò tìm tiêu đề cột được lồng vào. Nếu chúng được hoán đổi vị trí, bạn vẫn sẽ nhận được cùng một kết quả.
Mẹo Tăng Tốc Dò Tìm: Thêm Danh Sách Thả Xuống (Drop-Down List) Cho Ô Tra Cứu
Sau khi hoàn thành việc dò tìm hai chiều, bạn có thể tiến thêm một bước bằng cách thêm danh sách thả xuống (drop-down list) vào các ô chứa giá trị dò tìm để tăng tốc quá trình truy xuất dữ liệu.
Để thực hiện điều này, hãy làm theo các bước sau:
Bước 1: Thêm Drop-Down List cho Tiêu Đề Cột (Biến)
- Chọn một trong các ô tra cứu (trong trường hợp này là ô G1).
- Trên thanh ribbon, chuyển đến tab Data, sau đó nhấp vào Data Validation.
- Trong hộp thoại Data Validation, tại trường “Allow”, chọn List.
- Kích hoạt trường “Source”, sau đó chọn các ô chứa giá trị bạn muốn xuất hiện trong danh sách thả xuống. Trong trường hợp này, đó là các tiêu đề cột trong các ô B1 đến D1.
Cài đặt Data Validation List trong Excel, chọn các tiêu đề cột B1 đến D1 làm nguồn
Khi bạn nhấp vào “OK”, bạn sẽ thấy một nút thả xuống trong ô G1, khi nhấp vào sẽ hiển thị các tùy chọn biến.
Bước 2: Thêm Drop-Down List cho ID (Giá Trị Dò Tìm Hàng)
Bây giờ, hãy lặp lại quy trình cho ô F2, ô tra cứu còn lại.
Cài đặt Data Validation List trong Excel, chọn dải ô A2 đến A1000 cho cột ID làm nguồn
Lưu ý rằng trong trường “Source”, chúng tôi đã chỉ định một phạm vi mở rộng vượt quá kích thước hiện tại của tập dữ liệu (ví dụ: từ A2 đến A1000) để cho phép dữ liệu phát triển trong tương lai. Dấu chấm (.) sau dấu hai chấm (:) (còn gọi là toán tử trim ref trong ngữ cảnh này) cho Excel biết để cắt bỏ bất kỳ ô trống nào ở cuối phạm vi nguồn.
Ngoài ra, bạn có thể sử dụng một công thức đếm số ô chứa giá trị trong cột A và trả về số tùy chọn đó trong menu thả xuống.
Bây giờ, bạn có thể dễ dàng chọn một ID từ danh sách thả xuống trong ô F2 và biến mong muốn từ ô G1 để thực hiện dò tìm hai chiều một cách nhanh chóng.
Các Phương Pháp Thay Thế Cho Dò Tìm Hai Chiều Trong Excel (INDEX & MATCH)
Nếu bạn đang sử dụng Excel 2019 hoặc các phiên bản cũ hơn (trước khi XLOOKUP được giới thiệu), bạn vẫn có thể thực hiện dò tìm hai chiều bằng cách sử dụng kết hợp các hàm INDEX và MATCH. Đây là một lựa chọn thay thế mạnh mẽ và vẫn rất phổ biến trong cộng đồng người dùng Excel.
Kết Luận
Hàm XLOOKUP trong Excel là một công cụ đột phá, giúp việc dò tìm dữ liệu trở nên linh hoạt và mạnh mẽ hơn bao giờ hết, đặc biệt với khả năng dò tìm hai chiều hiệu quả. Việc lồng ghép các hàm XLOOKUP cho phép bạn truy xuất chính xác dữ liệu tại giao điểm của một hàng và một cột cụ thể, mở ra nhiều khả năng mới trong phân tích và quản lý bảng tính. Kết hợp với việc sử dụng danh sách thả xuống, quy trình dò tìm dữ liệu của bạn sẽ được tối ưu hóa đáng kể, tiết kiệm thời gian và giảm thiểu sai sót. Hãy ứng dụng XLOOKUP vào công việc hàng ngày để nâng cao năng suất và làm chủ dữ liệu của bạn. Khám phá thêm nhiều thủ thuật và công nghệ hữu ích khác trên trithuccongnghe.net để không ngừng phát triển kỹ năng của mình!