Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng Vlookup để tìm kiếm một khóa tìm kiếm trong nhiều cột (trong một ma trận) trên Google Sheets. Xin lưu ý rằng phương pháp mà tôi sẽ giải thích trong bài viết này không hoạt động trên Excel.
Hãy bắt đầu với cách sử dụng Vlookup mà bạn đã quen thuộc. Điều này sẽ giúp chúng ta dễ dàng tiếp cận vấn đề.
Như bạn có thể đã biết, có bốn đối số trong hàm Vlookup. Chúng là khóa_tìm_kiếm, phạm_vi, chỉ_số (cột kết quả), và đã_sắp_xếp (dữ liệu đã được sắp xếp hay chưa).
VLOOKUP(khóa_tìm_kiếm, phạm_vi, chỉ_số, [đã_sắp_xếp])
Trong đó, thông thường, khóa_tìm_kiếm (giá trị cần tìm) phải từ cột đầu tiên trong phạm_vi.
Ví dụ như A2:D7 là phạm_vi. khóa_tìm_kiếm là “Xanh” có sẵn trong cột đầu tiên của phạm_vi.
Do đó, công thức sau sẽ trả về giá trị “Sinh viên 9” vì chỉ_số (cột kết quả) là 3.
=vlookup("Xanh", A2:D7, 3, 0)Nếu chỉ_số là 2, thì kết quả sẽ là “Sinh viên 3”.
Nếu bạn sử dụng khóa_tìm_kiếm từ bất kỳ phạm_vi nào khác, ví dụ như “Sinh viên 1”, thì công thức sẽ trả về #N/A. Tôi sẽ tập trung vào vấn đề này trong bài viết này và sẽ đề cập đến nó sau.
Bạn đã nhận ra tại sao tôi đã nhấn mạnh “thông thường, khóa tìm kiếm phải từ cột đầu tiên” chưa?
Bởi vì (khóa_tìm_kiếm) có thể từ bất kỳ cột cụ thể nào với một phương pháp tạm thời (trong bài viết này chúng ta sẽ tìm hiểu cách sử dụng khóa_tìm_kiếm từ bất kỳ cột không cụ thể nào, tức là từ một phạm_vi cột).
Hãy giả định khóa_tìm_kiếm nằm trong cột cuối cùng (một cột cụ thể) trong Vlookup. Rồi làm thế nào để thu được kết quả như trên?
Dữ liệu mẫu – Phạm_vi là A2:D7
Hãy sắp xếp lại phạm_vi bằng cách di chuyển cột cuối cùng (D2:D7) lên đầu trong công thức bằng cặp dấu ngoặc nhọn.
=vlookup("Xanh", {D2:D7, A2:C7}, 3, 0)Tương tự: Vlookup ngược trong Google Sheets [Tùy chọn công thức].
Tình huống của chúng ta (Phạm_vi tìm kiếm ma trận)
Tình huống Vlookup của chúng ta hoàn toàn khác biệt so với hai ví dụ trên.
Trong tình huống của chúng ta, chúng ta muốn tinh chỉnh Vlookup để tìm kiếm khóa_tìm_kiếm trong nhiều cột (B2:D7) và sau đó trả về giá trị từ cột đầu tiên (A2:A7).
Phạm_vi dữ liệu mẫu (A2:D7):
Tôi muốn công thức Vlookup của mình tìm kiếm qua một phạm_vi (một ma trận) để tìm khóa tìm kiếm và trả về giá trị của một ô cụ thể trong cột đầu tiên của hàng tìm thấy.
Ở đây, phạm_vi là A2:D7 và khóa_tìm_kiếm của chúng ta có thể từ bất kỳ ô nào trong phạm_vi B2:D7. Chúng ta muốn kết quả từ cột đầu tiên (A2:A7).
Hãy xem cách điều chỉnh Vlookup để tìm kiếm khóa tìm kiếm trong nhiều cột trên Google Sheets.
Cách sử dụng Vlookup với khóa tìm kiếm trong phạm vi nhiều cột
Do Vlookup không thể tìm kiếm qua nhiều cột, giải pháp là làm phẳng bộ dữ liệu (phạm_vi). Hãy xem cách làm điều này.
Dựa trên dữ liệu mẫu ở trên, phạm_vi B2:D7 (nhiều cột khóa tìm kiếm) phải được làm phẳng để tạo thành một phạm_vi cột duy nhất. Sau đó, chúng ta có thể ‘giả tưởng’ sử dụng Vlookup để tìm kiếm khóa tìm kiếm trong phạm_vi nhiều cột (sẽ chỉ còn một cột duy nhất sau khi làm phẳng).
Ví dụ dưới đây cho thấy điều gì sẽ xảy ra với một phạm_vi sau khi làm phẳng và sau đó bạn có thể tìm hiểu cách thực hiện nó.
Dữ liệu:
Sau khi làm phẳng:
Để làm phẳng dữ liệu mẫu của chúng ta (ma trận tìm kiếm Vlookup), chúng ta có thể sử dụng công thức sau.
=flatten(B2:D7)Bạn có thể tìm hiểu cách sử dụng hàm FLATTEN tại đây – Cách sử dụng hàm FLATTEN trong Google Sheets.
Tuy nhiên, chỉ việc làm phẳng phạm_vi không đủ để sử dụng Vlookup để tìm khóa tìm kiếm trong nhiều cột. Chúng ta phải xử lý cột A2:A7 nữa, cột đầu tiên.
Hãy xem bảng này (hàng đầu tiên từ dữ liệu mẫu của chúng ta).
Sau khi làm phẳng, chúng ta sẽ có được tên sinh viên trong một cột. Trong một cột khác (cột thứ hai), chúng ta muốn có giá trị trong cột đầu tiên như được hiển thị dưới đây.
Logic
Nếu chúng ta dùng bất kỳ tên sinh viên nào làm khóa_tìm_kiếm trong Vlookup, kết quả sẽ là “Xanh”. Đó là logic của việc sử dụng Vlookup để tìm khóa tìm kiếm trong nhiều cột (ma trận) trên Google Sheets.
Đây là công thức làm phẳng để sử dụng như phạm_vi Vlookup.
=ArrayFormula(split(flatten(B2:D7&"|"&A2:A7),"|"))Để hiểu công thức này, bạn có thể kiểm tra hướng dẫn tương tự của tôi – Một công thức đơn giản để làm phẳng một tập dữ liệu trong Google Sheets.
Ví dụ công thức
Bây giờ (sau khi làm phẳng), khóa_tìm_kiếm của Vlookup không còn nằm ở nhiều cột. Nó nằm ở cột đầu tiên theo tiêu chuẩn của Vlookup.
Giả sử chúng ta muốn tìm kiếm “Sinh viên 11” nằm trong ô C4 và trả về giá trị từ cột đầu tiên cùng hàng, tức là “Vàng”. Công thức để sử dụng sẽ như sau.
=vlookup("Sinh viên 11", ArrayFormula(split(flatten(B2:D7&"|"&A2:A7),"|")), 2, 0)Trong đó các đối số như sau;
khóa_tìm_kiếm– “Sinh viên 11”phạm_vi– công thức làm phẳngchỉ_số– 2 (sau khi làm phẳng chỉ còn hai cột)đã_sắp_xếp– 0 (chưa sắp xếp)
Nhiều khóa tìm kiếm trong một ma trận
Tương tự như Vlookup thông thường, chúng ta có thể sử dụng nhiều khóa tìm kiếm với Vlookup ma trận.
Liên quan: Cách sử dụng VLOOKUP với nhiều tiêu chí trên Google Sheets [Đã giải quyết].
Để tìm kiếm “Sinh viên 5”, “Sinh viên 10”, “Sinh viên 18”, sử dụng công thức như sau.
=ArrayFormula(vlookup({"Sinh viên 5";"Sinh viên 10";"Sinh viên 18"}, ArrayFormula(split(flatten(B2:D7&"|"&A2:A7),"|")), 2, 0))Bạn có thể rút gọn công thức trên bằng cách loại bỏ một trong các hàm ArrayFormula.
=ArrayFormula(vlookup({"Sinh viên 5";"Sinh viên 10";"Sinh viên 18"}, split(flatten(B2:D7&"|"&A2:A7),"|"), 2, 0))Trong ví dụ sau đây, tôi có các khóa tìm kiếm như tham chiếu ô (F2:F4). Nó tương đương với công thức Vlookup trên.
Trước khi kết thúc cách sử dụng Vlookup để tìm khóa tìm kiếm trong nhiều cột và trả về giá trị từ cột đầu tiên, cần làm rõ một điều nữa.
Chúng ta có thể sử dụng phạm_vi mở trong công thức trên. B2:D7 có thể là B2:D và A2:A7 có thể là A2:A. Nhưng khi bạn thay đổi F2:F4 thành F2:F, hãy bao gồm IFNA bên ngoài Vlookup nhưng bên trong ArrayFormula như sau.
=ArrayFormula(ifna(vlookup(F2:F, split(flatten(B2:D&"|"&A2:A),"|"), 2, 0)))Trên blog này có rất nhiều bài viết hướng dẫn Vlookup hiếm. Hãy sử dụng biểu tượng tìm kiếm trên thanh điều hướng để tìm chúng.
Hy vọng bạn đã hiểu cách tinh chỉnh Vlookup để tìm khóa tìm kiếm trong nhiều cột (ma trận) trên Google Sheets. Chúc bạn có trải nghiệm tốt!
