Trong quá trình làm việc với tin học văn phòng Excel, các hàm tìm kiếm và tra cứu dữ liệu như VLOOKUP, HLOOKUP hay INDEX MATCH được sử dụng rất phổ biến. Tuy nhiên, những hàm này vẫn tồn tại một số hạn chế nhất định, đặc biệt khi cần tìm kiếm dữ liệu linh hoạt hoặc thay đổi cấu trúc bảng tính.
Để khắc phục những hạn chế đó, Microsoft đã phát triển hàm XLOOKUP trong Excel. Đây là hàm tra cứu dữ liệu hiện đại, cho phép tìm kiếm theo cả chiều dọc và chiều ngang, hỗ trợ tìm kiếm từ trái sang phải hoặc từ phải sang trái và mặc định sử dụng kết quả khớp chính xác.
Trong bài viết này, tinhoc115.com sẽ hướng dẫn bạn cách sử dụng hàm XLOOKUP từ cơ bản đến nâng cao, kèm theo những ví dụ thực tế giúp bạn dễ dàng áp dụng vào công việc.
1. Hàm XLOOKUP trong Excel là gì?
XLOOKUP là hàm dùng để tìm kiếm một giá trị trong một vùng dữ liệu và trả về kết quả tương ứng tại một vùng dữ liệu khác.
Ví dụ: Bạn có một bảng danh sách nhân viên gồm mã nhân viên, họ tên, phòng ban và mức lương. Khi nhập mã nhân viên, Excel sẽ tự động tìm kiếm và trả về họ tên, phòng ban hoặc mức lương tương ứng.
Điểm nổi bật của XLOOKUP là không yêu cầu cột chứa giá trị tìm kiếm phải nằm bên trái cột trả về kết quả như VLOOKUP.
Những ưu điểm nổi bật của hàm XLOOKUP:
- Tìm kiếm dữ liệu theo cả hai chiều trái – phải và phải – trái.
- Mặc định tìm kiếm chính xác, hạn chế sai sót.
- Không cần xác định số thứ tự cột trả về như VLOOKUP.
- Có thể tùy chỉnh nội dung hiển thị khi không tìm thấy dữ liệu.
- Hỗ trợ tìm kiếm gần đúng, ký tự đại diện và tìm kiếm ngược.
- Có thể trả về nhiều cột dữ liệu cùng lúc.
2. Cú pháp hàm XLOOKUP trong Excel
Cú pháp tổng quát của hàm XLOOKUP như sau:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Ý nghĩa các tham số:
| Tham số | Ý nghĩa |
|---|---|
| lookup_value | Giá trị cần tìm kiếm. |
| lookup_array | Vùng dữ liệu chứa giá trị cần tìm. |
| return_array | Vùng dữ liệu chứa kết quả cần trả về. |
| if_not_found | Nội dung hiển thị nếu không tìm thấy kết quả. |
| match_mode | Chế độ khớp dữ liệu. |
| search_mode | Hướng và phương thức tìm kiếm. |
Trong đó, ba tham số đầu tiên là bắt buộc. Các tham số còn lại có thể bỏ qua nếu không cần thiết.
Các chế độ tìm kiếm của XLOOKUP:
Tham số match_mode:
- 0: Tìm kiếm chính xác (mặc định).
- -1: Tìm chính xác, nếu không có thì trả về giá trị nhỏ hơn tiếp theo.
- 1: Tìm chính xác, nếu không có thì trả về giá trị lớn hơn tiếp theo.
- 2: Tìm kiếm bằng ký tự đại diện như *, ? và ~.
Tham số search_mode:
- 1: Tìm kiếm từ trên xuống dưới (mặc định).
- -1: Tìm kiếm ngược từ dưới lên trên.
- 2: Tìm kiếm nhị phân trên dữ liệu sắp xếp tăng dần.
- -2: Tìm kiếm nhị phân trên dữ liệu sắp xếp giảm dần.
Lưu ý: Khi sử dụng chế độ tìm kiếm nhị phân (2 hoặc -2), vùng dữ liệu cần được sắp xếp đúng thứ tự. Nếu không, kết quả có thể không chính xác.
3. Hướng dẫn sử dụng hàm XLOOKUP qua ví dụ thực tế
Ví dụ 1: Tìm tên nhân viên theo mã nhân viên
Giả sử chúng ta có bảng dữ liệu nhân viên như sau:
| Mã nhân viên | Họ và tên | Phòng ban | Lương |
|---|---|---|---|
| NV001 | Nguyễn Văn An | Kinh doanh | 15.000.000 |
| NV002 | Trần Thị Bình | Kế toán | 14.000.000 |
| NV003 | Lê Văn Cường | IT | 16.000.000 |
| NV004 | Phạm Thị Dung | Nhân sự | 13.000.000 |
| NV005 | Hoàng Văn Em | Marketing | 17.000.000 |
Yêu cầu: Khi nhập mã nhân viên NV003, Excel tự động trả về họ và tên tương ứng.
Giả sử:
- Ô F2 chứa mã nhân viên cần tìm.
- Vùng A2 chứa mã nhân viên.
- Vùng B2 chứa họ và tên.
Công thức:
=XLOOKUP(F2,A2:A6,B2:B6)
Kết quả trả về:
Lê Văn Cường
Giải thích:
- F2: Giá trị cần tìm là NV003.
- A2:A6: Vùng chứa mã nhân viên.
- B2:B6: Vùng chứa họ và tên cần trả về.
Chỉ với một công thức đơn giản, bạn đã có thể tra cứu thông tin nhân viên nhanh chóng mà không cần sử dụng VLOOKUP.
Ví dụ 2: Tìm kiếm và trả về thông báo khi không có dữ liệu
Trong thực tế, không phải lúc nào giá trị tìm kiếm cũng tồn tại trong bảng dữ liệu. Nếu không tìm thấy, XLOOKUP mặc định sẽ trả về lỗi #N/A.
Để hiển thị thông báo thân thiện hơn, bạn có thể sử dụng tham số if_not_found.
Công thức:
=XLOOKUP(F2,A2:A6,B2:B6,"Không tìm thấy nhân viên")
Khi mã nhân viên tồn tại, Excel trả về họ tên tương ứng.
Khi mã nhân viên không tồn tại, Excel hiển thị:
Không tìm thấy nhân viên
Đây là tính năng rất hữu ích khi xây dựng bảng tra cứu thông tin, quản lý nhân sự, quản lý hàng hóa hoặc báo cáo bán hàng.
Ví dụ 3: Tìm kiếm từ phải sang trái – Ưu điểm vượt trội so với VLOOKUP
Đây là một trong những ưu điểm quan trọng nhất của XLOOKUP.
Giả sử bảng dữ liệu có cấu trúc:
| Họ và tên | Mã nhân viên | Phòng ban |
|---|---|---|
| Nguyễn Văn An | NV001 | Kinh doanh |
| Trần Thị Bình | NV002 | Kế toán |
| Lê Văn Cường | NV003 | IT |
Yêu cầu: Nhập mã nhân viên NV003 để tìm ra họ tên tương ứng.
Công thức:
=XLOOKUP(F2,B2:B4,A2:A4)
Kết quả: Lê Văn Cường
Bạn có thể thấy vùng tìm kiếm nằm ở cột B, còn vùng trả về kết quả nằm ở cột A.
Điều này hoàn toàn thực hiện được với XLOOKUP nhưng VLOOKUP không thể xử lý trực tiếp theo cách thông thường, vì VLOOKUP chỉ tìm kiếm ở cột đầu tiên của vùng dữ liệu và trả kết quả từ các cột bên phải.
Ví dụ 4: Trả về nhiều cột dữ liệu cùng lúc
Một tính năng hữu ích khác của XLOOKUP là có thể trả về nhiều cột kết quả từ một công thức duy nhất.
Giả sử bạn muốn tra cứu mã nhân viên NV003 và lấy đồng thời họ tên, phòng ban và mức lương.
Công thức:
=XLOOKUP(F2,A2:A6,B2:D6,"Không tìm thấy")
Kết quả trả về gồm ba thông tin tương ứng:
- Họ và tên: Lê Văn Cường.
- Phòng ban: IT.
- Lương: 16.000.000.
Trong các phiên bản Excel hỗ trợ mảng động, kết quả sẽ tự động tràn sang các ô bên cạnh nếu những ô đó đang trống.
4. So sánh hàm XLOOKUP và VLOOKUP trong Excel
VLOOKUP là hàm tra cứu quen thuộc với nhiều người dùng Excel. Tuy nhiên, XLOOKUP mang lại nhiều cải tiến đáng kể.
| Tiêu chí | VLOOKUP | XLOOKUP |
|---|---|---|
| Hướng tìm kiếm | Trái sang phải | Hai chiều |
| Khớp chính xác | Phải thêm FALSE | Mặc định |
| Chỉ số cột | Phải khai báo | Không cần |
| Thông báo không tìm thấy | Thường kết hợp IFERROR | Có tham số riêng |
| Tìm kiếm ngược | Không hỗ trợ trực tiếp | Có |
| Trả về nhiều cột | Hạn chế | Hỗ trợ |
| Thay đổi cấu trúc bảng | Dễ ảnh hưởng số thứ tự cột | Linh hoạt hơn |
Ví dụ chuyển đổi từ VLOOKUP sang XLOOKUP
Công thức VLOOKUP:
=VLOOKUP(F2,A2:D6,2,FALSE)
Công thức XLOOKUP tương đương:
=XLOOKUP(F2,A2:A6,B2:B6)
Bạn có thể thấy công thức XLOOKUP dễ đọc và dễ quản lý hơn vì không cần xác định số thứ tự cột trả về.
Đặc biệt, khi thêm hoặc xóa cột trong bảng dữ liệu, công thức XLOOKUP thường ít bị ảnh hưởng hơn do vùng tìm kiếm và vùng trả về được khai báo riêng biệt.
5. Một số lỗi thường gặp khi sử dụng hàm XLOOKUP
Mặc dù XLOOKUP rất linh hoạt, người dùng vẫn có thể gặp một số lỗi trong quá trình sử dụng.
Lỗi #N/A – Không tìm thấy dữ liệu
Nguyên nhân:
- Giá trị tìm kiếm không tồn tại.
- Dữ liệu có khoảng trắng thừa.
- Mã tìm kiếm khác kiểu dữ liệu (số và văn bản).
Cách khắc phục: Kiểm tra dữ liệu đầu vào hoặc sử dụng tham số if_not_found để hiển thị thông báo.
Lỗi #SPILL! – Lỗi tràn mảng
Nguyên nhân: Công thức trả về nhiều kết quả nhưng những ô cần tràn đã có dữ liệu hoặc bị cản trở.
Cách khắc phục: Xóa nội dung trong vùng ô cần hiển thị kết quả và kiểm tra vùng tràn.
Lỗi #VALUE!
Nguyên nhân có thể do kích thước vùng tìm kiếm và vùng trả về không tương thích.
Cách khắc phục: Kiểm tra lại các vùng lookup_array và return_array để đảm bảo chúng có kích thước phù hợp.
Không nhận diện được hàm XLOOKUP
Nếu Excel báo lỗi tên hàm hoặc không hỗ trợ XLOOKUP, có thể bạn đang sử dụng phiên bản Excel cũ.
Theo Microsoft, XLOOKUP không có sẵn trong Excel 2016 và Excel 2019. Hàm được hỗ trợ trong Microsoft 365, Excel 2021 và Excel 2024.
6. Kết luận
Hàm XLOOKUP là một trong những hàm tra cứu dữ liệu hữu ích nhất trong Excel hiện đại. Với khả năng tìm kiếm linh hoạt theo hai chiều, mặc định khớp chính xác, hỗ trợ trả về nhiều cột và xử lý trường hợp không tìm thấy dữ liệu, XLOOKUP giúp công việc tra cứu trở nên đơn giản và hiệu quả hơn.
Nếu bạn thường xuyên sử dụng VLOOKUP để tìm kiếm dữ liệu, hãy bắt đầu làm quen với XLOOKUP để tối ưu bảng tính và nâng cao hiệu suất làm việc.
Hy vọng bài hướng dẫn của tinhoc115.com đã giúp bạn hiểu rõ cách sử dụng hàm XLOOKUP trong Excel và có thể áp dụng ngay vào công việc thực tế.
Hãy tiếp tục theo dõi tinhoc115.com để cập nhật thêm nhiều thủ thuật Excel, Word và tin học văn phòng hữu ích!
Trả lời