MICROSOFT CHÍNH HÃNG GIẢM MẠNH | MUA NGAY
XLOOKUP là một hàm tra cứu dữ liệu, cho phép người dùng tìm kiếm một giá trị trong một mảng và trả về kết quả tương ứng ở một mảng khác. Điểm đặc biệt của công cụ này là có thể tìm kiếm theo cả hàng dọc và hàng ngang, thay thế cả hàm VLOOKUP và hàm HLOOKUP.
Lưu ý:
Hàm XLOOKUP là hàm tra cứu dữ liệu thông minh trong Excel
Hàm XLOOKUP có cú pháp như sau:
= XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Lưu ý: Các đối số nằm trong dấu ngoặc vuông [ ] là tùy chọn, có thể bỏ qua nếu không cần thiết.
| Tên đối số | Giải thích |
| lookup_value | Giá trị cần tìm kiếm. Đây là giá trị cơ sở (có thể là một ô, văn bản, hoặc số) mà bạn dùng để dò tìm. |
| lookup_array | Vùng hoặc mảng dữ liệu dùng để dò tìm. Excel sẽ tìm kiếm lookup_value bên trong phạm vi này. |
| return_array | Vùng hoặc mảng dữ liệu chứa kết quả. Khi tìm thấy sự trùng khớp ở lookup_array, hàm sẽ trả về giá trị tương ứng nằm trong vùng này. |
| [if_not_found] | Giá trị trả về nếu không tìm thấy. Thay vì hiển thị lỗi #N/A như VLOOKUP, bạn có thể thiết lập hàm trả về một đoạn văn bản (ví dụ: "Không có dữ liệu"), số 0, hoặc để trống (""). |
| [match_mode] | Xác định kiểu khớp dữ liệu. Mặc định hàm sẽ tự động khớp chính xác (0).
|
| [search_mode] | Xác định hướng và cách thức dò tìm. Mặc định hàm sẽ tìm từ trên xuống (1).
|
Giả sử bạn có một bảng dữ liệu nhân sự như sau:
| Mã NV (Cột A) | Tên Nhân Viên (Cột B) | Phòng Ban (Cột C) | Lương (Cột D) |
| NV01 | Nguyễn Văn A | Nhân sự | 10,000,000 |
| NV02 | Trần Thị B | Kế toán | 12,000,000 |
| NV03 | Lê Văn C | IT | 15,000,000 |
Yêu cầu: Tìm lương của nhân viên có mã là "NV02".
Tại ô hiển thị kết quả, bạn nhập công thức:
Trong đó:
Ví dụ về hàm XLOOKUP tra cứu thông tin cơ bản
Đây chính là một trong những tính năng "ăn tiền" nhất của XLOOKUP. Khác với VLOOKUP (bắt buộc cột chứa giá trị tìm kiếm phải nằm ngoài cùng bên trái), XLOOKUP có thể tìm kiếm dữ liệu theo bất kỳ hướng nào, kể cả từ phải qua trái.
Giả sử bạn có một bảng dữ liệu mà cột Số Điện Thoại nằm ở bên phải (Cột C), và bạn muốn tìm Tên Nhân Viên nằm ở bên trái (Cột A) dựa trên số điện thoại mà khách hàng cung cấp.
| Tên Nhân Viên (Cột A) | Phòng Ban (Cột B) | Số Điện Thoại (Cột C) |
| Nguyễn Văn A | Nhân sự | 0901234567 |
| Trần Thị B | Kế toán | 0987654321 |
| Lê Văn C | IT | 0911222333 |
Yêu cầu: Tìm xem số điện thoại 0987654321 là của nhân viên nào.
Tại ô hiển thị kết quả, bạn nhập công thức:
Trong đó:
Ví dụ về hàm XLOOKUP tìm kiếm dữ liệu từ phải sang trái
Hàm XLOOKUP không chỉ thay thế VLOOKUP mà còn thay thế hoàn hảo cho HLOOKUP khi bạn cần dò tìm dữ liệu theo hàng ngang. Điểm mạnh là bạn không cần phải đếm số thứ tự hàng (như 1, 2, 3...) và công thức sẽ không bị lỗi nếu bạn chèn thêm hàng mới vào giữa.
Giả sử bạn có bảng dữ liệu báo cáo hiệu suất sau:
| Tháng | Tháng 1 | Tháng 2 | Tháng 3 | Tháng 4 | Tháng 5 | Tháng 6 |
| Sessions | 45,000 | 52,000 | 48,000 | 62,498 | 58,000 | 65,000 |
Yêu cầu: Tìm số lượng sessions đạt được trong tháng 4.
Tại ô hiển thị kết quả, bạn nhập công thức:
Trong đó:
Kỹ thuật lồng hai hàm XLOOKUP thường được gọi là Dò tìm 2 chiều (Ma trận). Nó giúp bạn tìm ra một dữ liệu chính xác nằm ở giao điểm của một Cột và một Hàng. Ứng dụng này thường thấy trong các báo cáo doanh thu theo tháng và theo khu vực, nơi bạn cần xác định chính xác số liệu dựa trên hai tiêu chí cùng lúc.
Giả sử bạn có một bảng báo cáo doanh thu như sau:
| Khu vực (Cột A) | Tháng 1 (Cột B) | Tháng 2 (Cột C) | Tháng 3 (Cột D) |
| Miền Bắc | 100,000 | 120,000 | 130,000 |
| Miền Trung | 80,000 | 90,000 | 95,000 |
| Miền Nam | 150,000 | 160,000 | 180,000 |
Yêu cầu: Tìm doanh thu của Miền Trung trong Tháng 2.
Tại ô hiển thị kết quả, bạn nhập công thức:
Bí quyết để hiểu công thức này là đọc nó thành 2 bước, Excel sẽ tính toán cái hàm ở bên trong trước:
Bước 1 - Hàm lồng bên trong "XLOOKUP("Tháng 2", B1:D1, B2:E4)"
>> Kết quả của bước này là Excel đang ngầm cầm trên tay một cột dọc gồm 3 con số của Tháng 2: 120,000; 90,000; 160,000
Bước 2 - Hàm bọc bên ngoài "=XLOOKUP("Miền Trung", B2:B4, [Kết_quả_hàm_trong])"
>> Kết quả của bước này là Excel nhặt ra con số 90,000.
Ví dụ về hàm XLOOKUP lồng nhiều điều kiện
| Tiêu chí | XLOOKUP | VLOOKUP | INDEX và MATCH |
| Chiều dò tìm | Mọi hướng. | Chỉ từ Trái sang Phải (Cột chứa giá trị tìm kiếm bắt buộc phải nằm ngoài cùng bên trái). | Mọi hướng (Trái sang Phải, Phải sang Trái, Trên xuống Dưới). |
| Ảnh hưởng khi chèn/xóa cột | Không bị ảnh hưởng vì tham chiếu trực tiếp đến vùng dữ liệu. | Dễ bị lỗi hoặc sai kết quả do dùng số thứ tự cột (ví dụ: cột số 3, 4). | Không bị ảnh hưởng vì tham chiếu trực tiếp đến vùng dữ liệu. |
| Giá trị mặc định | Mặc định là dò tìm chính xác. | Mặc định là dò tìm tương đối (dễ ra kết quả sai nếu quên nhập số 0 ở cuối). | Tự định cấu hình qua hàm MATCH (thường nhập 0 để khớp chính xác). |
| Xử lý lỗi (#N/A) | Tích hợp sẵn ngay trong đối số thứ 4 [if_not_found]. | Phải lồng thêm hàm IFERROR (Ví dụ: =IFERROR(VLOOKUP(...), "Lỗi")). | Phải lồng thêm hàm IFERROR. |
| Trả về nhiều cột (Mảng động) | Hỗ trợ mặc định. Chỉ cần quét vùng kết quả là nhiều cột, hàm sẽ tự tràn dữ liệu ra. | Rất phức tạp, phải dùng mảng {1,2,3} hoặc kéo công thức. | Phải kéo công thức và cố định ô cẩn thận. |
| Dò tìm từ dưới lên (Lấy data mới nhất) | Hỗ trợ mặc định (Thiết lập [search_mode] là -1). | Không làm được. | Rất phức tạp (phải kết hợp mảng). |
Khi nào nên dùng hàm nào?
1. VLOOKUP
Dù có nhiều hạn chế, VLOOKUP vẫn là hàm "quốc dân" ai cũng biết. Nếu bạn chỉ cần dò một bảng dữ liệu đơn giản, không thay đổi cấu trúc cột, VLOOKUP vẫn đáp ứng tốt.
Xóa địa chỉ
Volume từ bảng này sang bảng khác khi vị trí các cột đã chuẩn.Trước năm 2020 (khi XLOOKUP ra mắt), đây là bộ đôi bắt buộc phải biết của dân xử lý dữ liệu chuyên nghiệp. Mặc dù công thức dài và khó đọc hơn, nó vẫn có giá trị trong các trường hợp:
3. XLOOKUP
XLOOKUP sinh ra để khắc phục mọi nhược điểm của hai cách trên. Nó đặc biệt tỏa sáng khi bạn phải xử lý và bóc tách dữ liệu từ các tệp trích xuất lớn.
So sánh hàm VLOOKUP, INDEX & MATCH và XLOOKUP
Dưới đây là chi tiết các lỗi XLOOKUP thường gặp nhất:
Nguyên nhân: XLOOKUP hoạt động theo nguyên tắc "1 kèm 1". Cột chứa giá trị tìm kiếm và cột chứa kết quả phải có chiều dài y hệt nhau. Nếu không, hàm sẽ lập tức báo lỗi.
Ví dụ: Khi ghép dữ liệu báo cáo, bạn quét cột URL từ dòng 2 đến dòng 5000 (A2:A5000), nhưng lúc quét cột Sessions bạn lại lỡ tay chỉ kéo đến dòng 4999 (B2:B4999).
Cách khắc phục: Click vào thanh công thức, kiểm tra ngay tọa độ của đối số thứ 2 (lookup_array) và đối số thứ 3 (return_array). Sửa lại các con số cuối cùng cho khớp nhau tuyệt đối (cùng là 5000).
Lỗi #VALUE! trong khi sử dụng hàm XLOOKUP
Nguyên nhân: Khi bạn yêu cầu XLOOKUP trả về nhiều cột cùng lúc, hàm cần không gian trống ở các ô liền kề bên phải để tự động "tràn" kết quả ra. Nếu khu vực nó định tràn ra đang chứa bất kỳ thứ gì (dù chỉ là một khoảng trắng do gõ nhầm phím cách, hay một ghi chú cũ), hàm sẽ tự động ngắt ngang.
Ví dụ: Viết công thức ở ô D2, dự kiến kết quả sẽ ra ở D2, E2 và F2. Nhưng ô E2 lại đang có chữ "Check lại", thế là bị lỗi.
Cách khắc phục: Click vào ô đang báo lỗi, Excel sẽ hiện một đường viền nét đứt mờ mờ bao quanh vùng mà nó cần để tràn dữ liệu. Bạn chỉ cần quét dọn vùng đó, chọn các ô bị vướng và nhấn phím Delete để xóa sạch. Kết quả sẽ tự động bung ra ngay lập tức.
Lỗi #SPILL! khi sử dụng hàm XLOOKUP
Nguyên nhân: Lỗi thường xuất hiện khi bạn tải các tệp dữ liệu thô từ hệ thống (GSC, Ahrefs, ERP...) xuống. Một bên là ID định dạng Số (Number), một bên lại bị xuất ra dưới dạng Chữ (Text).
Ví dụ: Bạn tìm ID bài viết 12345 (Text) trong danh sách ID gốc là 12345 (Number). Dù đọc giống nhau, XLOOKUP coi đây là 2 thực thể khác biệt và báo không tìm thấy.
Cách khắc phục:
Lỗi định dạng khi sử dụng hàm XLOOKUP
Nguyên nhân: Excel có tính năng tự động tịnh tiến tọa độ. Khi bạn viết công thức cho dòng đầu tiên rồi kéo copy xuống hàng ngàn dòng bên dưới, tọa độ của Vùng tìm kiếm và Vùng kết quả cũng bị "trượt" xuống theo một cách tương ứng. Hệ quả là hàm không còn quét từ đầu bảng nữa, dẫn đến việc bỏ sót toàn bộ dữ liệu ở phần trên.
Ví dụ: Ở ô đầu tiên, vùng quét là A2:A5000. Khi bạn kéo công thức xuống dòng thứ 20, vùng quét bị tự động đổi thành A21:A5019. Dữ liệu từ dòng 2 đến 20 đã bị bỏ lỡ.
Cách khắc phục: Hãy tạo thói quen ngay sau khi quét xong vùng dữ liệu trong công thức, bấm phím F4 trên bàn phím. Các dấu đô-la ($) sẽ xuất hiện bao quanh tọa độ (ví dụ: $A$2:$A$5000), khóa chặt vùng này lại.
Lỗi thiếu cố định vùng chọn khi dùng hàm XLOOKUP
Để trị được những file Excel "hạng nặng" với hàng chục ngàn dòng, bảng Pivot Table phức tạp hay các hàm dò tìm mảng lớn, bạn cần những trợ thủ đắc lực hơn:
| Tiêu chí | Cấu hình khuyên dùng | Giải thích |
| Bộ nhớ (RAM) | Tối thiểu 16GB | Excel ngốn rất nhiều RAM khi xử lý dữ liệu lớn. 16GB giúp cuộn trang mượt mà, máy không bị treo hay văng file đột ngột khi đang làm việc. |
| Vi xử lý (CPU) | Core i5 hoặc Ryzen 5 trở lên (Thế hệ mới) | Tốc độ chạy hàm và tính toán phụ thuộc vào CPU. Bắt buộc chọn đời mới (Intel Gen 12, 13 trở lên hoặc AMD 7000 series) để xử lý nhanh gấp nhiều lần đời cũ. |
| Ổ cứng SSD | SSD 512GB | Giúp mở và lưu các file nặng hàng chục MB chỉ trong nháy mắt (dưới 3 giây), không phải chờ đợi vòng tròn loading. |
| Màn hình & Bàn phím | 15.6 inch có cụm phím số |
|
Hiện nay, Điện máy XANH đang cung cấp đa dạng các dòng laptop văn phòng từ Apple (MacBook), laptop Dell, laptop HP đến laptop Asus với nhiều phân khúc giá khác nhau cùng nhiều ưu đãi hấp dẫn.
Các dòng máy này không chỉ sở hữu thiết kế mỏng nhẹ, sang trọng mà còn có hiệu năng cực kỳ ấn tượng, đáp ứng tốt nhu cầu học tập và làm việc văn phòng. Bạn có thể trực tiếp đến cửa hàng để trải nghiệm trước khi quyết định đầu tư.
Hàm XLOOKUP hiện không có sẵn trên phiên bản Excel 2019, 2016 hay các bản cũ hơn do đây là tính năng độc quyền cho các phiên bản mới.
Để sử dụng, người dùng cần nâng cấp lên gói Microsoft 365 hoặc phiên bản Excel 2021 trở về sau để tận hưởng trọn vẹn sự tiện lợi. Ngoài ra, bạn cũng có thể cân nhắc sử dụng phiên bản Excel Online miễn phí hoặc Google Sheets như một phương án thay thế tạm thời.
Lỗi #VALUE! thường xảy ra khi mảng tìm kiếm và mảng trả về của công thức không có kích thước tương đương nhau về số hàng hoặc số cột.
Ví dụ, nếu vùng chọn tìm kiếm gồm 10 dòng nhưng vùng kết quả chỉ có 8 dòng, hệ thống sẽ báo lỗi do không thể đối chiếu dữ liệu tương ứng.
Cách khắc phục đơn giản nhất là kiểm tra lại các tham chiếu mảng trong công thức để đảm bảo chúng hoàn toàn khớp về số lượng ô.
Hàm này được tối ưu hóa rất tốt về mặt hiệu suất, giúp bảng tính hoạt động mượt mà ngay cả khi xử lý khối lượng dữ liệu khổng lồ lên đến hàng nghìn dòng.
Tuy nhiên, nếu laptop của bạn có cấu hình quá yếu, việc lồng quá nhiều hàm nâng cao vẫn có thể gây ra hiện tượng giật lag nhẹ trong quá trình nhập liệu. Bạn nên trang bị các dòng máy có cấu hình khá tại Điện máy XANH để đảm bảo mọi thao tác luôn ổn định và trơn tru nhất.
Việc nắm vững cách dùng hàm XLOOKUP không chỉ giúp bạn tiết kiệm thời gian mà còn mang lại sự chính xác tuyệt đối trong việc quản lý dữ liệu. Hy vọng bài viết trên có thể giúp ích cho bạn. Chúc bạn thực hành thành công!
Không hài lòng
↑
Đang xử lý... Vui lòng chờ trong giây lát.
