Cách dùng hàm XLOOKUP trong Excel tra cứu dữ liệu đơn giản, nhanh chóng và hiệu quả

Trong quá trình xử lý dữ liệu trong Excel, việc tìm kiếm thông tin từ bảng dữ liệu lớn là một nhu cầu rất thường gặp. Trước đây, chúng ta chủ yếu dựa vào các hàm như VLOOKUP, HLOOKUP hoặc kết hợp INDEX và MATCH để giải quyết các bài toán tra cứu. Tuy nhiên, những phương pháp này đôi khi khá phức tạp, hạn chế về tính linh hoạt, dễ xảy ra lỗi khi thay đổi dữ liệu. Vậy nên hàm XLOOKUP là một chọn lựa thay thế tuyệt vời.

Tổng quan về hàm XLOOKUP

Hàm XLOOKUP là gì?

Hàm XLOOKUP được thiết kế để thay thế cho các hàm tra cứu truyền thống như VLOOKUP, HLOOKUP và tổ hợp INDEX-MATCH. Khắc phục hoàn toàn những điểm yếu của các phương pháp cũ.

Không giống như VLOOKUP chỉ tra cứu từ trái sang phải, XLOOKUP cho phép tìm kiếm theo bất kỳ hướng nào trong bảng dữ liệu - ngang, dọc hoặc thậm chí theo chiều ngược lại. Đồng thời, nó đơn giản hóa cú pháp, dễ đọc, dễ hiểu và giảm thiểu khả năng xảy ra lỗi khi dữ liệu thay đổi.

Điểm nổi bật của XLOOKUP

• Tìm kiếm hai chiều: Dọc hoặc ngang.
• Không phụ thuộc vào số thứ tự cột.
• Cho phép thiết lập giá trị trả về khi không tìm thấy.
• Có thể tìm kiếm theo chiều ngược (từ dưới lên).
• Hỗ trợ tìm kiếm chính xác hoặc gần đúng.

Công thức hàm XLOOKUP trong Excel

Cú pháp chuẩn của hàm XLOOKUP

• = XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Giải thích chi tiết các đối số trong hàm XLOOKUP

lookup_value (Giá trị cần tìm kiếm) – Bắt buộc

Đây là giá trị mà bạn muốn tìm trong bảng dữ liệu. Nó có thể là một con số, một chuỗi văn bản hoặc một tham chiếu ô.

Nếu bạn bỏ qua đối số này, XLOOKUP sẽ tự động tìm kiếm các ô trống trong lookup_array. Điều này hữu ích trong một số trường hợp đặc biệt khi xử lý dữ liệu thiếu.

lookup_array (Mảng tìm kiếm) – Bắt buộc

Đây là phạm vi hoặc mảng các ô mà XLOOKUP sẽ quét để tìm giá trị phù hợp với lookup_value. Lookup_array có thể là một cột hoặc một hàng tùy thuộc vào cách bố trí dữ liệu. Lưu ý rằng độ dài của lookup_array phải phù hợp với return_array để đảm bảo kết quả đúng.

return_array (Mảng trả về) – Bắt buộc

Đây là phạm vi hoặc mảng chứa giá trị mà bạn muốn XLOOKUP trả về sau khi tìm thấy lookup_value. return_array có kích thước phải tương ứng với lookup_array (ví dụ: cùng số dòng hoặc cùng số cột).

if_not_found (Giá trị khi không tìm thấy) – Tùy chọn

Đây là giá trị mà bạn muốn hiển thị nếu lookup_value không tìm thấy trong lookup_array.

Nếu bạn không cung cấp đối số if_not_found, Excel sẽ trả về lỗi #N/A mặc định. Nếu muốn hiển thị thông điệp thân thiện hơn như “Không tìm thấy dữ liệu”, bạn nên thiết lập đối số này.

match_mode (Chế độ khớp) – Tùy chọn

Đối số này xác định cách so khớp giá trị khi tìm kiếm:

• 0 – Khớp chính xác tuyệt đối. Nếu không tìm thấy, trả về lỗi #N/A. Đây là thiết lập mặc định.
• -1 – Nếu không tìm thấy khớp chính xác, XLOOKUP sẽ tìm giá trị nhỏ hơn gần nhất.
• 1 – Nếu không tìm thấy khớp chính xác, XLOOKUP sẽ tìm giá trị lớn hơn gần nhất.
• 2 – Cho phép sử dụng ký tự đại diện (*, ?, ~) trong tìm kiếm.

Việc chọn đúng match_mode đặc biệt quan trọng khi làm việc với dữ liệu không hoàn toàn trùng khớp hoặc cần tìm kiếm linh hoạt.

search_mode (Chế độ tìm kiếm) – Tùy chọn

Chế độ này xác định cách XLOOKUP quét qua lookup_array:

• 1 – Tìm kiếm theo thứ tự từ đầu đến cuối (mặc định).
• -1 – Tìm kiếm theo thứ tự ngược lại, bắt đầu từ cuối mảng.
• 2 – Tìm kiếm nhị phân trong mảng đã được sắp xếp theo thứ tự tăng dần (nhanh hơn, nhưng yêu cầu dữ liệu phải được sắp xếp).
• -2 – Tìm kiếm nhị phân trong mảng đã sắp xếp theo thứ tự giảm dần.

Nếu sử dụng chế độ tìm kiếm nhị phân (2 hoặc -2) nhưng lookup_array chưa được sắp xếp, kết quả trả về có thể sai hoặc không hợp lệ.

Hướng dẫn cách sử dụng XLOOKUP qua các ví dụ thực tế

Ví dụ 1: Tra cứu mã điện thoại quốc gia dựa trên tên quốc gia

Giả sử bạn có một danh sách các quốc gia ở cột B (B2:B11) và mã quốc gia tương ứng ở cột D (D2:D11). Khi nhập tên quốc gia vào ô F2, bạn muốn trả về mã điện thoại tương ứng.

• Công thức sử dụng: =XLOOKUP(F2, B2:B11, D2:D11)

Trong trường hợp này:

• lookup_value: Ô F2 chứa tên quốc gia cần tìm.
• lookup_array: Phạm vi B2:B11 chứa danh sách tên quốc gia.
• return_array: Phạm vi D2:D11 chứa mã quốc gia cần trả về.

Không cần chỉ định tham số match_mode vì mặc định XLOOKUP đã thực hiện tìm kiếm khớp chính xác. Đây là điểm thuận lợi lớn so với VLOOKUP, vốn phải chỉ định thêm giá trị FALSE để yêu cầu tìm kiếm chính xác.

Ví dụ 2: Tra cứu đồng thời tên nhân viên và phòng ban dựa trên ID nhân viên

Bạn có bảng dữ liệu ID nhân viên ở cột B (B5:B14), tên nhân viên ở cột C và phòng ban ở cột D. Khi nhập ID vào ô B2, bạn muốn trả về cả tên nhân viên và tên phòng ban tương ứng.

• Công thức sử dụng: =XLOOKUP(B2, B5:B14, C5:D14)

Điểm đặc biệt:

Không giống như VLOOKUP chỉ trả về một cột dữ liệu mỗi lần, XLOOKUP có khả năng trả về nhiều cột cùng lúc.

• lookup_value là ô B2 chứa ID nhân viên cần tìm.
• lookup_array là cột B5:B14.
• return_array là phạm vi C5:D14, tức cả hai cột Tên nhân viên và Phòng ban.

Nếu sử dụng VLOOKUP trong trường hợp này, bạn sẽ phải dùng hai công thức riêng biệt, mỗi công thức trả về một cột. Nhưng với XLOOKUP, chỉ cần một công thức duy nhất, tiết kiệm thời gian và hạn chế lỗi.

Ví dụ 3: Thêm đối số if_not_found để xử lý khi không tìm thấy kết quả

Tiếp tục cùng ví dụ tra cứu nhân viên ở trên, lần này bạn muốn nếu ID nhập vào không tồn tại thì hiển thị thông báo “Không tìm thấy nhân viên” thay vì lỗi #N/A.

Công thức sử dụng:

• =XLOOKUP(B2, B5:B14, C5:D14, "Không tìm thấy nhân viên")

Giải thích:

• Đối số if_not_found đã được thêm vào với giá trị là chuỗi văn bản “Không tìm thấy nhân viên”.
• Khi không có ID phù hợp trong danh sách, công thức sẽ trả về thông báo thân thiện thay vì lỗi, giúp bảng tính chuyên nghiệp hơn.

Ví dụ 4: Tra cứu thuế suất dựa trên thu nhập qua chế độ tìm kiếm gần đúng

Giả sử bạn có bảng quy định thuế suất:

• Thu nhập tối đa ở cột C (C2:C7).
• Thuế suất tương ứng ở cột B (B2:B7).

Bạn muốn tra cứu thuế suất phù hợp dựa trên thu nhập nhập vào ô E2. Nếu không tìm thấy giá trị khớp tuyệt đối, bạn muốn trả về mức thuế suất tiếp theo lớn hơn.

Công thức sử dụng:

• =XLOOKUP(E2, C2:C7, B2:B7, 0, 1, 1)

Chi tiết công thức:

• lookup_value: Ô E2 chứa thu nhập cần xác định thuế suất.
• lookup_array: Phạm vi C2:C7 chứa mức thu nhập tối đa.
• return_array: Phạm vi B2:B7 chứa các thuế suất.
• if_not_found: Nếu không tìm thấy thu nhập phù hợp, trả về 0.
• match_mode = 1: Cho phép tìm kiếm gần đúng (nếu không có giá trị bằng đúng thì tìm giá trị lớn hơn gần nhất).
• search_mode = 1: Tìm kiếm từ trên xuống dưới theo thứ tự bình thường.

Trường hợp này mô phỏng việc xác định mức thuế suất dựa trên thu nhập, rất hữu ích trong lập trình bảng tính tài chính hoặc tính lương tự động.

Ưu điểm vượt trội của XLOOKUP so với VLOOKUP, HLOOKUP và INDEX-MATCH

Linh hoạt hơn về hướng tra cứu

Không bị giới hạn hướng tìm kiếm như VLOOKUP (luôn phải từ trái sang phải) hay HLOOKUP (phải tìm theo chiều ngang).

Không còn phụ thuộc vào số cột

Bạn không cần chỉ định thứ tự cột như với VLOOKUP, mà đơn giản chỉ cần chọn vùng dữ liệu cần tìm và vùng dữ liệu trả về.

Giảm thiểu lỗi khi chỉnh sửa bảng

Khi thêm, xoá cột trong bảng dữ liệu, công thức XLOOKUP vẫn hoạt động ổn định, không bị lỗi tham chiếu như VLOOKUP.

Dễ đọc, dễ hiểu hơn

Công thức XLOOKUP ngắn gọn, trực quan, dễ đọc, dễ chỉnh sửa hơn so với tổ hợp INDEX-MATCH.

Những lỗi phổ biến khi dùng XLOOKUP và cách khắc phục

• Sai thứ tự vùng lookup và return: Luôn đảm bảo rằng lookup_array và return_array phải cùng kích thước và tương ứng từng dòng hoặc cột.
• Không khai báo if_not_found: Nếu không thiết lập, khi không tìm thấy giá trị, Excel sẽ trả về lỗi #N/A. Hãy sử dụng tham số if_not_found để tránh điều này.
• Dùng nhầm match_mode: Nếu cần khớp gần đúng, đừng quên chọn -1 hoặc 1 phù hợp.
• Search_mode không đúng yêu cầu: Nếu bạn cần tìm từ cuối bảng, nhớ chọn search_mode = -1.

Tạm kết

Hàm XLOOKUP là một hàm hữu ích, không thể thiếu đối với những ai làm việc nhiều cùng Excel. Nhờ khả năng tra cứu linh hoạt, cú pháp gọn gàng, dễ hiểu và hiệu suất cao, XLOOKUP đã thực sự thay đổi cách chúng ta xử lý và khai thác dữ liệu. Việc thành thạo XLOOKUP sẽ giúp bạn tiết kiệm thời gian và tăng hiệu quả công việc. Mong rằng những gì FPT Shop vừa chia sẻ sẽ hữu ích cho bạn.

Nếu bạn đang cần tìm một thiết bị học tập hoặc làm việc văn phòng, hãy tham khảo ngay các mẫu laptop dành riêng cho học sinh sinh viên tại FPT Shop. FPT Shop hiện đang có rất nhiều chương trình ưu đãi hấp dẫn, sản phẩm đa dạng, giá tốt và hỗ trợ trả góp linh hoạt.

Xem thêm về các sản phẩm laptop học sinh sinh viên tốt nhất hiện nay tại đây:

• Laptop sinh viên

Xem thêm:

• Hướng dẫn chi tiết cách dùng hàm VSTACK và HSTACK trong Excel dành cho người mới
• Bỏ túi ngay cách tính đơn giá trong Excel đơn giản, dễ nhớ, giúp tăng hiệu quả làm việc
• 10 hàm tính toán trong Excel có thể giúp bạn xử lý lượng lớn dữ liệu trong nháy mắt