Cách sử dụng hàm XLOOKUP trong Google Sheets chi tiết từ A - Z

Khi làm việc với dữ liệu trên Google Sheets, tra cứu thông tin nhanh và chính xác là yêu cầu gần như bắt buộc. Thay vì phụ thuộc vào VLOOKUP hay HLOOKUP với nhiều hạn chế, hàm XLOOKUP trong Google Sheets mang đến cách tra cứu linh hoạt hơn, ít lỗi hơn. Bài viết này sẽ giúp bạn hiểu và áp dụng XLOOKUP hiệu quả qua ví dụ thực tế.

Cách sử dụng hàm XLOOKUP trong Google Sheets chi tiết

Hàm XLOOKUP trong Google Sheets là hàm dùng để tìm kiếm một giá trị trong bảng dữ liệu và trả về kết quả tương ứng từ một vùng khác. Bạn chỉ cần xác định giá trị cần tìm, vùng để tìm, và vùng chứa kết quả, không cần quan tâm dữ liệu nằm bên trái, bên phải, phía trên hay phía dưới.

So với các hàm tra cứu cũ như VLOOKUP hay HLOOKUP, XLOOKUP dễ dùng hơn, linh hoạt hơn và ít lỗi hơn. Hàm không yêu cầu dữ liệu phải sắp xếp, không cần đếm cột, và vẫn hoạt động chính xác ngay cả khi bạn thêm hoặc xóa cột trong bảng. Nhờ đó, XLOOKUP được xem là lựa chọn hiện đại và hiệu quả nhất khi làm việc với dữ liệu trong Google Sheets.

Hàm XLOOKUP trong Google Sheets có cú pháp như sau:

=XLOOKUP(lookup_value, lookup_range, result_range, [if_not_found], [match_mode], [search_mode])

Trong đó:

• lookup_value (Giá trị cần tìm): Là giá trị bạn muốn tra cứu trong bảng dữ liệu. Có thể là một ô (A2, B5…), một giá trị nhập trực tiếp (ví dụ: “SP001”, “Nguyễn Văn A”) hoặc kết quả của một hàm khác.
• lookup_range (Vùng dò tìm): Là vùng dữ liệu chứa giá trị cần tìm, thường là một cột hoặc một hàng trong bảng.
• result_range (Vùng trả về kết quả): Là vùng dữ liệu chứa kết quả tương ứng với giá trị cần tìm.
• if_not_found (Giá trị trả về khi không tìm thấy – tùy chọn): Cho phép bạn hiển thị nội dung tùy ý thay vì lỗi #N/A, ví dụ: “Không tìm thấy”, “N/A” hoặc để trống.
• match_mode (Chế độ khớp dữ liệu – tùy chọn): Xác định cách XLOOKUP so khớp dữ liệu, như khớp chính xác, khớp gần đúng hoặc sử dụng ký tự đại diện.
• search_mode (Chế độ tìm kiếm – tùy chọn): Quy định hướng tìm kiếm dữ liệu, từ trên xuống dưới hoặc từ dưới lên trên.

Lưu ý: Vùng lookup_range và result_range phải có cùng số hàng (hoặc số cột). Nếu không, Google Sheets sẽ trả về lỗi #VALUE!.

Cú pháp hàm XLOOKUP trong Google Sheets

Một cách dùng phổ biến nhất của hàm XLOOKUP là tra cứu theo cột để thay thế hoàn toàn hàm VLOOKUP. Với XLOOKUP, bạn không còn phải lo cột tra cứu nằm bên trái hay bên phải cột kết quả - công thức vẫn hoạt động chính xác.

Ví dụ: Bạn nhập Mã sản phẩm và cần lấy ra Tên sản phẩm tương ứng.

• Ô G2: Nhập mã sản phẩm (ví dụ: SP03)
• Ô H2: Hiển thị tên sản phẩm

Công thức sử dụng: =XLOOKUP(G2; A2:A6; B2:B6; "Không tìm thấy")

Ý nghĩa công thức:

• G2: mã sản phẩm cần tìm
• A2:A6: cột chứa mã sản phẩm
• B2:B6: cột chứa tên sản phẩm cần trả về
• "Không tìm thấy": nội dung hiển thị nếu không có mã phù hợp

Kết quả: Khi nhập SP03 ở ô G2, Google Sheets sẽ trả về “Laptop Dell” ở ô H2.

Ví dụ hàm XLOOKUP trong Google Sheets tra cứu theo cột, thay thế hàm VLOOKUP

Ngoài việc tra cứu theo cột, hàm XLOOKUP còn cho phép tra cứu theo hàng, nhờ đó bạn có thể thay thế hoàn toàn hàm HLOOKUP trước đây. Cách này đặc biệt hữu ích với các bảng báo cáo, bảng KPI hoặc dữ liệu trình bày theo dạng ngang.

Ví dụ 2: Bạn nhập Tên sản phẩm và cần lấy ra Giá bán tương ứng.

• Ô G2: Nhập tên sản phẩm (ví dụ: Tai nghe Sony)
• Ô H2: Hiển thị giá bán

Công thức sử dụng: =XLOOKUP(G2;B2:B6;D2:D6)

Giải thích công thức:

• G2: tên sản phẩm cần tìm
• B2:B6: vùng chứa tên sản phẩm
• D2:D6: vùng chứa giá bán cần trả về

Kết quả: Khi nhập Tai nghe Sony, Google Sheets sẽ trả về 1500000 ở ô H2.

Hàm XLOOKUP trong Google Sheets tra cứu theo hàng, áp dụng cho bảng dữ liệu dạng ngang

Một hạn chế lớn của hàm VLOOKUP là không thể tra cứu từ phải sang trái. Tuy nhiên, XLOOKUP khắc phục hoàn toàn nhược điểm này, giúp bạn tra cứu dữ liệu linh hoạt hơn rất nhiều.

Ví dụ: Bạn dựa vào Danh mục sản phẩm để tìm Mã sản phẩm tương ứng.

• Ô G2: Nhập danh mục (ví dụ: Phụ kiện)
• Ô H2: Hiển thị mã sản phẩm

Công thức sử dụng: =XLOOKUP(G2; C2:C7; A2:A7)

Giải thích công thức:

• G2: Danh mục cần tìm
• C2:C7: Cột chứa danh mục sản phẩm (nằm bên phải)
• A2:A7: Cột chứa mã sản phẩm cần trả về (nằm bên trái)

Kết quả: Khi nhập Phụ kiện, Google Sheets sẽ trả về SP01 - mã sản phẩm đầu tiên thuộc danh mục này.

Cách dùng hàm XLOOKUP trong Google Sheets để tra cứu dữ liệu từ phải sang trái

Thêm một ưu điểm nữa của hàm XLOOKUP trong Google Sheets là khả năng trả về nhiều cột kết quả chỉ với một công thức duy nhất. Điều này giúp bạn không cần kéo công thức sang ngang như trước, bảng tính gọn gàng hơn và xử lý nhanh hơn.

Ví dụ: Bạn nhập Mã sản phẩm và muốn lấy cùng lúc Tên sản phẩm, Giá bán và Tồn kho.

• Ô G2: Nhập mã sản phẩm (ví dụ: SP01)
• Ô H2, I2, J2: Hiển thị kết quả

Công thức sử dụng: =XLOOKUP(G2; A2:A7; B2:E7)

Giải thích công thức:

• G2: Mã sản phẩm cần tìm
• A2:A7: Cột chứa mã sản phẩm
• B2:E7: Vùng kết quả gồm nhiều cột (Tên sản phẩm, Danh mục, Giá, Tồn kho)

Kết quả: Khi nhập SP01, Google Sheets sẽ tự động trả về Chuột Logitech – Phụ kiện – 250000 – 120 và hiển thị tràn sang các ô bên phải.

Hàm XLOOKUP trả về nhiều cột kết quả cùng lúc trong Google Sheets

Mặc dù hàm XLOOKUP rất dễ dùng và linh hoạt, nhưng để áp dụng hiệu quả và hạn chế lỗi trong thực tế, bạn cần ghi nhớ một số điểm quan trọng sau:

• Đảm bảo vùng dò tìm và vùng trả về có cùng kích thước: Vùng lookup_range và result_range phải có cùng số hàng (hoặc số cột). Nếu không khớp, Google Sheets sẽ báo lỗi #VALUE!.
• Kiểm tra kỹ giá trị cần tìm: Nhiều lỗi #N/A không phải do công thức sai mà do dữ liệu nhập vào chưa đúng, như sai chính tả, thừa khoảng trắng hoặc sai kiểu dữ liệu. Hãy đảm bảo giá trị cần tìm khớp hoàn toàn với dữ liệu trong bảng.
• Luôn sử dụng tham số if_not_found: Để bảng tính trông chuyên nghiệp hơn và tránh hiển thị lỗi kỹ thuật, bạn nên thêm tham số if_not_found. Nhờ đó, khi không tìm thấy dữ liệu, bảng sẽ hiển thị nội dung dễ hiểu như “Không tìm thấy” thay vì lỗi #N/A.

Lỗi #N/A xảy ra khi hàm XLOOKUP không tìm thấy giá trị phù hợp trong vùng dò tìm. Đây là lỗi thường gặp nhất khi sử dụng XLOOKUP trong Google Sheets, đặc biệt với bảng dữ liệu lớn.

Nguyên nhân phổ biến:

• Giá trị cần tìm không tồn tại trong bảng (ví dụ: nhập SP99 nhưng bảng chỉ có SP01–SP05).
• Sai kiểu dữ liệu (text và number khác nhau, ví dụ 001 và 1) hoặc có khoảng trắng thừa.
• Sai chính tả hoặc thiếu/thừa ký tự.

Cách khắc phục:

• Kiểm tra lại xem giá trị nhập vào có tồn tại trong bảng hay không.
• Đảm bảo dữ liệu được nhập đồng nhất (đúng kiểu, không thừa khoảng trắng).
• Thêm tham số if_not_found để hiển thị thông báo dễ hiểu thay vì lỗi #N/A.

Lỗi #N/A khi sử dụng hàm XLOOKUP trong Google Sheets và cách nhận biết

Trong quá trình sử dụng hàm XLOOKUP, bạn có thể gặp lỗi #VALUE! khi Google Sheets không thể xác định cách trả kết quả do các vùng dữ liệu được chọn không tương thích với nhau.

Nguyên nhân thường gặp:

• Vùng dò tìm và vùng trả về không cùng kích thước, dẫn đến việc XLOOKUP không biết lấy dữ liệu ở dòng hoặc cột nào.
• XLOOKUP được dùng để trả về nhiều cột, nhưng các ô bên phải đã có dữ liệu, khiến kết quả không thể hiển thị.
• Cấu trúc dữ liệu không đồng nhất, một vùng theo cột dọc trong khi vùng còn lại lại theo hàng ngang.

Cách khắc phục:

• Đảm bảo lookup_range và result_range có cùng số hàng hoặc số cột.
• Khi trả về mảng, hãy chắc chắn các ô nhận kết quả đang trống.
• Kiểm tra để hai vùng dữ liệu cùng hướng (đều là hàng hoặc đều là cột).

Lỗi #VALUE! trong hàm XLOOKUP do vùng dữ liệu không tương thích

Trong thực tế, việc nắm vững hàm XLOOKUP trong Google Sheets sẽ giúp bạn xử lý dữ liệu linh hoạt hơn, đặc biệt khi bảng tính thường xuyên thay đổi. Chỉ cần áp dụng đúng cú pháp và lưu ý các lỗi phổ biến, XLOOKUP có thể trở thành công cụ tra cứu chính trong hầu hết công việc hằng ngày của bạn.

Nếu bạn đang tìm máy tính bảng và phụ kiện chính hãng để học tập, làm việc hay giải trí, hãy tham khảo sản phẩm tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn. Nhiều mẫu mã, giá tốt và chính sách bảo hành rõ ràng giúp bạn yên tâm lựa chọn.