Cách sử dụng hàm VLOOKUP trong Google Sheets với các ví dụ cụ thể

Khi làm việc với các dữ liệu liên quan đến nhau, một trong những thách thức phổ biến nhất là tìm kiếm thông tin trên nhiều sheets khác nhau. Bạn thường thực hiện những công việc như vậy trong cuộc sống hàng ngày, chẳng hạn như khi quét bảng lịch trình chuyến bay cho số chuyến bay để biết thời gian và trạng thái khởi hành. Hàm VLOOKUP trong Google Sheets hoạt động theo cách tương tự với khả năng tra cứu và truy xuất dữ liệu phù hợp từ một bảng khác trên cùng một sheets hoặc từ một sheets khác.

Có thể vẫn còn nhiều người cho rằng Vlookup là một trong những hàm khó và ít người biết đến. Nhưng trên thực tế, rất dễ dàng để thực hiện các phép tính toán thông qua hàm VLOOKUP trong Google Sheets và dưới đây thông qua những ví dụ cụ thể sẽ giúp các bạn hiểu rõ hơn về hàm này và cách sử dụng chi tiết.

Hàm VLOOKUP trong Google Sheets cú pháp và cách sử dụng

Hàm Vlookup trong Google Sheets được thiết kế để thực hiện tra cứu theo chiều dọc, tìm kiếm giá trị khóa (mã định danh duy nhất) xuống cột đầu tiên trong một phạm vi được chỉ định và trả về một giá trị trong cùng một hàng từ cột khác.

Cú pháp cho hàm Vlookup như sau:

VLOOKUP(search_key, range, index, [is_sorted])

Trong đó 3 đối số đầu tiên là bắt buộc, đối số cuối cùng là tùy chọn:

• Search_key: Là giá trị cần tìm kiếm (giá trị tra cứu hoặc mã định danh duy nhất). Ví dụ: bạn có thể tìm kiếm từ "apple", số 10 hoặc giá trị trong ô A2.
• Range: Phạm vi hai hoặc nhiều cột dữ liệu cho tìm kiếm. Hàm VLOOKUP của Google Sheets luôn tìm kiếm trong cột đầu tiên của phạm vi .
• Index: Số cột trong phạm vi mà từ đó giá trị phù hợp (giá trị trong cùng hàng với search_key) sẽ được trả về.
• Is_sorted: Cho biết cột tra cứu có được sắp xếp (TRUE) hay không (FALSE). Trong hầu hết các trường hợp, FALSE được khuyến nghị.

+ Nếu is_sorted là TRUE hoặc bị bỏ qua (mặc định), cột đầu tiên của phạm vi phải được sắp xếp theo thứ tự tăng dần, tức là từ A đến Z hoặc từ nhỏ nhất đến lớn nhất.

Trong trường hợp này, hàm Vlookup trả về một kết quả gần đúng. Chính xác hơn, nó tìm kiếm kết quả phù hợp chính xác trước tiên. Nếu không tìm thấy đối sánh chính xác, hàm sẽ tìm kiếm đối sánh gần nhất nhỏ hơn hoặc bằng search_key. Nếu tất cả các giá trị trong cột tra cứu lớn hơn khóa tìm kiếm, thì sẽ trả về lỗi #N/A.

+ Nếu is_sorted là FALSE, thì không cần sắp xếp. Trong trường hợp này, hàm Vlookup tìm kiếm kết quả phù hợp chính xác. Nếu cột tra cứu chứa 2 hoặc nhiều giá trị chính xác bằng search_key, giá trị đầu tiên được tìm thấy sẽ được trả về.

Thoạt nhìn, cú pháp có vẻ hơi phức tạp, nhưng ví dụ về hàm Vlookup của Google Sheets dưới đây sẽ giúp mọi thứ dễ hiểu hơn.

Giả sử bạn có hai bảng: Bảng chính và bảng tra cứu như hình ảnh dưới đây. Các bảng đều có một cột chung (Order ID) là một mã định danh duy nhất. Bạn muốn kéo trạng thái của từng đơn hàng từ bảng tra cứu sang bảng chính.

Bây giờ, làm thế nào để sử dụng hàm Vlookup trong Google Sheets cho nhiệm vụ này? Để bắt đầu, hãy xác định các đối số cho công thức Vlookup:

• Search_key: Là Order ID (A3), giá trị được tìm kiếm trong cột đầu tiên của bảng Tra cứu.
• Range: Phạm vi bảng tra cứu ($F$3:$G$8). Lưu ý rằng chúng tôi khóa phạm vi bằng cách sử dụng tham chiếu ô tuyệt đối vì dự định sẽ sao chép công thức sang nhiều ô.
• Index là 2: Vì cột Status mà từ đó chúng ta sẽ trả về kết quả khớp là cột thứ 2 trong phạm vi.
• Is_sorted là FALSE: Vì cột tìm kiếm của chúng ta (F) không được sắp xếp.

Đặt tất cả các đối số lại với nhau, chúng ta sẽ nhận được công thức như sau:

=VLOOKUP(A3,$F$3:$G$8,2,false)

Nhập công thức này vào ô đầu tiên (D3) của bảng chính, sao đó sao chép xuống tất cả các ô của cột D và bạn sẽ nhận được kết quả tương tự như sau:

Nếu bạn vẫn cảm thấy hàm Vlookup còn khó hiểu. Vậy thi hãy cùng Gitiho tiếp tục tìm hiểu rõ hơn.

Xem thêm: Hướng dẫn cách phân biệt lệnh VLOOKUP và lệnh HLOOKUP trong Excel

5 điều cần biết về hàm Vlookup trong Google Sheets

Như bạn đã biết, hàm Vlookup của Google Sheets là một hàm linh hoạt với rất nhiều tùy chọn. Vì vậy hãy ghi nhớ năm điều đơn giản đưới đây sẽ giúp bạn không gặp rắc rối và tránh được hầu hết các lỗi Vlookup phổ biến.

• Hàm Vlookup trong Google Sheets không thể “nhìn” sang bên trái của nó, mà luôn tìm kiếm ở cột đầu tiên (ngoài cùng bên trái) của phạm vi. Để thực hiện các phép tính với Vlookup từ bên trái, hãy sử dụng hàm Index Match trong Google Sheets.
• Vlookup trong Google Sheets sẽ không phân biệt chữ thường và chữ in hoa. 
• Nếu hàm Vlookup trả về kết quả không chính xác, hãy đặt đối số is_sorted thành FALSE để trả về kết quả phù hợp chính xác.
• Khi is_sorted được đặt thành TRUE hoặc bị bỏ qua, hãy nhớ sắp xếp cột đầu tiên của phạm vi theo thứ tự tăng dần. Trong trường hợp này, hàm VLOOKUP sẽ sử dụng thuật toán tìm kiếm nhị phân nhanh hơn chỉ hoạt động chính xác trên dữ liệu được sắp xếp.
• Hàm Vlookup trong Google Sheets có thể tìm kiếm khớp một phần dựa trên các ký tự đại diện: dấu hỏi (?) và dấu hoa thị (*).

Cách sử dụng hàm Vlookup thông qua các ví dụ 

Bây giờ bạn đã hiểu ý tưởng cơ bản về cách hoạt động của hàm Vlookup trong Google Sheets, đã đến lúc thử tự tạo một vài công thức. 

Sử dụng hàm Vlookup từ một sheets khác

Trong bảng tính sử dụng thực tế, bảng chính (Main table) và bảng tra cứu (Lookup table) thường bố trí ở hai sheets khác nhau trong một bảng dữ liệu Google Sheets (như trong hình dưới đây). 

Để tham chiếu công thức Vlookup của bạn đến một sheets khác trong cùng một bảng tính, hãy đặt tên bảng tính theo sau là dấu chấm than (!) trước tham chiếu phạm vi. Ví dụ như công thức dưới đây:

=VLOOKUP(A2,Sheet4!$A$2:$B$7,2,false)

Công thức sẽ tìm kiếm giá trị trong A2 với phạm vi là A2:A7 trên Sheet4 và trả về giá trị phù hợp từ cột B (cột thứ 2 trong phạm vi).

Nếu tên sheets bao gồm khoảng trắng hoặc các ký tự không phải chữ cái, hãy đảm bảo đặt nó trong dấu ngoặc kép. Ví dụ:

=VLOOKUP(A2,'Lookup table'!$A$2:$B$7,2,false)

Mẹo: Thay vì nhập tham chiếu đến một sheets khác theo cách thủ công, bạn có thể yêu cầu Google Sheets tự động chèn. Đối với điều này, hãy bắt đầu nhập công thức Vlookup và khi được yêu cầu nhập đối số phạm vi, hãy chuyển sang sheets tra cứu và chọn phạm vi bằng chuột. 

Thao tác này sẽ thêm tham chiếu phạm vi vào công thức và bạn chỉ phải thay đổi tham chiếu tương đối (mặc định) thành tham chiếu tuyệt đối. Để thực hiện việc này, hãy nhập dấu $ trước ký tự cột và số hàng, hoặc chọn tham chiếu và nhấn F4 để chuyển đổi giữa các loại tham chiếu khác nhau.

Sử dụng hàm Vlookup với các ký tự đại diện

Trong trường hợp bạn không biết toàn bộ giá trị tra cứu (search_key), nhưng biết một phần của nó, bạn có thể thực hiện tra cứu với các ký tự đại diện sau:

• Dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào.
• Dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự nào.

Giả sử bạn muốn truy xuất thông tin về một đơn đặt hàng cụ thể từ bảng bên dưới nhưng lại không thể nhớ lại đầy đủ thứ tự ID mà chỉ nhớ ký tự đầu tiên bắt đầu là "A". Vì vậy, có thể sử dụng dấu hoa thị (*) để điền vào phần còn thiếu, lúc này công thức sẽ như sau:

=VLOOKUP("a*",$A$2:$C$7,2,false)

Nhưng tốt nhất bạn có thể nhập phần đã biết của khóa tìm kiếm (search key) vào một số ô và nối ô đó với "*" để tạo thành công thức Vlookup linh hoạt hơn như dưới đây.

Để xác định chủng loại của bảng dữ liệu bên dưới bạn sử dụng công thức: =VLOOKUP($F$1&"*",$A$2:$C$7,2,false)

Để xác định đơn giá bạn sử dụng công thức: =VLOOKUP($F$1&"*",$A$2:$C$7,3,false)

Mẹo: Nếu bạn cần tìm kiếm một dấu chấm hỏi hoặc ký tự dấu hoa thị thực sự, hãy đặt dấu ngã (~) trước ký tự, ví dụ: "~ *".

Sử dụng hàm Index Match cho Vlookup bên trái

Một trong những hạn chế đáng kể nhất của hàm Vlookup (cả trong Excel và Google Sheets) là không thể “nhìn” sang bên trái. Tức là, nếu cột tìm kiếm không phải là cột đầu tiên trong bảng tra cứu, Vlookup của Google Sheets sẽ bị lỗi. Trong trường hợp như vậy, hãy sử dụng hàm Index Match mạnh mẽ hơn:

Công thức tổng quát sẽ như sau:

INDEX (return_range,MATCH(search_key, lookup_range, 0)) 

Ví dụ: Để tra cứu giá trị A3 (search_key) trong phạm vi G3:G8 (lookup_range) và trả về kết quả khớp từ F3:F8 (return_range), hãy sử dụng công thức sau:

=INDEX($F$3:$F$8,MATCH(A3, $G$3:$G$8, 0))

Như hình ảnh dưới đây cho thấy cách hàm Index Match làm việc.

 

Một ưu điểm khác của hàm Index Match so với hàm Vlookup là nó “miễn nhiễm” với các thay đổi cấu trúc bạn thực hiện trong trang tính vì nó tham chiếu trực tiếp đến cột trả về. Đặc biệt, việc chèn hoặc xóa một cột trong bảng tra cứu sẽ phá vỡ công thức Vlookup vì số chỉ mục "được mã hóa cứng" trở nên không hợp lệ, trong khi hàm Index Match vẫn an toàn.

Xem thêm: Hướng dẫn hàm VLOOKUP trong Excel cho người mới bắt đầu: Học với các ví dụ

Sử dụng hàm Vlookup phân biệt chữ hoa chữ thường 

Trong trường hợp văn bản quan trọng, hãy sử dụng hàm Index Match kết hợp với các hàm TRUE và EXACT để tạo công thức mảng Vlookup trong Google Sheets khi cần phân biệt chữ hoa chữ thường.

Công thức tổng quát sẽ như sau:

ArrayFormula(INDEX(return_range, MATCH (TRUE,EXACT(lookup_range, search_key),0)))

Giả sử khóa tìm kiếm nằm trong ô A3, phạm vi tra cứu là G3:G8 và phạm vi trả về là F3:F8, công thức sẽ như sau:

=ArrayFormula(INDEX($F$3:$F$8, MATCH (TRUE,EXACT($G$3:$G$8, A3),0)))

Giống như kết quả hiển thị trong hình ảnh ở phía dưới, công thức không có vấn đề gì với việc phân biệt các ký tự viết hoa và viết thường như A-1001 và a-1001.

Mẹo: Nhấn tổ hợp phím Ctrl + Shift + Enter trong khi chỉnh sửa công thức sẽ tự động chèn hàm Arrayformula vào đầu công thức.

Hàm Vlookup là cách phổ biến nhất nhưng không phải là cách duy nhất để tra cứu trong Google Sheets. Phần tiếp theo sẽ là những giải pháp thay thế hiệu quả hơn. 

Sử dụng tiện ích Merge Sheets trong Google Sheets 

Nếu bạn đang tìm kiếm một cách trực quan không cần sử dụng hàm Vlookup trong Google Sheets để thực hiện các phép tính thì có thể xem xét sử dụng tiện ích bổ sung Merge Sheets. Tiện ích này được cung cấp miễn phí trên Google Sheets add-ons store.

Sau khi tải và cài đặt tiện ích bổ sung vào Google Sheets, bạn sẽ tìm thấy tiện ích này trong tab Add-ons.

Với tiện ích bổ sung Merge Sheets, bạn có thể dễ dàng xác định việc lấy thông tin từ cột Status dựa trên Order ID. Cách thực hiện như sau:

Bước 1: Chọn bất kỳ ô nào có dữ liệu trong sheets Mains của bảng dữ liệu Google Sheets (như trong hình ở trên) và truy cập vào menu Add-ons > Merge Sheets > Start.

Trong hầu hết các trường hợp, tiện ích bổ sung sẽ tự động chọn toàn bộ bảng dữ liệu cho bạn. Nếu không, hãy bấm vào nút Auto select hoặc chọn phạm vi trong sheets Mains theo cách thủ công, rồi bấm Next.

Bước 2: Chọn phạm vi trong sheets Lookup sheet như hình ảnh ở trên. Phạm vi không nhất thiết phải có cùng kích thước với phạm vi trong sheets Mains. Như trong ví dụ này, bảng tra cứu có nhiều hàng hơn bảng chính 2 hàng.

Bước 3: Chọn một hoặc nhiều cột chính (số nhận dạng duy nhất) để so sánh. Vì chúng tôi đang so sánh các sheets theo Order ID nên sẽ chỉ chọn cột này.

Bước 4: Trong cột Lookup columns, hãy chọn các cột trong sheets tra cứu (Lookup sheet) mà bạn muốn truy xuất dữ liệu. Trong cột chính (Main columns), hãy chọn các cột tương ứng trong sheets mà bạn muốn sao chép dữ liệu vào rồi bấm nút Next.

Trong ví dụ này, chúng tôi đang kéo thông tin từ cột Status của sheets tra cứu Lookup sheet vào cột Status của sheets có tên Main sheet.

Bước 5: Sang bước tiếp theo, hãy chọn một hoặc nhiều hành động bổ sung. Thông thường, bạn tích vào tùy chọn Add non-matching rows to the end of the main table để muốn thêm các hàng không khớp vào cuối bảng chính, tức là sao chép các hàng chỉ tồn tại trong bảng tra cứu Lookup sheet vào cuối bảng chính (Main). Sau đó bấm nút Next.

Bước 6: Bấm nút Finish để tiện ích bổ sung Merge Sheets xử lý và hoàn tất.

Xem thêm: Cách viết công thức VLOOKUP đơn giản, dễ hiểu qua các bước ví dụ

Sử dụng tiện ích Multiple VLOOKUP Matches trong Google Sheets

Multiple VLOOKUP Matches là một công cụ khác của Google Sheets để tra cứu nâng cao. Như tên gọi của nó, phần bổ trợ có thể trả về tất cả các kết quả phù hợp, không chỉ là kết quả đầu tiên như hàm VLOOKUP thực hiện. Hơn nữa, nó có thể đánh giá nhiều điều kiện, tra cứu theo bất kỳ hướng nào và trả về tất cả hoặc số lượng kết hợp được chỉ định dưới dạng giá trị hoặc công thức.

Bây giờ hãy xem cách tiện ích bổ sung này hoạt động trên dữ liệu thực tế như thế nào. Giả sử có một số đơn đặt hàng trong bảng mẫu như hình dưới đây chứa một số mục và bạn muốn lấy tất cả các mục của một đơn đặt hàng cụ thể. 

Mặc định một hàm Vlookup không thể làm được điều này, trong khi hàm QUERY mạnh hơn có thể. Vấn đề là chức năng này yêu cầu kiến thức về ngôn ngữ truy vấn hoặc ít nhất là cú pháp SQL, trong khi bạn lại không rành về nó. Vì vậy cài đặt và sử dụng tiện ích bổ sung Multiple VLOOKUP Matches sẽ là giải pháp đơn giản và nhanh nhất trong trường hợp này. 

Trong bảng dữ liệu Google Sheets của bạn, truy cập vào menu Add-ons > Multiple VLOOKUP Matches > Start và xác định tiêu chí tra cứu:

Bước 1: Chọn phạm vi (Source Range) với dữ liệu của bạn (A1: D9).

Bước 2: Chỉ định có bao nhiêu kết quả phù hợp để trả về (tất cả trong trường hợp như trong ví dụ này) tại mục Return.

Bước 3: Chọn các cột để trả về dữ liệu (Item, Amount and Status).

Bước 4: Đặt một hoặc nhiều điều kiện. Chúng tôi muốn lấy thông tin về đầu vào số thứ tự trong F2, vì vậy chỉ định cấu hình một điều kiện là: Order ID = F2.

Bước 5: Chọn ô trên cùng bên trái cho kết quả.

Bước 6: Bấm nút Preview result để xem trước kết quả đảm bảo rằng bạn nhận được chính xác những đang tìm kiếm.

Bước 7: Nếu tất cả đều ổn, chỉ cần bấm nút Insert formula hoặc để hoàn tất.

Đối với ví dụ này, chúng tôi chọn trả về kết quả phù hợp dưới dạng công thức. Vì vậy, bây giờ bạn có thể nhập bất kỳ số thứ tự nào vào F2 và công thức như hình ảnh bên dưới sẽ tự động tính toán lại cho phù hợp.

Trên đây Gitiho vừa hướng dẫn các bạn chi tiết cách sử dụng hàm VLOOKUP trong Google Sheets. Đồng thời chỉ ra cách sử dụng các công thức Vlookup giải quyết các tác vụ cụ thể khi sử dụng trong thực tế.

Cuối cùng, đừng quên tham khảo khóa học Google Sheets của Gitiho bạn nhé! Hẹn gặp lại các bạn trong những bài viết tiếp theo.