Hướng Dẫn Sử Dụng Hàm XLOOKUP Hai Chiều Trong Excel Để Tra Cứu Dữ Liệu Hiệu Quả

Hàm XLOOKUP của Microsoft Excel nổi tiếng với khả năng truy xuất giá trị từ một hàng hoặc cột cụ thể trong tập dữ liệu. Tuy nhiên, công cụ mạnh mẽ này còn có thể được ứng dụng để thực hiện tra cứu hai chiều (hay còn gọi là tra cứu hai chiều hoặc tra cứu ma trận), trả về giá trị tại giao điểm của một hàng và cột được chỉ định. Đây là một tính năng đột phá, giúp người dùng linh hoạt hơn rất nhiều trong việc phân tích và quản lý dữ liệu so với các hàm tra cứu truyền thống.

Để có thể truy cập và sử dụng hàm XLOOKUP, bạn cần đang sử dụng ứng dụng Excel trên máy tính (PC hoặc Mac) phiên bản Excel 2021 trở lên, hoặc là một phần của gói đăng ký Microsoft 365. Hàm này cũng có sẵn và hoạt động trơn tru trên các phiên bản Excel dành cho web, cũng như các ứng dụng Excel trên máy tính bảng và thiết bị di động. Việc hiểu rõ cách khai thác toàn bộ tiềm năng của XLOOKUP, đặc biệt là khả năng tra cứu hai chiều, sẽ nâng cao đáng kể kỹ năng xử lý bảng tính của bạn.

Hộp sản phẩm Microsoft 365 Personal bao gồm các ứng dụng Office như Excel

Tra Cứu Một Chiều Với Hàm XLOOKUP Trong Excel

Để nắm bắt được cách XLOOKUP có thể thực hiện tra cứu hai chiều, trước tiên bạn cần hiểu rõ cách thức hoạt động của hàm này trong các tình huống một chiều cơ bản. XLOOKUP là một sự thay thế mạnh mẽ cho VLOOKUP và HLOOKUP, mang lại nhiều tính năng cải tiến và linh hoạt hơn.

Hàm XLOOKUP có sáu đối số:

=XLOOKUP(giá_trị_tìm_kiếm, mảng_tìm_kiếm, mảng_trả_về, [giá_trị_nếu_không_tìm_thấy], [chế_độ_khớp], [chế_độ_tìm_kiếm])

Trong đó:

• giá_trị_tìm_kiếm (bắt buộc): Là giá trị mà bạn muốn tìm.
• mảng_tìm_kiếm (bắt buộc): Là phạm vi các ô mà bạn muốn tìm giá_trị_tìm_kiếm.
• mảng_trả_về (bắt buộc): Là phạm vi các ô chứa giá trị bạn muốn trả về. Phạm vi này phải có cùng kích thước với mảng_tìm_kiếm.
• [giá_trị_nếu_không_tìm_thấy] (tùy chọn): Là văn bản hoặc giá trị để trả về nếu giá_trị_tìm_kiếm không được tìm thấy trong mảng_tìm_kiếm. Nếu đối số này bị bỏ qua, Excel sẽ trả về lỗi #N/A.
• [chế_độ_khớp] (tùy chọn): Xác định loại khớp bạn muốn.
  • 0: Khớp chính xác (mặc định).
  • -1: Khớp chính xác hoặc mục nhỏ hơn tiếp theo.
  • 1: Khớp chính xác hoặc mục lớn hơn tiếp theo.
  • 2: Khớp ký tự đại diện (ví dụ: *, ?).
• [chế_độ_tìm_kiếm] (tùy chọn): Chỉ định cách tìm kiếm được thực hiện.
  • 1: Tìm kiếm từ trên xuống dưới hoặc từ trái sang phải (mặc định).
  • -1: Tìm kiế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_tìm_kiếm theo thứ tự tăng dần.
  • -2: Tìm kiếm nhị phân với mảng_tìm_kiếm theo thứ tự giảm dần.

Nếu đối số [giá_trị_nếu_không_tìm_thấy] bị bỏ qua và giá trị tìm kiếm không được tìm thấy, Excel sẽ trả về lỗi #N/A. Nếu các đối số [chế_độ_khớp] và [chế_độ_tìm_kiếm] bị bỏ qua, tùy chọn mặc định sẽ được áp dụng (khớp chính xác cho [chế_độ_khớp] và tìm kiếm từ trên xuống dưới hoặc từ trái sang phải cho [chế_độ_tìm_kiếm]).

Ví dụ, khi nhập công thức sau vào ô G2:

=XLOOKUP(F2,A2:A100,D2:D100,"ID không hợp lệ",0,1)

Công thức này sẽ trả về điểm số là 48.

Ví dụ minh họa hàm XLOOKUP một chiều trong Excel, tìm điểm số dựa trên ID đã cho

Công thức XLOOKUP này lấy ID trong ô F2 (giá_trị_tìm_kiếm), tìm kiếm nó trong các ô từ A2 đến A100 (mảng_tìm_kiếm), và trả về giá trị tương ứng trong các ô từ D2 đến D100 (mảng_trả_về). Nếu ID trong ô F2 không có trong danh sách từ A2 đến A100, hàm sẽ trả về chuỗi “ID không hợp lệ” (giá_trị_nếu_không_tìm_thấy). Đối số thứ năm yêu cầu Excel chỉ trả về các kết quả khớp chính xác (chế_độ_khớp là 0), và đối số thứ sáu đảm bảo tìm kiếm được thực hiện từ đầu đến cuối phạm vi (chế_độ_tìm_kiếm là 1).

Ứng Dụng XLOOKUP Cho Tra Cứu Hai Chiều Trong Excel

Trong khi tra cứu một chiều hữu ích trong nhiều tình huống, chúng không cho phép bạn thay đổi biến mà bạn đang tìm kiếm. Ví dụ, công thức trên chỉ trả về điểm số dựa trên ID được chỉ định, nhưng nếu bạn muốn xem tuổi hoặc giới tính thì sao? Đây chính là lúc tra cứu hai chiều (còn được gọi là tra cứu hai chiều hoặc tra cứu ma trận) phát huy tác dụng. Đây là một kỹ thuật mạnh mẽ giúp nâng tầm khả năng phân tích dữ liệu của bạn.

Để thực hiện tra cứu hai chiều với XLOOKUP, bạn cần lồng một hàm XLOOKUP vào trong hàm XLOOKUP khác:

=XLOOKUP(giá_trị_tìm_kiếm¹, mảng_tìm_kiếm¹, XLOOKUP(giá_trị_tìm_kiếm², mảng_tìm_kiếm², mảng_trả_về))

Trong đó:

• giá_trị_tìm_kiếm¹ và mảng_tìm_kiếm¹ là giá trị và mảng tìm kiếm cho biến thứ nhất (ví dụ: ID).
• giá_trị_tìm_kiếm² và mảng_tìm_kiếm² là giá trị và mảng tìm kiếm cho biến thứ hai (ví dụ: tên cột như “Score”, “Age”, “Gender”).
• mảng_trả_về là toàn bộ phạm vi dữ liệu mà bạn muốn tìm kiếm kết quả.

Bạn cũng có thể thêm các đối số tùy chọn [giá_trị_nếu_không_tìm_thấy], [chế_độ_khớp], và [chế_độ_tìm_kiếm] cho mỗi hàm XLOOKUP theo cách tương tự như khi thực hiện tra cứu một chiều. Tuy nhiên, để giữ cho ví dụ đơn giản, chúng tôi đã bỏ qua các đối số này trong cú pháp và các ví dụ dưới đây.

Dựa trên cấu trúc này, khi nhập công thức sau vào ô G2:

=XLOOKUP(F2,A2:A100,XLOOKUP(G1,B1:D1,B2:D100))

Công thức này sẽ trả về điểm số là 48.

Minh họa công thức XLOOKUP hai chiều trong Excel trả về điểm số dựa trên ID và tiêu đề cột "Score"

Điều này xảy ra vì Excel trước tiên tìm kiếm ID được chỉ định (giá_trị_tìm_kiếm¹ từ ô F2) trong các ô từ A2 đến A100 (mảng_tìm_kiếm¹). Sau đó, nó tìm kiếm biến được chỉ định (giá_trị_tìm_kiếm² từ ô G1, ví dụ “Score”) trong các ô từ B1 đến D1 (mảng_tìm_kiếm² – tức là các tiêu đề cột). Cuối cùng, hàm trả về kết quả tại giao điểm của hai tra cứu này trong phạm vi dữ liệu từ B2 đến D100 (mảng_trả_về).

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.

Công thức XLOOKUP hai chiều trong Excel hiển thị tuổi dựa trên ID và tiêu đề cột "Age"

Tương tự, nếu bạn thay đổi nó thành “Gender”, bạn sẽ nhận được kết quả là M (Male).

Kết quả tra cứu XLOOKUP hai chiều trong Excel trả về giới tính dựa trên ID và tiêu đề cột "Gender"

Trong các công thức XLOOKUP hai chiều, thứ tự lồng ghép các hàm XLOOKUP không quan trọng. Trong ví dụ này, tra cứu ID được đặt trước trong công thức, và tra cứu 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ả. Điều này thể hiện sự linh hoạt của XLOOKUP khi xử lý các truy vấn phức tạp.

Mẹo Chuyên Nghiệp: Thêm Danh Sách Thả Xuống Cho Ô Tra Cứu

Bây giờ khi bạn đã hoàn thành việc thiết lập tra cứu hai chiều, bạn có thể đi xa hơn một bước bằng cách thêm danh sách thả xuống vào các ô chứa giá trị tìm kiếm để tăng tốc quá trình truy xuất dữ liệu và giảm thiểu lỗi nhập liệu. Đây là một mẹo nhỏ nhưng cực kỳ hữu ích để cải thiện trải nghiệm người dùng và tính chuyên nghiệp của bảng tính Excel.

Để thực hiện điều này, bạn hãy chọn một trong các ô tra cứu (trong trường hợp này, là ô G1), sau đó trên tab Data trên thanh Ribbon, nhấp vào “Data Validation” (Xác thực dữ liệu).

Lựa chọn ô G1 và tính năng Data Validation trên tab Data của Excel để tạo danh sách thả xuống

Tiếp theo, trong hộp thoại Data Validation, tại trường Allow, chọn “List” (Danh sách). Cuối cùng, kích hoạt trường “Source” (Nguồn), và chọn các ô chứa các giá trị mà bạn muốn xuất hiện trong danh sách thả xuống. Trong ví dụ này, đó là các tiêu đề cột trong các ô từ B1 đến D1.

Cửa sổ Data Validation trong Excel với tùy chọn "List" được chọn và nguồn dữ liệu là các ô B1 đến D1

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, nút này sẽ hiển thị các tùy chọn biến (như Score, Age, Gender) để bạn lựa chọn.

Ô G1 trong bảng tính Excel hiển thị danh sách thả xuống các lựa chọn biến sau khi áp dụng Data Validation

Bây giờ, hãy lặp lại quy trình này cho ô F2, ô tra cứu còn lại.

Cấu hình Data Validation cho ô F2, chọn "List" và phạm vi nguồn A2 đến A1000 để tạo danh sách ID

Lưu ý rằng, trong trường Source, 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 để cho phép bảng tính phát triển (tập dữ liệu của tôi chứa 100 hàng, nhưng tôi đã mở rộng nguồn đến ô A1000). Dấu chấm (hay còn gọi là toán tử tham chiếu cắt xén trong ngữ cảnh này) sau dấu hai chấm cho Excel biết rằng sẽ cắt bỏ bất kỳ ô trống nào ở cuối phạm vi nguồn.

Ngoài ra, bạn cũng 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ố lượng tùy chọn đó trong menu thả xuống để tạo ra danh sách động hơn.

Giờ đây, bạn có thể dễ dàng chọn một ID từ danh sách thả xuống trong ô F2.

Danh sách thả xuống trong ô F2 của Excel hiển thị các tùy chọn ID có sẵn để lựa chọn

Kết Luận

Hàm XLOOKUP trong Excel đã chứng tỏ là một công cụ mạnh mẽ và linh hoạt hơn rất nhiều so với các hàm tra cứu truyền thống, đặc biệt là với khả năng tra cứu hai chiều. Bằng cách lồng ghép hai hàm XLOOKUP, người dùng có thể dễ dàng truy xuất dữ liệu tại giao điểm của bất kỳ hàng và cột nào, mang lại sự linh hoạt tối đa trong việc phân tích và hiển thị thông tin.

Việc kết hợp tính năng tra cứu hai chiều của XLOOKUP với các danh sách thả xuống thông qua Data Validation không chỉ nâng cao hiệu quả làm việc mà còn cải thiện đáng kể trải nghiệm người dùng, giúp giảm thiểu lỗi và tăng tốc quá trình truy xuất dữ liệu. Mặc dù XLOOKUP chỉ có sẵn trong các phiên bản Excel mới hơn (Excel 2021 trở lên hoặc Microsoft 365), những người dùng các phiên bản cũ hơn vẫn có thể thực hiện tra cứu hai chiều hiệu quả bằng cách sử dụng kết hợp các hàm INDEX và MATCH. Điều này cho thấy Excel luôn cung cấp nhiều giải pháp linh hoạt để đáp ứng các nhu cầu xử lý dữ liệu khác nhau.

Hãy bắt đầu áp dụng XLOOKUP hai chiều vào bảng tính của bạn ngay hôm nay để khám phá những khả năng mới trong việc quản lý và phân tích dữ liệu. Bạn có kinh nghiệm hay mẹo nào khác khi sử dụng XLOOKUP không? Hãy chia sẻ ý kiến của bạn ở phần bình luận bên dưới, hoặc khám phá thêm các bài viết hướng dẫn Excel hữu ích khác trên website của chúng tôi!