Trong bài hướng dẫn này, tôi sẽ giới thiệu tất cả các mẹo và thủ thuật cần thiết để thành thạo chức năng Vlookup trong Google Sheets.
Có tổng cộng 10 biến thể khác nhau (1 cơ bản và 9 nâng cao) của Google Sheets Vlookup được bao gồm.
Hãy học cách sử dụng cơ bản của Google Sheets Vlookup thông qua các ví dụ đơn giản của tôi. Tôi hy vọng ngay cả người mới bắt đầu cũng có thể hiểu.
Sau đó, hãy tìm hiểu các mẹo và thủ thuật và biến thể công thức liên quan đến chức năng phổ biến Vlookup.
Cú pháp và các đối số của hàm
Cú pháp
VLOOKUP(từ_khóa_tìm_kiếm, phạm_vi, chỉ_số, [đã_sắp_xếp])
Giải thích các đối số
- từ_khóa_tìm_kiếm – Giá trị (văn bản, số, ngày, thời gian, v.v.) cần tìm kiếm.
- phạm_vi – Phạm vi / mảng để tìm kiếm.
- chỉ_số – Số cột của giá trị cần trả về. Cột đầu tiên trong phạm vi được đánh số là 1.
- đã_sắp_xếp – Đây là một đối số tùy chọn và mặc định là TRUE, cho biết cột cần tìm kiếm (cột đầu tiên của phạm vi đã chỉ định) đã được sắp xếp. FALSE (chưa sắp xếp) được khuyến nghị trong hầu hết các trường hợp.
Bạn đã chuyển từ Excel sang Sheets?
Nếu vậy, đừng quên đọc bài so sánh này giữa Sheets Vlookup và Excel Vlookup – So sánh Công thức Vlookup trong Excel và Google Sheets.
Từ A đến Z với Vlookup trong Google Sheets
Tôi đã cố gắng hết sức để bao gồm tất cả các biến thể công thức Vlookup có thể có trong bài hướng dẫn này. Hãy bắt đầu.
Ví dụ cơ bản
Bạn là người mới? Đây là điểm bắt đầu hoàn hảo!
Không cần phải nói, Vlookup trong Google Sheets là một trong những chức năng đầu tiên mà bạn nên học để trở thành người dùng Sheets thành thạo.
Mục đích của Vlookup trong ứng dụng bảng tính
Bạn có thể sử dụng chức năng này để tìm kiếm theo chiều dọc trong cột đầu tiên của bảng hoặc phạm vi dữ liệu với từ_khóa_tìm_kiếm và trả về câu trả lời từ một cột bạn chỉ định. Không hiểu? Tôi sẽ giải thích.
Tôi muốn tìm điểm “Bài kiểm tra 2” của “Học sinh 3”. Ở đây, tôi có thể sử dụng “Học sinh 3” làm từ_khóa_tìm_kiếm cho Vlookup trong Google Sheets và chỉ số “Bài kiểm tra 2” là 3.
Sử dụng Vlookup, chúng ta có thể trích xuất điểm 86. Làm thế nào? Sử dụng “Học sinh 3” làm khóa tìm kiếm trong Vlookup trong Google Sheets và số cột (chỉ số) là 3.
Công thức Vlookup cơ bản:
=vlookup("Học sinh 3",A2:C5,3,FALSE)
Công thức này có thể trả về điểm 86.
Đừng quên chỉ định đối số cuối cùng là FALSE. Điều này cho biết phạm vi không được sắp xếp và cần khớp chính xác.
Nếu phạm vi của bạn đã được sắp xếp, bạn có thể sử dụng TRUE hoặc bỏ qua đối số cuối cùng. Nhưng tùy chọn tốt nhất là luôn luôn sử dụng FALSE. Xem thêm một ví dụ cơ bản khác về hàm Vlookup trong Google Sheets.
Công thức trên tìm kiếm theo cột đầu tiên các từ khóa tìm kiếm “Chuối” (trong ô D2) và trả về “Giá” tương ứng từ Cột 2.
Tìm kiếm các từ khóa từ một phạm vi – Mẹo nâng cao của Vlookup
Chức năng Vlookup trong Google Sheets có thể chứa nhiều từ khóa tìm kiếm. Ví dụ, tôi muốn tìm giá của “Táo” và “Xoài” trong ví dụ dưới đây. Làm thế nào để làm điều đó?
Hãy sử dụng tham chiếu phạm vi từ khóa tìm kiếm và sau đó chỉ cần bọc công thức bằng hàm ArrayFormula như dưới đây.
Nếu bạn muốn chỉ định nhiều từ khóa tìm kiếm trong công thức Vlookup, đặt các từ khóa tìm kiếm nhiều trong các Dấu ngoặc nhọn.
=ArrayFormula(vlookup({"Xoài";"Táo"},A2:B5,2,FALSE))
Tương tự: Làm thế nào để sử dụng Vlookup để Trả về Kết quả Mảng trong Google Sheets
Trả về toàn bộ một dòng – Mẹo nâng cao của Vlookup
Không giống như những gì nhiều người nghĩ, bạn có thể sử dụng hàm Vlookup để tìm kiếm xuống cột đầu tiên bằng từ khóa tìm kiếm và trả về giá trị của toàn bộ dòng của ô khớp. Làm thế nào để làm điều đó?
Ví dụ về Công thức Vlookup trả về toàn bộ một dòng:
Chỉ cần nhập tất cả các số cột được phân tách bằng dấu phẩy và bao quanh chúng bằng Dấu ngoặc nhọn / Dấu ngoặc nhọn. Cuối cùng, bọc toàn bộ công thức bằng hàm ArrayFormula.
Bạn có thể làm cho công thức Vlookup này linh hoạt hơn. Công thức trên sẽ có lỗi nếu bạn chèn một cột hoặc nhiều cột mới giữa cột A và cột D.
Công thức Vlookup trong Google Sheets này có thể giải quyết vấn đề này. Nó hoàn toàn linh hoạt. {1,2,3,4} trong công thức trên được thay thế bằng công thức Thứ tự_cột(A2:D5).
=ArrayFormula(vlookup("Học sinh 2",A2:E5,Thứ_tự_cột(A2:D5),FALSE))
Công thức Column trả về số 1, 2, 3, 4 (A, B, C, D).
Chủ đề tương tự: Có thể sử dụng nhiều giá trị bằng cách sử dụng Vlookup [Làm thế nào để]
Biến thể trong Chỉ số cột – Mẹo nâng cao của Vlookup
Bạn có thể di chuyển chỉ số cột cùng với cột. Ví dụ, tôi muốn trả lại giá trị từ cột 4. Nhưng nếu tôi chèn một cột mới, số cột này có thể trở thành 5.
Làm thế nào để có được chỉ số cột cố định trong công thức Vlookup trong Google Sheets?
Để có một chỉ số cột cố định trong Vlookup của Sheets, hãy sử dụng hàm Columns, không phải hàm Column, trong chỉ số.
Hàm COLUMNS đếm số cột trong phạm vi A2: D2 và trả về 4.
Nếu bạn muốn giá trị cột thứ ba, công thức sẽ như sau.
=vlookup("Học sinh 2",A2:D5,Columns(A2:D2)-1,FALSE)
Vlookup hướng sang trái – Mẹo nâng cao của Vlookup
Từ khóa tìm kiếm trong Vlookup phải luôn luôn từ cột đầu tiên. Đó là tiêu chuẩn và bạn không thể thay đổi điều đó. Nhưng có một cách để làm điều đó. Từ khóa tìm kiếm trong Vlookup của tôi nằm ở cột thứ tư. Tôi có thể làm gì?
Đây là giải pháp để sử dụng bất kỳ cột nào trong Vlookup trong Google Sheets làm cột từ khóa tìm kiếm.
Các bước để thực hiện:
- Đổi vị trí cột bằng các Dấu ngoặc nhọn như dưới đây.
Giả sử phạm vi gốc của bạn là A2: D4 và từ khóa tìm kiếm của bạn là “Học sinh 3” từ cột 4. Vậy hãy đưa cột thứ tư đó đến cột đầu tiên. Sau đó mới Vlookup làm việc.
Bạn có thể sắp xếp lại vị trí cột bên trong chính công thức Vlookup trong Google Sheets như dưới đây mà không làm thay đổi vị trí cột thực tế.
{D2:D5,A2:C5}
Bây giờ xem công thức Vlookup.
=vlookup("Học sinh 3",{D2:D5,A2:C5},2,FALSE)
Công thức trả về điểm “Bài kiểm tra 1” của “Học sinh 3”.
Ở công thức Vlookup Sheets này, số chỉ mục cột là 2. Theo dữ liệu thực tế, đó là cột 1. Nhưng tôi đã sắp xếp lại cột. Bây giờ cột 4 chưa tên của học sinh là cột 1 và cột điểm “Bài kiểm tra 1” là cột 2.
Sự nhạy cảm với hoa thường – Mẹo nâng cao của Vlookup
Để thực hiện một Vlookup phân biệt chữ hoa chữ thường trong Google Sheets, bạn nên phụ thuộc vào một công thức kết hợp liên quan đến Vlookup và một số công thức khác.
Hàm Query trong Google Sheets có phân biệt chữ hoa. Bạn có thể sử dụng tính linh hoạt của chức năng này ở đây.
Ví dụ về công thức:
Lần này, tôi muốn chia sẻ dữ liệu mẫu với bạn. Xem ô A4 và A5 trong bộ dữ liệu này.
Các giá trị giống nhau. Nhưng theo phân biệt hoa thường, chúng là các giá trị khác nhau, phải không?
- Giá trị trong ô A4: “IL102B”
- Giá trị trong ô A5: “IL102b”
Ở đây, tôi muốn trả lại Qty. (cột 5) của mục “IL102b”. Công thức Vlookup này có trả về kết quả chính xác không?
=vlookup("IL102b",A2:G5,5,FALSE)
Không phải! Vlookup mặc định không phân biệt chữ hoa. Vì vậy, nó sẽ trả về Qty. của mục “IL102B” vì nó là sự xuất hiện đầu tiên trong cột A.
Giải pháp:
Như tôi đã nói, hàm Query phân biệt chữ hoa. Vì vậy, trước tiên, chúng ta có thể lọc phạm vi dữ liệu A2: G5 như dưới đây.
=query(A2:G5,"Select * where A='IL102b'")
Công thức này sẽ trả lại hàng chứa “IL102b” trong Cột A. Vì vậy, bạn có thể sử dụng Query này như phạm vi trong Vloookup như dưới đây.
=vlookup("IL102b",query(A2:G5,"Select * where A='IL102b'"),5,FALSE)
Có một số giải pháp khác nữa. Hãy đọc chi tiết ở đây – Vlookup phân biệt chữ hoa [Đã giải quyết]
Tên phạm vi – Mẹo nâng cao của Vlookup
Tôi có thể sử dụng tên cho phạm vi trong Vlookup trong Google Sheets phải không?
Có thể! Bạn có thể sử dụng tên đã đặt cho phạm vi trong Vlookup. Đây là một ví dụ.
Để đặt tên một phạm vi, đầu tiên hãy chọn phạm vi mà bạn muốn đặt tên. Ở đây phạm vi là A2: D5. Sau đó từ menu> Dữ liệu, bạn có thể chọn “Phạm vi có tên” và đặt tên phạm vi. Ở đây tôi đặt tên phạm vi A2: D5 là “doanh_số”
Các công thức Vlookup dưới đây trả lại cùng một kết quả.
Công thức Vlookup mà không có Tên phạm vi:
=vlookup("Học sinh 3",A2:D5,4,false)
Công thức Vlookup với Tên phạm vi:
=vlookup("Học sinh 3",doanh_số,4,false)
Kết hợp từ khóa tìm kiếm và Điều kiện khác – Mẹo nâng cao của Vlookup
Trong ví dụ dưới đây, tôi muốn tìm giá “Xi măng” của nhà cung cấp “Z”. Bạn có thể sử dụng từ khóa tìm kiếm như sau trong Vlookup.
"Xi măngZ"
Thêm vào đó, trong phạm vi công thức, kết hợp các cột chứa mục “Xi măng” và Tên nhà cung cấp “Z”.
A2: A7 & B2: B7
Công thức:
=ArrayFormula(vlookup("Xi măngZ",{A2:A7 & B2:B7,C2:C7},2,FALSE))
Khi bạn sử dụng Dấu ngoặc nhọn, hãy bao bọc công thức Vlookup bằng hàm ArrayFormula. Tóm lại, bạn có thể sử dụng loại Vlookup này để chứa nhiều từ khóa tìm kiếm, gọi là tiêu chí.
Chi tiết hơn: Làm thế nào để sử dụng VLOOKUP với nhiều điều kiện trong Google Sheets [Đã giải quyết]
Vlookup hình ảnh – Mẹo nâng cao của Vlookup
Không có gì là không thể với Vlookup hình ảnh trong Google Doc Sheets.
Tôi muốn tìm “Mì gà” trong Cột A và trả lại hình ảnh trong Cột E, tức là cột thứ năm.
=VLOOKUP("Mì gà",A2:E3,5,FALSE)
Công thức không có thay đổi. Nếu bạn muốn biết cách chèn hình ảnh và thay đổi kích thước phù hợp với một ô, các liên kết dưới đây có thể chỉ bạn.
- Cách sử dụng hàm Hình ảnh và Vlookup Hình ảnh trong Google Sheets.
- Chèn hình ảnh vào ô mà không có công thức trong Google Sheets.
Ký tự đại diện – Mẹo nâng cao của Vlookup
Làm thế nào để sử dụng ký tự đại diện trong Vlookup trong Google Sheets?
Tôi chỉ đang bao gồm một ví dụ cơ bản. Giả sử tôi muốn tìm một mã sản phẩm “Y-10005” trong cột A và trả lại một giá trị từ Cột B.
Công thức Vlookup này có thể trả về #N/A.
=vlookup("Y",A2:B6,2,FALSE)
Sử dụng ký tự đại diện sao với từ_khóa_tìm_kiếm “Y”.
=vlookup("Y*",A2:B6,2,FALSE)
Nếu Từ khóa Tìm kiếm nằm trong ô khác, ví dụ trong ô D1, sử dụng công thức sau để bao gồm ký tự đại diện sao trong Vlookup.
=vlookup(D1 & "*",A2:B6,2,FALSE)
Có nhiều ký tự đại diện khác mà bạn có thể sử dụng trong hàm Vlookup của Google Sheets.
Kết luận
Bạn có thể làm theo hướng dẫn dưới đây để biết thêm về các ký tự đại diện có sẵn trong Google Sheets.
Nên đọc: Cách sử dụng các ký tự đại diện trong các công thức Google Sheets.
Nếu bạn tìm kiếm trên trang web này, bạn có thể tìm thấy nhiều ví dụ về công thức Vlookup để sử dụng trong thực tế của mình.
Đó là tất cả về việc sử dụng công thức Vlookup trong Google Sheets. Chúc bạn vui vẻ!
