Hàm XLOOKUP – Công cụ dò tìm dữ liệu đột phá ở trên Excel thuộc phần mềm Microsoft, hỗ trợ khắc phục hoàn toàn giới hạn tìm kiếm một chiều hoặc lỗi đếm cột của hàm VLOOKUP. Quý độc giả hãy cùng nhau khám phá cách sử dụng hàm XLOOKUP trong Excel chuẩn nhất hiện nay, nhằm hỗ trợ xử lý bảng tính nhanh chóng – chính xác nhé!
Hàm XLOOKUP trong Excel là gì?
XLOOKUP – Hàm tra cứu dữ liệu, cho phép người dùng tìm kiếm giá trị trong một mảng và sẽ trả về kết quả tương ứng ở một mảng khác. Theo đó, điểm đặc biệt của công cụ này đó là tìm kiếm theo cả hàng dọc – hàng ngang, thay thế cả hàm VLOOKUP và hàm HLOOKUP.
Lưu ý:
- Hàm XLOOKUP khả dụng ở trên những phiên bản hiện đại như Microsoft 365, Excel 2021, Excel Online và nền tảng Google Sheets.
- Những phiên bản Office đời cũ như Excel 2016 hay 2019 sẽ không hỗ trợ hàm này.
Vậy, công thức hàm XLOOKUP trong Excel như thế nào?
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Cụ thể:
- Lookup_value: giá trị sử dụng nhằm tìm kiếm (giá trị bắt buộc);
- Lookup_array: vùng hoặc mảng dữ liệu sử dụng nhằm tra cứu (là giá trị bắt buộc);
- Return_array: vùng/ mảng dữ liệu để lấy dữ liệu trả về (là giá trị bắt buộc);
- If_not_found (không bắt buộc): giá trị thay thế khi không tìm thấy. Nếu như bỏ qua, lỗi #N/A chính là kết quả;
- Match_mode (không bắt buộc): kiểu tìm kiếm;
- 0 hoặc bỏ qua: tìm kiếm chính xác. Nếu như giá trị tìm kiếm không tìm thấy, #N/A sẽ được trả về;
- 1: tìm kiếm chính xác hay là giá trị nhỏ hơn gần nhất. Nếu như giá trị tìm kiếm chính xác không được tìm thấy, giá trị nhỏ hơn gần nhất khi đó sẽ được trả về;
- – 1: tìm kiếm chính xác hoặc giá trị lớn hơn gần nhất. Nếu như giá trị tìm kiếm chính xác không được tìm thấy, giá trị lớn hơn gần nhất sẽ được trả về.
- 2: tìm kiếm theo ký tự đại diện;
- Search_mode (không bắt buộc): hướng tìm kiếm ở trong dữ liệu;
- 1 hoặc bỏ qua: tìm từ đầu tới cuối;
- -1: tìm từ cuối tới đầu;
- 2: tìm kiếm nhị phân ở trên dữ liệu được sắp xếp tăng dần.
- -2: ttìm kiếm nhị phân trên dữ liệu được sắp xếp giảm dần
Hướng dẫn cách dùng hàm XLOOKUP trong Excel chuẩn nhất
Một số những ví dụ do bản tin tức mới nhất hôm nay chia sẻ về một số các trường hợp hữu dụng về cách dùng hàm XLOOKUP trong Excel, bên cạnh đó mọi người sẽ có thêm một số những ví dụ phức tạp hơn nhằm nâng cao thêm khả năng dùng hàm tra cứu trong Excel.
Tra cứu theo chiều ngang và chiều dọc
Microsoft dùng hai hàm cho ra hai kiểu tra cứu: VLOOKUP khi muốn tra cứu trong cột và hàm HLOOKUP nếu như mọi người muốn tra cứu theo hàng.
Hàm XLOOKUP sẽ hỗ trợ tra cứu theo cả cột – hàng với cùng một cú pháp. Như vậy, mọi người sẽ có ít thông tin phải nhớ hơn khi dùng đến hàm XLOOKUP.
Công thức tra cứu theo cột sử dụng XLOOKUP cụ thể như sau:
=XLOOKUP(E1, A2:A6, B2:B6)
Công thức tra cứu theo hàng sử dụng XLOOKUP như sau:
=XLOOKUP(I1, B1:F1, B2:F2)
Tra cứu & Lấy giá trị ở cột bên trái cột tìm kiếm
Ở trong những phiên bản trước của Excel, cách thức đáng tin cậy duy nhất để mọi người có thể trả về giá trị tìm kiếm ở trong cột năm ở bên trái cột có chứa giá trị cần tra cứu đó là dùng kết hợp hàm Index và Match. Đối với hàm XLOOKUP trong Excel, mọi người có thể làm việc này với công thức đơn giản hơn nhiều, cụ thể:
=XLOOKUP(F1, B2:B6, A2:A6)
Tra cứu chính xác & Tra cứu gần đúng với hàm XLOOKUP
Thiết lập sẽ giúp cho hàm XLOOKUP có thể sẽ tra cứu được chính xác, hoặc tra cứu gần đúng được điều khiển bởi tham số thứ 5: match_mode. Mặc định, hàm XLOOKUP sẽ tra cứu chính xác nhất.
Chú ý: nếu như mọi người thiết lập tham số match_mode là 1 hoặc -1 thì XLOOKUP vẫn luôn luôn tìm kiếm giá trị chính xác trước, còn nếu giá trị này không được tìm thấy thì giá trị gần đúng mới được trả về.
Một số những giá trị mà match_mode nhận và có ý nghĩa cụ thể như sau:
- 0 hoặc bỏ qua: tìm kiếm chính xác. trường hợp không tìm thấy, sẽ trả về lỗi #N/A;
- -1: tìm kiếm chính xác trước, nếu như không tìm thấy, sẽ trả về giá trị nhỏ hơn gần nhất;
- 1: tìm kiếm chính xác trước, nếu như không tìm thấy và sẽ trả về giá trị lớn hơn gần nhất.
Tìm kiếm chính xác bằng XLOOKUP
Thường thì mọi người dùng khả năng tìm kiếm chính xác của những hàm tìm kiếm nhiều hơn khả năng tìm kiếm gần đúng. Bởi một cách mặc định, hàm XLOOKUP trong Excel sẽ tìm kiếm một cách chính xác, do đó mọi người có thể bỏ qua không điền tham số match_mode, chỉ điền đủ 3 tham số bắt buộc của hàm XLOOKUP:
=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6)
Tra cứu gần đúng với hàm XLOOKUP
Cách dùng hàm XLOOKUP – Nhằm thực hiện tra cứu gần đúng dùng hàm XLOOKUP, mọi người nên tiến hành thiết lập match_mode với giá trị 1 hoặc -1. Việc dùng 1 hay -1 cũng tùy thuộc vào dữ liệu của bạn sẽ được tổ chức như thế nào.
Nếu như ô F2 có giá trị là 98, vì không tìm thấy được giá trị 98 ở trong vùng tìm kiếm là B2:B6, vì vậy XLOOKUP tìm giá trị nhỏ hơn gần nhất đó là 90, và 90 sẽ ứng với kết quả A tại cùng dòng.
Nếu bảng tìm kiếm của mọi người có chứa dữ liệu như trên, 98 sẽ không được tìm thấy ở trong vùng tìm kiếm, match_mode là 1, nên hàm XLOOKUP sẽ tìm đến giá trị lớn hơn gần nhất (100) và kết quả chúng ta có được là A.
Mẹo: Khi mọi người copy công thức XLOOKUP để áp dụng cho nhiều dòng, cần phải khóa tham chiếu $B$2:$B$6 để khi kéo công thức thì tham chiếu không thay đổi.
Những lỗi thường gặp khi dùng hàm XLOOKUP trong Excel
Tiếp theo ở chuyên mục Tin học văn phòng chia sẻ đến với mọi người về những lỗi XLOOKUP thường gặp nhất hiện nay, gồm có:
Lỗi #VALUE!
- Nguyên nhân: XLOOKUP hoạt động theo nguyên tắc “1 kèm 1”. Cột có chứa giá trị tìm kiếm, cột chứa kết quả cần phải có chiều dài giống nhau. Nếu không, hàm sẽ bị lỗi.
- Lấy ví dụ minh họa: thực hiện ghép dữ liệu báo cáo, quét cột URL từ dòng 2 đến dòng 5000 (A2:A5000), tuy nhiên khi quét cột Sessions bạn lại lỡ tay chỉ kéo đến dòng 4999 (B2:B4999).
- Giải pháp khắc phục: nhấn vào thanh công thức, kiểm tra tọa độ của đối số thứ 2 (lookup_array) và đối số thứ 3 (return_array). Khi đó, hãy sửa lại từng con số cuối cùng cho khớp nhau tuyệt đối (cùng là 5000).
Lỗi #SPILL!
- Nguyên nhân: khi yêu cầu XLOOKUP trả về nhiều cột cùng lúc, hàm cần đến không gian trống ở những ô liền kề bên phải để tự động “tràn” kết quả ra. Nếu như khu vực nó định tràn ra đang có chứa bất cứ thứ gì (dù là một khoảng trắng do gõ nhầm phím cách, hoặc một ghi chú cũ), khi đó hàm sẽ tự động ngắt ngang.
- Lấy ví dụ minh họa: viết công thức ở ô D2, theo như dự kiến kết quả sẽ ra ở D2, E2 và F2. Tuy nhiên, ô E2 lại đang có chữ “Tìm kiếm”, như vậy cũng bị lỗi.
- Giải pháp khắc phục: nhấp vào ô đang bị lỗi, Excel sẽ hiện một đường viền nét đứt mờ mờ bao quanh cùng mà nó cần để tràn dữ liệu ra. Mọi người chỉ cần quét dọn vùng đó, lựa chọn những ô bị vướng và nhấn Delete để xóa sạch. Kết quả sẽ tự động bung ra ngay lập tức.
Lỗi #N/A hoặc trả về khoảng trắng
- Nguyên nhân: lỗi này thường xuất hiện khi mọi người tải những tệp dữ liệu thô từ hệ thống (GSC, Ahrefs, ERP…) xuống. Một bên sẽ là ID định dạng số (Number), một bên lại bị xuất ra dưới dạng chữ (Text).
- Lấy ví dụ minh họa: mọi người đang tìm ID bài viết 12345 (Text) trong danh sách ID gốc là 12345 (Number). Cho 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.
- Giải pháp khắc phục:
- Lựa chọn cả một cột dữ liệu bị lệch định dạng;
- Vào trong thẻ Data trên thanh công cụ -> Lựa chọn Text to Columns.
- Không chỉnh gì thêm, nhấn vào Finish luôn. Cột đó sẽ được ép về chuẩn định dạng số và hàm sẽ tự động nhận diện được ngay.
Nguồn: Sưu tầm
