Trong bài hướng dẫn này, chúng ta sẽ tìm hiểu cách sử dụng công thức Vlookup để tìm kiếm một phần của một câu trong một danh sách từ trong Google Sheets.
Khác biệt so với công thức VLOOKUP thông thường
Chúng ta đã sử dụng “Câu” như một biểu tượng trong tiêu đề. Điều này có nghĩa là độ dài của khóa tìm kiếm có thể lớn hơn giá trị trong cột tìm kiếm.
Vì vậy, công thức Vlookup thông thường sẽ không hoạt động ở đây.
Ví dụ, “South Dakota Apples” là khóa tìm kiếm để sử dụng trong Vlookup.
Chúng ta muốn tìm kiếm trong một cột chứa danh sách các địa điểm; North Carolina, South Dakota và North Dakota; và lấy ra tên thành phố từ cột kế tiếp.
Nếu bạn tách câu (khóa tìm kiếm) ra và sử dụng các đầu ra như các khóa tìm kiếm, nó sẽ không trả về kết quả chính xác. Vì vậy, chúng ta phải tuân theo một phương pháp hoàn toàn khác.
Đó là những gì chúng ta sẽ tìm hiểu trong bài hướng dẫn này.
Thực ra, để Vlookup một câu trong một danh sách từ, chúng ta sẽ không sử dụng công thức Vlookup vì nó không hỗ trợ điều này.
Có một phương pháp giải quyết sẽ thậm chí xử lý nhiều khóa tìm kiếm (câu) cùng một lúc!
Phương pháp làm việc bằng cách sử dụng Countif, Filter, Len, Sort, Index và Ifna
Chúng ta có thể sử dụng hai loại công thức để Vlookup một câu trong một danh sách từ trong Google Sheets.
Một là công thức không mảng có thể xử lý một khóa tìm kiếm.
Nếu bạn có nhiều khóa tìm kiếm, bạn phải sao chép-dán công thức này xuống.
Loại thứ hai là công thức mảng có thể xử lý nhiều khóa tìm kiếm và lấp đầy kết quả của nó.
Cả hai công thức đều sử dụng sự kết hợp của Countif, Filter, Len, Sort, Index và Ifna.
Ngoài ra, công thức mảng sử dụng hàm trợ giúp Lambda để lấp đầy kết quả.
Công thức kéo xuống để Vlookup một câu trong một danh sách từ
Tất cả các hàm trong sự kết hợp này đều có vai trò của riêng mình.
Dưới đây là công thức không mảng để Vlookup (tìm kiếm theo chiều dọc) một câu trong một từ. Chèn nó vào ô D2 và sao chép-dán xuống càng xa bạn muốn.
=ifna(index(sort(filter(B2:B,countif(C2,"*"&A2:A&"*")),filter(Len(A2:A),countif(C2,"*"&A2:A&"*")),0),1))
Giải thích công thức
Logic của công thức này bắt đầu từ COUNTIF và cú pháp của nó như sau.
COUNTIF(range, criterion)Nếu bạn đếm A2:A (Countif criterion) với các ký tự đại diện trong ô C2 (Countif range), bạn sẽ nhận được #1 cho tất cả các giá trị khớp một phần với A2:A.
Để kiểm tra, sử dụng =ArrayFormula(countif(C2,"*"&A2:A&"*")) trong ô F2 (chúng ta cần ArrayFormula khi kiểm tra nó độc lập).
FILTER sẽ lọc B2:B những giá trị khớp với kết quả Countif = 1.
=filter(B2:B,countif(C2,"*"&A2:A&"*"))Chúng ta đã loại bỏ ArrayFormula với Countif vì nó nằm trong một công thức mảng khác, tức là Filter.
Dường như đủ để Vlookup một câu trong một từ trong Google Sheets một cách ngắn gọn. Nhưng không phải vậy!
Khi sử dụng giá trị C2, mọi thứ ổn. Nhưng nếu bạn sử dụng giá trị C3, tức là “West Virginia Blueberries,” Countif sẽ khớp với Virginia và West Virginia.
Vì vậy, Filter sẽ xuất ra hai tên thành phố, Richmond và Charleston.
Sự khớp tốt nhất của khóa tìm kiếm “West Virginia Blueberries” là “West Virginia”, không phải “Virginia”. Vì vậy, chúng ta mong đợi Charleston là kết quả.
Giải pháp là SORT kết quả Filter (B2:B) dựa trên độ dài của các giá trị A2:A tương ứng theo thứ tự giảm dần.
Vì vậy, chúng ta sẽ nhận được kết quả tốt nhất ở đầu đầu ra đã lọc.
=sort(filter(B2:B,countif(C2,"*"&A2:A&"*")),filter(Len(A2:A),countif(C2,"*"&A2:A&"*")),0)INDEX trích xuất giá trị đầu tiên. IFNA được sử dụng để trả về giá trị trống nếu kết quả khớp câu Vlookup trả về #N/A.
Công thức lấp đầy xuống để Vlookup một câu trong một danh sách từ
Câu hỏi ở đây là; làm thế nào để chuyển công thức không mảng “Vlookup một câu trong một danh sách từ” thành công thức mảng?
Ngày nay, điều đó đơn giản vì chúng ta có thể làm điều đó bằng cách sử dụng các hàm Lambda. Ở đây chúng ta sẽ sử dụng MAP.
Chúng ta muốn sử dụng tất cả các khóa tìm kiếm trong Vlookup, nghĩa là trong công thức workaround.
Phạm vi tương ứng là C2:C. Vì vậy, chúng ta có thể sử dụng công thức sau đây trong ô D2 (D2:D phải trống trước đó).
=Map(C2:C,lambda(r,ifna(index(sort(filter(B2:B,countif(r,"*"&A2:A&"*")),filter(Len(A2:A),countif(r,"*"&A2:A&"*")),0),1))))Đó là tất cả về cách Vlookup một câu trong một danh sách từ trong Google Sheets.
Hy vọng bạn tìm thấy nó hữu ích. Cảm ơn vì đã đọc. Chúc bạn có những trải nghiệm thú vị!
