Chào các bạn! Hôm nay mình sẽ chia sẻ với các bạn về cách sử dụng hàm XLOOKUP trong Google Sheets. Đây là một trong những hàm quan trọng giúp tìm kiếm giá trị trong một phạm vi dựa trên hàng hoặc cột. Hãy cùng tìm hiểu chi tiết về hàm này nhé!
Hàm XLOOKUP trong Google Sheets là gì?
Hàm XLOOKUP trong Google Sheets được sử dụng để tìm kiếm giá trị trong một phạm vi dựa trên hàng hoặc cột. Ví dụ, bạn có thể sử dụng nó để tìm kiếm “Mango” trong một cột và trả về giá của nó từ một cột khác. Ngoài ra, bạn cũng có thể tìm kiếm “Ben” trong một hàng và trả về điểm số của anh ta từ một hàng khác.
Trước đây, chúng ta đã sử dụng các hàm VLOOKUP và HLOOKUP để thực hiện hai hoạt động này. Tuy nhiên, nhược điểm của các hàm này là chỉ có thể tìm kiếm xuống cột đầu tiên (VLOOKUP) hoặc hàng đầu tiên (HLOOKUP) trong phạm vi. XLOOKUP không có giới hạn này, do đó nó có thể được sử dụng để tìm kiếm giá trị trong bất kỳ cột hoặc hàng nào trong phạm vi.
Trong XLOOKUP, chúng ta có thể chỉ định xem chúng ta muốn tìm kiếm chính xác, giá trị lớn nhất tiếp theo, giá trị nhỏ nhất tiếp theo, tìm kiếm theo ký tự đại diện, tìm kiếm từ mục nhập đầu tiên đến mục nhập cuối cùng hoặc tìm kiếm từ mục nhập cuối cùng đến mục nhập đầu tiên, v.v.
Ví dụ, công thức XLOOKUP sau sẽ tìm kiếm “Wheat” trong cột A và trả về giá trị trong cột C từ vị trí được tìm thấy.
XLOOKUP("Wheat",A:A,C:C,"Không có sẵn")
Cú pháp của hàm XLOOKUP trong Google Sheets
Cú pháp của hàm XLOOKUP trong Google Sheets như sau:
XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])
So với các hàm khác trong Google Sheets, chúng ta cần chú ý đến các đối số của hàm XLOOKUP. Điều này là vì nó có sáu đối số, nhiều hơn hầu hết các hàm khác.
Mình đã sắp xếp các giải thích về các đối số dưới hai tiêu đề (nhóm) dưới đây:
- Các đối số yêu cầu của hàm XLOOKUP: search_key, lookup_range và result_range.
- Các đối số tùy chọn của hàm XLOOKUP: missing_value, match_mode và search_mode.
Với hiểu biết cơ bản về các đối số này, chúng ta sẽ có thể sử dụng hàm XLOOKUP một cách dễ dàng trong các bảng tính của mình.
Các đối số bắt buộc trong hàm XLOOKUP
Ba đối số đầu tiên trong hàm XLOOKUP là bắt buộc. Dưới đây là mục đích của chúng và một số ví dụ công thức XLOOKUP sử dụng chúng.
Các đối số cơ bản là:
- search_key: Giá trị cần tìm kiếm. Ví dụ: 156, “Apple”, date(2022,09,20), hoặc “ABC100”.
- lookup_range: Phạm vi được xem xét để tìm kiếm (một cột duy nhất cho tìm kiếm dọc và một hàng duy nhất cho tìm kiếm ngang).
- result_range: Phạm vi được xem xét để trả về kết quả.
Vì chúng ta không chỉ định các đối số tùy chọn trong cú pháp hàm XLOOKUP, công thức sẽ lấy giá trị mặc định của chúng. Điều này có nghĩa là:
- Nếu không có search_key, công thức XLOOKUP sẽ trả về giá trị lỗi #N/A.
- Nó sẽ thực hiện một kết quả khớp chính xác với search_key; không phải kết quả cho giá trị nhỏ nhất hoặc lớn nhất kế tiếp.
- Nó sẽ tìm kiếm từ mục nhập đầu tiên đến mục nhập cuối cùng trong phạm vi lookup_range, từ trên xuống dưới trong XLOOKUP dọc và từ trái sang phải trong XLOOKUP ngang.
Chúng ta sẽ sử dụng dữ liệu sau đây (vui lòng xem Hình 1 bên dưới) để kiểm tra hàm XLOOKUP với ba đối số bắt buộc.
Trước khi tiếp tục, nếu bạn chưa quen với việc sử dụng tiêu chí ngày, thời gian, văn bản, số hoặc ngày thời gian trong XLOOKUP, hãy kiểm tra bài hướng dẫn HLOOKUP của tôi. Dưới phần cú pháp có một bảng bạn có thể tham khảo.
Các ví dụ công thức
-
Công thức F4:
=XLOOKUP(G2,B2:B7,D2:D7)
Hàm XLOOKUP tìm kiếm giá trị “barley” (G2) trong khoảng B2:B7 (phạm vi tìm kiếm) và trả về giá trị từ khoảng D2:D7 (phạm vi kết quả) trong hàng trùng khớp. Nếu chúng ta xem xét dữ liệu trên là sản xuất các loại ngũ cốc năm 2021 và 2022, công thức sẽ trả về số lượng sản xuất của “barley” trong năm 2022. -
Công thức F6:
=XLOOKUP(G2,B2:B7,C2:D7)
Phạm vi kết quả bao gồm hai cột, do đó công thức XLOOKUP sẽ trả về giá trị từ cả hai cột. Đây là một ví dụ về việc sử dụng mảng 2D trong phạm vi kết quả trong hàm XLOOKUP trong Google Sheets. Kết quả của công thức sẽ là số lượng sản xuất của “barley” trong năm 2021 và 2022, trong cùng một hàng. -
Công thức F12:
=ARRAYFORMULA(XLOOKUP(G9:G10,B2:B7,D2:D7))
Đây là một ví dụ về việc sử dụng nhiều khóa tìm kiếm trong hàm XLOOKUP trong Google Sheets. Công thức tìm kiếm các giá trị “barley” và “rye” (G9:G10) trong khoảng B2:B7 (phạm vi tìm kiếm) và trả về các giá trị từ phạm vi D2:D7 (phạm vi kết quả) trong các hàng trùng khớp. Công thức trả về số lượng sản xuất của “barley” và “rye” trong năm 2022.
Khi sử dụng nhiều khóa tìm kiếm trong hàm XLOOKUP, bạn phải sử dụng hàm ARRAYFORMULA cùng với nó. Điều này cho biết cho Google Sheets trả về một mảng giá trị, đó là những gì bạn cần khi sử dụng nhiều khóa tìm kiếm trong hàm XLOOKUP.
- Công thức F15:
=ARRAYFORMULA(XLOOKUP(G9:G10,B2:B7,C2:D7))
Ở đây, phạm vi kết quả XLOOKUP bao gồm hai cột. Tuy nhiên, vì có nhiều hơn một khóa tìm kiếm, công thức chỉ trả về các giá trị từ cột đầu tiên. Điều này là do hàm XLOOKUP không thể trả về các mảng 2D.
Chúng ta có thể giải quyết vấn đề của XLOOKUP không trả về một mảng 2D bằng cách sử dụng một phương pháp phụ để giữ tất cả các biến thể XLOOKUP. Phương pháp này liên quan đến việc sử dụng hàm CHOOSEROWS với hàm XMATCH. Bạn có thể tìm thấy một hướng dẫn về cách thực hiện điều này tại đây: Hàm CHOOSEROWS trong Google Sheets.
Hãy hiểu rõ những điều trên trước khi tiếp tục đối số tùy chọn bên dưới.
Các đối số tùy chọn trong hàm XLOOKUP
Ba đối số tùy chọn cho hàm XLOOKUP là:
- missing_value: Giá trị trả về nếu không tìm thấy khớp với search_key.
- match_mode: Loại khớp để thực hiện trong lookup_range.
- search_mode: Cách tìm kiếm qua lookup_range.
Dưới đây là cách sử dụng chúng trong các công thức XLOOKUP:
Giá trị bị thiếu và các ví dụ
missing_value là một đối số tùy chọn trong hàm XLOOKUP trong Google Sheets. Nếu loại trừ đối số này, hàm sẽ mặc định trả về #N/A nếu không tìm thấy khớp.
Công thức XLOOKUP sau sẽ trả về “Có vẻ như có lỗi đánh máy!” nếu search_key không được tìm thấy trong khoảng B2:B7.
xlookup(G2,B2:B7,D2:D7,"Có vẻ như có lỗi đánh máy!")
Mình có ba khóa tìm kiếm và một trong số chúng không khớp. Vậy về missing_value trong tình huống này?
Xin vui lòng xem ảnh dưới đây (Hình 2).
Chế độ khớp trong hàm XLOOKUP trong Google Sheets
match_mode trong hàm XLOOKUP xác định cách tìm khớp cho search_key, đó là giá trị bạn đang tìm kiếm.
0: Khớp chính xác (mặc định).
1: Khớp chính xác hoặc giá trị tiếp theo lớn hơn search_key.
-1: Khớp chính xác hoặc giá trị tiếp theo nhỏ hơn search_key.
2: Khớp với ký tự đại diện.
Công thức ví dụ:
Trong các ví dụ sau, chúng ta có các ngày phỏng vấn trong cột B và tên các ứng viên trong cột C.
Hãy sử dụng hàm XLOOKUP để tìm ứng viên vào một ngày cụ thể. Nếu không có ứng viên nào có sẵn vào ngày đó, hãy trả về ứng viên vào ngày trước đó hoặc ngày tiếp theo.
Công thức:
=XLOOKUP(D3,B3:B7,C3:C7,”Không có cuộc hẹn”,1)
22/09/2021 (ô D3) là ngày phải khớp trong cột B. Như bạn có thể thấy, nó có sẵn trong cột B. Do đó, bất kể match_mode được sử dụng là 0 (khớp chính xác), 1 (giá trị tiếp theo lớn nhất), -1 (giá trị tiếp theo nhỏ nhất) hoặc 2 (khớp với ký tự đại diện), công thức sẽ trả về tên “Erik”.
Thay đổi search_key thành 24/09/2021. Công thức khớp chính xác sau đây sẽ trả về “Không có cuộc hẹn”.
=XLOOKUP(D3,B3:B7,C3:C7,"Không có cuộc hẹn",0)
Nếu bạn thay đổi tham số match_mode của hàm XLOOKUP thành 1, kết quả sẽ là “Russel”, và -1 sẽ trả về “Ben”.
Làm thế nào về khớp với ký tự đại diện trong hàm XLOOKUP trong Google Sheets?
Hàm XLOOKUP hỗ trợ ba ký tự đại diện: dấu sao (*), dấu chấm hỏi (?) và dấu gạch ngang (~).
Nhiều người sai lầm cho rằng XLOOKUP chỉ hỗ trợ hai ký tự đại diện: dấu sao và dấu chấm hỏi. Dưới đây là cách sử dụng dấu gạch ngang trong hàm XLOOKUP trong Google Sheets.
Chế độ tìm kiếm trong hàm XLOOKUP trong Google Sheets
Đối số search_mode trong hàm XLOOKUP xác định cách tìm kiếm qua lookup_range, đó là phạm vi các ô mà hàm XLOOKUP tìm kiếm search_key.
Dưới đây là 4 chế độ tìm kiếm.
1: Tìm từ mục nhập đầu tiên đến mục nhập cuối cùng (mặc định).
-1: Tìm từ mục nhập cuối cùng đến mục nhập đầu tiên.
2: Tìm kiếm nhị phân (lookup_range phải được sắp xếp theo thứ tự tăng dần).
-2: Tìm kiếm nhị phân (lookup_range phải được sắp xếp theo thứ tự giảm dần).
Công thức ví dụ:
Trong số bốn chế độ tìm kiếm, 1 và -1 rất hữu ích khi bạn có nhiều sự xuất hiện của các khóa tìm kiếm trong phạm vi tìm kiếm.
Chế độ tìm kiếm nhị phân 2 và -2 trong hàm XLOOKUP là phần khó hiểu nhất. Bạn có thể hiểu nó thông qua bảng dưới đây.
Lưu ý: Đầu tiên, sắp xếp dữ liệu theo thứ tự tăng dần (A-Z) hoặc giảm dần (Z-A). Tùy theo đó, sử dụng chế độ tìm kiếm 2 hoặc -2.
Bảng: A1:B4 (Thứ tự tăng dần [A-Z])
23/9/21 | Ben
23/9/21 | Russell
26/9/21 | Andrew
26/9/21 | Charles
Nếu search_key là 24/09/2021 trong chế độ tìm kiếm nhị phân 2, ngày gần nhất với search_key sẽ là 23/09/2021 khi match_mode là -1 (giá trị tiếp theo nhỏ nhất) hoặc 26/09/2021 khi match_mode là 1 (giá trị tiếp theo lớn nhất).
Ví dụ 1:
=XLOOKUP(DATE(2021,9,24),A1:A4,B1:B4,”Not Available”,-1,2)
Kết quả: “Russel”
Ví dụ 2:
=XLOOKUP(DATE(2021,9,24),A1:A4,B1:B4,”Not Available”,1,2)
Kết quả: “Andrew”
Bảng: A1:B4 (Thứ tự giảm dần [Z-A])
26/9/21 | Charles
26/9/21 | Andrew
23/9/21 | Russel
23/9/21 | Ben
Xin vui lòng xem các ngày được in đậm cho khớp gần nhất với khóa tìm kiếm 24/09/2021. Chế độ tìm kiếm nhị phân là -2.
xmlookup(DATE(2021,9,24),A1:A4,B1:B4,"Not Available",-1,2)
XLOOKUP ngang trong Google Sheets
Hàm XLOOKUP trong Google Sheets hoạt động cũng tốt với các phạm vi tìm kiếm và kết quả ngang. Dưới đây là một ví dụ tổng hợp về các giải thích trước đó của tôi, nhưng sử dụng dữ liệu ngang.
Giả sử hàng đầu tiên của một bảng chứa khung thời gian của lịch trình dự án. Trong các hàng dưới đó, tôi có phân bổ lao động. Hãy xem cách truy xuất lao động được phân bổ vào một ngày cụ thể.
Khóa tìm kiếm là 15/07/2023 trong ô A10. Chúng tôi sẽ tìm kiếm ngày này trong B2:E2 và trả về lao động được phân bổ cho “công việc 3” từ B5:E5. Nếu không có khóa tìm kiếm nào khớp, chúng tôi sẽ lấy con số từ ngày lớn nhất tiếp theo.
Vì chúng ta có phạm vi tìm kiếm đã được sắp xếp, chúng tôi có thể sử dụng chế độ tìm kiếm nhị phân 2 hoặc chế độ tìm kiếm 1. Tất nhiên, match_mode là 1, vì chúng tôi muốn thực hiện khớp chính xác hoặc khớp với ngày lớn nhất tiếp theo.
Công thức XLOOKUP ngang:
1: =XLOOKUP(A10,B2:E2,B5:E5,””,1,2)
2: =XLOOKUP(A10,B2:E2,B5:E5,””,1,1)
Tài liệu tham khảo
Mình tin rằng mình đã bao quát mọi thứ mà người dùng bảng tính cần biết để làm chủ hàm XLOOKUP, giải pháp tìm kiếm cuối cùng trong Google Sheets. Dưới đây là một số tài liệu tham khảo bổ sung.
- XLOOKUP Visible (Filtered) Data in Google Sheets.
- XLOOKUP Nth Match Value in Google Sheets.
- Nested XLOOKUP Function in Google Sheets.
- HLOOKUP và XLOOKUP: Khác biệt chính trong Google Sheets.
- XLOOKUP với nhiều tiêu chí trong Google Sheets
- XLOOKUP cho các kết quả cột nhiều trong Google Sheets
Bạn cũng có thể sử dụng hàm FILTER hoặc hàm QUERY để lọc các hàng hoặc cột khớp với một hoặc nhiều giá trị tìm kiếm.
