Lọc Cột Kết Quả Vlookup trong Google Sheets (Ví dụ về Công thức)

GiGiKing·Tháng tư 1, 2024·0 comments

Dùng Filter với các cột kết quả Vlookup trong Google Sheets mang lại nhiều lợi ích.

Ví dụ như loại bỏ những ô trống, loại bỏ giá trị không mong muốn từ hàng được tìm kiếm và áp dụng toán tử so sánh.

Hãy cùng tìm hiểu trong bài hướng dẫn mới này.

Giới thiệu

Theo ý kiến của tôi, trước đây, Vlookup trong Google Sheets vượt trội hơn so với phiên bản Excel. Bạn có thể đồng ý hoặc không đồng ý với quan điểm này.

Sự linh hoạt của Vlookup trong Google Sheets để đáp ứng yêu cầu tìm kiếm là vô tận. Có ba lý do chính cho điều này.

  1. Hàm chấp nhận biểu thức trong tất cả các đối số.
  2. Sự có mặt của các hàm mảng như Filter, Sort, Query mà chúng ta có thể sử dụng trong hoặc ngoài Vlookup (để tìm hiểu về các hàm này, vui lòng tham khảo hướng dẫn về hàm của tôi).
  3. Google Sheets có chức năng ArrayFormula thay vì công thức mảng Ctrl + Shift + Enter truyền thống trong Excel để làm việc với các mảng.

Một lưu ý nhỏ, chúng ta sẽ tận dụng tốt tính năng thứ hai trên để lọc các cột kết quả Vlookup trong Google Sheets.

Điểm thứ 3 trên đã thay đổi trò chơi.

Bởi vì, trong Google Sheets, chúng ta có thể tạo ra mảng ảo bằng cách sử dụng Dấu ngoặc nhọn. Điều này mang lại vô số khả năng tinh chỉnh phạm vi tìm kiếm.

Nhưng sau đó, Excel đã đuổi kịp bằng cách giới thiệu XLOOKUP và các chức năng mảng động như Filter và Sort (tuy không có trong tất cả các phiên bản).

Bây giờ Excel cũng đủ năng lực để đáp ứng tất cả yêu cầu tìm kiếm.

Bạn có thể quan tâm đến: So sánh Công thức Vlookup trong Excel và Google Sheets.

Dữ liệu mẫu

Để lọc kết quả Vlookup, chúng ta phải biết cách nhận giá trị từ tất cả các cột trong hàng tìm kiếm.

Tôi đã trình bày sự giống nhau ở đây – Làm thế nào để trả về nhiều giá trị bằng Vlookup trong Google Sheets?

Muốn xem ví dụ? Dưới đây là một ví dụ!

Công thức chính:

=ArrayFormula(vlookup(A1,A4:H9,column(B3:H3),0))

Sample Data for Testing the Combo in Sheets

  • search_key – A1, là “Item 2”
  • range – A4:H9 (phạm vi tìm kiếm cho search_key trong cột đầu tiên).
  • index – column(B3:H3), là các cột từ 2 đến 8.

Công thức trên trả về kết quả gồm 7 cột.

Hãy xem cách chúng ta có thể lọc các cột kết quả Vlookup trong Google Sheets để đáp ứng các yêu cầu khác nhau.

Ví dụ về việc lọc cột kết quả Vlookup trong Google Sheets

Dưới đây là một số ví dụ công thức dưới ba tiêu đề con.

Lọc các ô trống từ cột kết quả Vlookup

Nếu bạn thay thế search_key trong ô A1 bằng “Item 3”, công thức chính sẽ trả về kết quả sau đây.

      2  5  8

Hãy áp dụng Bộ lọc để loại bỏ các ô trống trong các cột kết quả Vlookup (loại bỏ ArrayFormula vì nó không cần thiết với Filter).

=filter(
     vlookup(A1,A4:H9,column(B3:H3),0),
     len(vlookup(A1,A4:H9,column(B3:H3),0))
)

Filter Vlookup Result Columns in Google Sheets - Example

Điều này hữu ích khi bạn muốn nhận kết quả từ ‘N’ cột đầu tiên không rỗng trong Vlookup. Để làm điều đó, chỉ cần bao gồm Array_Constrain cùng với nó.

=array_constrain(
     filter(
        vlookup(A1,A4:H9,column(B3:H3),0),
        len(vlookup(A1,A4:H9,column(B3:H3),0))
     ),1,N
)

Thay ‘N’ bằng 1 để nhận giá trị đầu tiên, 2 để nhận hai giá trị đầu tiên, và cứ tiếp tục.

Trước đây, chúng ta đã sử dụng một công thức khác chỉ có khả năng trả về giá trị không rỗng đầu tiên sau một tìm kiếm dọc. Đây là hướng dẫn đó – Di chuyển cột chỉ báo nếu ô trống trong Vlookup trong Google Sheets.

Đó là một công thức Vlookup lồng nhau.

Áp dụng so sánh

Sự phối hợp giữa Filter và Vlookup có nhiều ưu điểm, và đây là một ví dụ khác sử dụng toán tử so sánh.

Hãy diễn giải nội dung của bảng trên như sau.

  • A4:A9 – các mặt hàng bán hàng.
  • B4:H9 – số lượng bán hàng của các mặt hàng trong A4:A9 từ Chủ nhật đến Thứ Bảy.

Giả sử bạn muốn tìm kiếm “Mặt hàng 6” trong cột A và trả về các giá trị bán hàng nhỏ hơn 5.

=filter(
     vlookup(A1,A4:H9,column(B3:H3),0),
     vlookup(A1,A4:H9,column(B3:H3),0)<5
)

Nó sẽ làm điều đó.

Lọc các cột kết quả Vlookup để loại bỏ các giá trị không mong muốn

Thực tế, trong hai ví dụ trên, chúng ta đã học cách loại bỏ các giá trị không mong muốn.

Để tinh chỉnh kết quả thêm nữa, chúng ta có thể sử dụng các toán tử so sánh khác hoặc Regex.

Trong ba ví dụ công thức dưới đây, bạn hãy chú ý đến ví dụ thứ ba sử dụng biểu thức chính quy.

Ví dụ 1

Để lọc các cột kết quả Vlookup cho giá trị khác 5.

=filter(
     vlookup(A1,A4:H9,column(B3:H3),0),
     vlookup(A1,A4:H9,column(B3:H3),0)<>5
)

Ví dụ 2

Chỉ trả về giá trị 5.

=filter(
     vlookup(A1,A4:H9,column(B3:H3),0),
     vlookup(A1,A4:H9,column(B3:H3),0)=5
)

Ví dụ 3

Đây là một ví dụ nâng cao hơn.

Chúng ta có thể sử dụng Regexmatch để lọc các cột kết quả Vlookup bằng nhiều điều kiện. Công thức này chỉ trả lại các giá trị 1 và 10.

=filter(
     vlookup(A1,A4:H9,column(B3:H3),0),
     regexmatch(vlookup(A1,A4:H9,column(B3:H3),0)&"","^1$|^10$")
)

Nếu các cột kết quả chứa văn bản, thay thế 1, 10 bằng các chuỗi tương ứng.

Bạn có thể thêm nhiều điều kiện bằng cách phân tách chúng bằng dấu | như ^apple$|^orange$|^banana$.

Để trả về các giá trị khác 1 và 10 sau một Vlookup, sử dụng giá trị FALSE.

=filter(
     vlookup(A1,A4:H9,column(B3:H3),0),
     regexmatch(vlookup(A1,A4:H9,column(B3:H3),0)&"","^1$|^10$")=FALSE
)

Liên quan: Regexmatch trong Tiêu chí Lọc trong Google Sheets.

Lọc cột kết quả Vlookup và lỗi N/A

Tất cả các công thức trên có thể trả về lỗi #N/A trong hai trường hợp. Đó là (1) khi search_key của Vlookup không có trong cột đầu tiên trong phạm vi hoặc (2) khi Filter không tìm thấy kết quả.

Giả sử giá trị trong ô A1 là “Gold.” Không có mặt hàng như vậy trong A4:A9.

Vì vậy, Vlookup sẽ trả về bảy lỗi #N/A vì chúng ta sử dụng nhiều cột chỉ mục (kết quả) trong công thức. Nếu bạn sử dụng Filter với Vlookup, bạn sẽ chỉ thấy một lỗi.

Khi sử dụng công thức Ví dụ 2 trên và điều kiện/trạng thái trong ô A1 là “Item 4”, Filter sẽ trả về lỗi trên. Điều này xảy ra vì điều kiện không khớp trong các cột kết quả Vlookup.

Để loại bỏ các lỗi đó và nhận giá trị tùy chỉnh, bọc công thức bên ngoài với IFNA.

=ifna(cong_thuc,"thong_bao")

Đó là tất cả. Cảm ơn đã đọc bài viết này. Chúc bạn vui vẻ!

GiGiKing

Related posts