Hàm XLOOKUP trong Google Sheets| Hướng dẫn sử dụng từ A-Z

Trong quá trình làm việc với Google Sheets, việc tra cứu dữ liệu là thao tác diễn ra hằng ngày. Đặc biệt với các bảng dữ liệu lớn như danh sách nhân viên, sản phẩm, đơn hàng hay báo cáo doanh thu. Trước đây, người dùng thường phải dựa vào VLOOKUP hoặc HLOOKUP để tìm kiếm dữ liệu. Tuy nhiên, các hàm này tồn tại khá nhiều hạn chế. Đó là lý do hàm XLOOKUP trong Google Sheets ra đời và là sự thay thế tối ưu. Trong bài viết này, GCS Việt Nam sẽ giúp bạn hiểu rõ về cách sử dụng hàm XLOOKUP trong Google Sheets. 

Hàm XLOOKUP trong Google Sheets là gì?

Để sử dụng hiệu quả hàm XLOOKUP trong Google Sheets, việc đầu tiên là bạn cần nắm rõ khái niệm và bản chất của hàm. Với hàm XLOOKUP cũng không ngoại lệ. 

Khái niệm hàm XLOOKUP

Hàm XLOOKUP trong Google Sheets là một hàm dùng để tìm kiếm một giá trị trong một vùng dữ liệu xác định và trả về kết quả tương ứng từ một vùng khác. Điểm đặc biệt của hàm XLOOKUP là nó không bị giới hạn bởi hướng tra cứu, cũng như không yêu cầu dữ liệu phải sắp xếp theo thứ tự. 

Hiểu một cách đơn giản, khi bạn sử dụng hàm XLOOKUP trong Google Sheets, bạn sẽ chỉ cần cung cấp các giá trị như là:

• Giá trị cần tìm (ví dụ: mã nhân viên, mã sản phẩm)
• Vùng dữ liệu để dò tìm giá trị đó
• Vùng dữ liệu chứa kết quả cần trả về

Không giống hàm VLOOKUP, bạn không cần đếm cột, không lo bị sai khi thêm hoặc xóa cột trong bảng dữ liệu. Nhờ đó, công thức hàm trở nên dễ đọc, hạn chế tối đa các lỗi phát sinh. 

Hàm XLOOKUP với VLOOKUP & HLOOKUP có gì khác nhau

Để thấy rõ giá trị của hàm XLOOKUP, bạn có thể so sánh với hai hàm tra cứu quen thuộc là VLOOKUP và HLOOKUP. Dưới đây là những điểm khác biệt, chúng ta có thể thấy rõ khi sử dụng 3 hàm XLOOKUP với VLOOKUP & HLOOKUP. 

Nếu bạn đang làm việc với Google Sheets hoặc Excel phiên bản mới, XLOOKUP là lựa chọn tối ưu để thay thế VLOOKUP và HLOOKUP nhờ tính linh hoạt, dễ bảo trì và hạn chế lỗi.

Lợi ích khi sử dụng hàm XLOOKUP trong Google Sheets 

Việc sử dụng hàm XLOOKUP trong Google Sheets mang lại rất nhiều lợi ích. Đặc biệt với những người thường xuyên làm việc với dữ liệu lớn hoặc báo cáo phức tạp.

• Thứ nhất, hàm XLOOKUP giúp giảm độ phức tạp của công thức. Công thức ngắn gọn, dễ đọc, ngay cả với người mới bắt đầu cũng có thể hiểu và chỉnh sửa sau này. 
• Thứ hai, hàm XLOOKUP giảm thiểu lỗi sai trong quá trình làm việc. Việc không cần đếm cột, không phụ thuộc vào thứ tự dữ liệu giúp bạn hạn chế tối đa lỗi logic. Đây là một vấn đề thường gặp với hàm VLOOKUP.
• Thứ ba, hàm XLOOKUP linh hoạt hơn trong thực tế sử dụng. Bạn có thể tra cứu dữ liệu động, trả về nhiều cột kết quả, kết hợp với các hàm khác để xử lý dữ liệu nâng cao. 
• Thứ tư, hàm XLOOKUP giúp tiết kiệm thời gian. Bạn không còn phải tốn công đếm hàng chục cột trong một bảng dữ liệu khổng lồ. Việc chỉ cần quét vùng chứa giá trị tìm kiếm và vùng kết quả giúp thao tác nhanh hơn gấp nhiều lần. 
• Cuối cùng, hàm XLOOKUP giúp trả về kết quả mảng. Chỉ với một lần nhập công thức, hàm XLOOKUP có thể trả về kết quả của nhiều cột liên tiếp. Điều này giúp bạn không phải kéo công thức sang ngang, làm giảm dung lượng file và tăng tốc độ xử lý cho Google Sheets. 

Cú pháp và các tham số về cách dùng hàm XLOOKUP

Để biết cách dùng hàm XLOOKUP trong Google Sheets, bước tiếp theo, bạn cần phải nắm rõ về cú pháp đầy đủ của hàm 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: Đây là giá trị mà bạn muốn tra cứu trong bảng dữ liệu. Giá trị này có thể là một ô dữ liệu (A2, B5…), một giá trị nhập trực tiếp (“SP001”, “Nguyễn Văn A”) hoặc kết quả của một hàm khác.
  • lookup_range – Vùng dữ liệu để dò tìm: Đây là vùng chứa giá trị cần tìm, thường là một cột hoặc một hàng. 
  • result_range – Vùng trả về kết quả: Đây là vùng chứa dữ liệu mà bạn muốn lấy kết quả tương ứng.
  • if_not_found – Giá trị trả về khi không tìm thấy (tùy chọn): Thay vì để Google Sheets hiển thị lỗi #N/A, bạn có thể chủ động hiển thị “Không tìm thấy dữ liệu”, “N/A”, Hoặc để trống (“”).
  • match_mode – Chế độ khớp dữ liệu (tùy chọn): Tham số này cho phép bạn kiểm soát cách XLOOKUP so khớp dữ liệu chính xác, gần đúng, bằng ký tự đại diện. 

• search_mode – Chế độ tìm kiếm (tùy chọn): Tham số này xác định hướng tìm kiếm từ trên xuống dưới, từ dưới lên trên. 

*Lưu ý: 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ẽ báo lỗi #VALUE!.

Cách dùng hàm XLOOKUP trong Google Sheets qua ví dụ thực tế

Sau khi đã nắm rõ cú pháp và ý nghĩa từng tham số, cách tốt nhất để hiểu sâu và nhớ chính là thực hiện áp dụng vào ví dụ cụ thể. Phần này, chúng tôi sẽ minh họa 05 cách sử dụng hàm XLOOKUP trong Google Sheets phổ biến nhất, giúp bạn hiểu thêm về cách dùng ra sao, cùng tham khảo nhé.

Bảng dữ liệu ví dụ

Tra cứu theo cột (Thay thế VLOOKUP)

Thông thường, cách dùng hàm XLOOKUP trong Google Sheets phổ biến nhất dùng thay thế hoàn toàn cho hàm VLOOKUP. Nếu trước đây bạn phải lo lắng về việc cột tra cứu phải nằm bên trái cột kết quả, thì với XLOOKUP, vấn đề này không còn tồn tại.

*Yêu cầu: Sử dụng bảng dữ liệu trên nhập Mã sản phẩm (SP03) và trả về Tên sản phẩm tương ứng.

Giả sử:

• Ô G2: nhập mã sản phẩm (SP03)
• Ô H2: hiển thị tên sản phẩm

Công thức:

=XLOOKUP(G2; A2:A7; B2:B7; “Không tìm thấy”)

Trong đó:

• G2: giá trị cần tìm (mã sản phẩm)
• A2:A7: cột chứa mã sản phẩm
• B2:B7: cột chứa tên sản phẩm cần trả về
• “Không tìm thấy”: giá trị hiển thị nếu không có mã phù hợp

Không giống VLOOKUP, công thức này không cần chỉ số cột, nên nếu bạn thêm hoặc xóa cột trong bảng, công thức vẫn hoạt động chính xác.

Kết quả: 

Tra cứu theo hàng (Thay thế HLOOKUP)

Ngoài tra cứu theo cột, XLOOKUP còn có thể tra cứu theo hàng ngang. Do đó, bạn có thể sử dụng hàm XLOOKUP thay để cho việc dùng hàm HLOOKUP trước đây. Với các bảng dữ liệu dạng báo cáo theo tháng hoặc chỉ số KPI, đây là cách dùng cực kỳ hiệu quả.

*Yêu cầu: Dựa vào bảng dữ liệu trên với Tên sản phẩm, hãy trả về Giá bán.

Giả sử:

• Ô G3: nhập tên sản phẩm (Màn hình LG)
• Ô H3: hiển thị giá

Công thức:

=XLOOKUP(G3;B2:B7; D2:D7)

Trong đó:

• B2:B7: vùng dò tìm tên sản phẩm
• D2:D7: vùng chứa giá cần trả về

XLOOKUP không quan tâm dữ liệu nằm dọc hay ngang, chỉ cần vùng dò tìm và vùng kết quả tương ứng.

Kết quả:

Tra cứu từ phải sang trái

Hàm VLOOKUP không thể tra cứu từ phải sang trái. Nhưng hàm XLOOKUP lại giải quyết hoàn toàn được vấn đề này. 

*Yêu cầu: Dựa trên bảng dữ liệu dưới đây, bạn hãy dựa vào Danh mục sản phẩm và trả về Mã sản phẩm đầu tiên thuộc danh mục đó. 

Giả sử:

• Ô G2: nhập “Máy tính”
• Ô H2: hiển thị mã sản phẩm

Công thức:

=XLOOKUP(G2; C2:C7; A2:A7)

Trong đó:

• lookup_range (Danh mục) nằm bên phải result_range (Mã SP)
• Với VLOOKUP, yêu cầu này không thể thực hiện

Kết quả:

Khắc phục lỗi không thể tìm thấy dữ liệu

Trong thực tế, không phải lúc nào dữ liệu bạn tìm cũng tồn tại. Nếu không xử lý, bảng tính sẽ đầy lỗi #N/A gây khó chịu cho người xem. Do đó, bạn có thể sử dụng hàm XLOOKUP để xử lý lỗi không tìm thấy dữ liệu như sau:

*Yêu cầu: Dựa vào bảng dữ liệu trên, bạn hãy tra cứu Tồn kho theo mã sản phẩm, nếu không có thì hiển thị “Không tồn tại”.

Giả sử: 

• Ô G2: hiển thị tên sản phẩm
• Ô H2: tra cứu hàng tồn

Công thức:

=XLOOKUP(G2; A2:A7; E2:E7; “Không tồn tại”)

Trong đó:

• G2 – Giá trị cần tìm (lookup_value)
• A2:A7 – Vùng dò tìm (lookup_range) chứa danh sách mã sản phẩm trong bảng dữ liệu
• E2:E7 – Vùng trả về kết quả (result_range)  là cột chứa dữ liệu bạn muốn lấy, trong trường hợp này là số lượng tồn kho.
• “Không tồn tại” – Giá trị khi không tìm thấy (if_not_found)

Kết quả: 

Trả về một mảng kết quả (nhiều cột cùng lúc)

Sử dụng hàm XLOOKUP trong Google Sheets, bạn có thể trả về nhiều cột chỉ với một công thức. 

*Yêu cầu: Bạn hãy dựa vào bảng dữ liệu trên, dùng mã sản phẩm để áp dụng công thức trả về Tên sản phẩm, giá, tồn kho.

Giả sử: 

• Ô G2: Nhập mã sản phẩm SP01
• Ô H2, I2, J2: hiển thị kết quả

Công thức:

=XLOOKUP(G2; A2:A7; B2:E7)

Trong đó:

• G2 là giá trị cần tìm (mã sản phẩm)
• A2:A7 là vùng dò tìm mã sản phẩm
• B2:E7 là 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ả: 

Lỗi và cách khắc phục lỗi khi sử dụng hàm XLOOKUP trong Google Sheets

Trong quá trình sử dụng hàm XLOOKUP trong Google Sheets, việc gặp lỗi là điều hoàn toàn bình thường. Việc hiểu rõ nguyên nhân và cách khắc phục những lỗi này không chỉ giúp bạn sửa lỗi nhanh hơn, mà còn giúp sử dụng XLOOKUP hiệu quả và chuyên nghiệp hơn. 

Dưới đây là hai lỗi thường gặp nhất khi cách dùng hàm XLOOKUP trong Google Sheets nhất:

Lỗi #VALUE!

Lỗi #VALUE! xuất hiện khi hàm XLOOKUP không thể xử lý dữ liệu đầu vào, thường do vùng dò tìm và vùng kết quả không tương thích với nhau. 

Nguyên nhân phổ biến gây lỗi #VALUE! khi dùng hàm XLOOKUP thường do các vấn đề là:

• lookup_range và result_range không cùng kích thước

Ví dụ: lookup_range: A2:A7 (6 dòng) mà result_range: E2:E10 (9 dòng). Khi số hàng hoặc số cột không bằng nhau, hàm XLOOKUP không biết lấy dữ liệu dòng nào để trả về, lúc này sẽ báo lỗi #VALUE!.

• Dùng XLOOKUP trả về mảng nhưng các ô bên phải đã có dữ liệu

Khi bạn dùng hàm XLOOKUP để trả về nhiều cột cùng lúc, Google Sheets cần các ô trống để cho kết quả. Nếu các ô này đã có dữ liệu, hàm sẽ báo lỗi. 

• Chọn sai hướng dữ liệu (hàng – cột)

Ví dụ: lookup_range là cột dọc mà result_range lại là hàng ngang. Tức là không cùng cấu trúc, trả về lỗi #VALUE!.

Cách khắc phục lỗi #VALUE! khi sử dụng hàm XLOOKUP trong Google Sheets như sau:

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

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 phổ biến nhất khi sử dụng hàm XLOOKUP trong Google Sheets, đặc biệt trong các bảng dữ liệu lớn.

Nguyên nhân phổ biến gây lỗi #N/A khi dùng hàm XLOOKUP trong Google Sheets là do

• Giá trị cần tra cứu không có trong bảng dữ liệu

Ví dụ: nhập mã sản phẩm SP99 trong khi bảng chỉ có từ SP01 đến SP05. 

• Nhập sai kiểu dữ liệu (text và number). 

Một số mã có thể nhìn giống nhau nhưng thực chất khác kiểu (ví dụ: 001 và 1) và có khoảng trắng thừa ở đầu hoặc cuối ô

• Dữ liệu bị sai chính tả hoặc thiếu ký tự

Chỉ cần thừa hoặc thiếu 1 ký tự, hàm XLOOKUP sẽ không nhận diện được dữ liệu.

Cách khắc phục lỗi #N/A khi dùng hàm XLOOKUP trong Google Sheets như sau:

• Kiểm tra lại dữ liệu nhập vào có tồn tại trong bảng hay không
• Đảm bảo dữ liệu trong bảng được nhập thống nhất
• Sử dụng tham số if_not_found để xử lý lỗi thân thiện hơn

Lưu ý quan trọng khi sử dụng hàm XLOOKUP trong Google Sheets

Mặc dù hàm XLOOKUP trong Google Sheets được đánh giá là dễ dùng, linh hoạt và ít lỗi hơn so với VLOOKUP hay HLOOKUP. Nhưng để sử dụng hàm này hiệu quả và bền vững trong thực tế, bạn vẫn cần nắm rõ một số lưu ý quan trọng. Những lưu ý dưới đây sẽ giúp bạn tránh lỗi không đáng có, tối ưu công thức và làm việc với Google Sheets một cách chuyên nghiệp hơn.

• Đảm bảo vùng dò tìm và vùng kết quả có cùng kích thước

Bạn cần lưu ý lookup_range và result_range phải có cùng số hàng hoặc số cột. Nếu hai vùng này không tương thích, Google Sheets sẽ trả về lỗi #VALUE!.

• Kiểm tra kỹ dữ liệu đầu vào (lookup_value)

Có rất nhiều lỗi #N/A xảy ra không phải do công thức sai, mà do giá trị cần tìm không khớp dữ liệu trong bảng. Các lỗi thường gặp gồm sai chính ta, thừa khoảng trống, sai kiểu dữ liệu. Để đảm bảo đúng, bạn nên nhập đúng công thức và đồng nhất.

• Luôn dùng tham số if_not_found để tránh lỗi #N/A

Một bảng tính chuyên nghiệp không nên hiển thị lỗi kỹ thuật cho người xem. Vì vậy, khi sử dụng hàm XLOOKUP trong Google Sheets, bạn nên luôn thêm tham số if_not_found.

Lời kết

Qua bài viết này, hy vọng bạn đã hiểu rõ hàm XLOOKUP trong Google Sheets là gì, cách dùng ra sao và vì sao đây là hàm tra cứu nên ưu tiên sử dụng trong công việc hiện nay. So với các hàm truyền thống như VLOOKUP hay HLOOKUP, XLOOKUP không chỉ dễ dùng hơn mà còn linh hoạt, ít lỗi và phù hợp với nhiều tình huống tra cứu dữ liệu thực tế.

Nếu bạn đang làm việc thường xuyên với Google Sheets, hãy bắt đầu thay thế dần các hàm tra cứu cũ bằng hàm XLOOKUP trong Google Sheets. Bạn sẽ thấy rõ được sự tối ưu hiệu suất và nâng cao kỹ năng xử lý dữ liệu. Nhớ theo dõi GCS Việt Nam qua các kênh chính thống để cập nhật thêm nhiều thủ thuật của Google Sheets nhé.

• Fanpage: GCS – Google Cloud Solutions 

• Hotline: 024.9999.7777