Cách sử dụng hàm VLOOKUP trong Google Sheets chi tiết nhất

Hàm VLOOKUP là công cụ quan trọng trong Google Sheets, giúp tra cứu và trích xuất dữ liệu nhanh chóng từ bảng tính. Trong nội dung này, bạn sẽ được tìm hiểu cách dùng VLOOKUP, các kiểu tìm kiếm, cách kết hợp với nhiều hàm khác và những lưu ý cần biết khi áp dụng trong thực tế.

Hướng dẫn chi tiết cách sử dụng hàm VLOOKUP trong Google Sheets

Hàm VLOOKUP trong Google Sheets là một công cụ dùng để tìm kiếm và lấy dữ liệu từ một bảng. Cụ thể, nó giúp bạn tìm một giá trị trong cột đầu tiên của bảng và lấy kết quả từ một cột khác trong cùng một hàng.

Cú pháp của hàm VLOOKUP là: =VLOOKUP(search_key; range; index; [is_sorted])

Giải thích chi tiết:

• search_key: Là giá trị bạn muốn tìm kiếm, có thể là số, chữ, hoặc tham chiếu đến một ô.
• range: Là vùng dữ liệu bạn sẽ tìm trong đó. Cột đầu tiên của vùng này sẽ được dùng để tìm kiếm.
• index: Chỉ số của cột trong vùng dữ liệu mà bạn muốn lấy kết quả từ đó. Cột đầu tiên có chỉ số là 1.
• [is_sorted]: Tùy chọn, mặc định là TRUE (tức là bảng đã được sắp xếp theo cột tìm kiếm). Nếu bạn muốn tìm kiếm chính xác, hãy đặt là FALSE.

Ví dụ 1: Bạn có hai bảng dữ liệu trên một bảng tính như hình. Bạn cần phải biết giá tương ứng với mã sản phẩm.

Bước 1: Nhập công thức sử dụng: =VLOOKUP(B15;A2:E9;3;FALSE) vào ô cần hiển thị kết quả.

Giải thích:

• B15: Ô chứa mã sản phẩm cần tìm
• A2:E9: Phạm vi bảng dữ liệu để dò tìm
• 3: Số thứ tự của cột Giá trong bảng dữ liệu
• FALSE (hoặc 0): Yêu cầu tìm chính xác tuyệt đối mã sản phẩm

Bước 2: Nhấn Enter để hiển thị kết quả. Kết quả ở đây là 24.490.000.

Sử dụng VLOOKUP để tra giá sản phẩm chính xác

Tìm kiếm tương đối được áp dụng khi giá trị cần dò tìm nằm trong một bảng đã được sắp xếp theo thứ tự tăng dần.

Ví dụ 2: Bạn có danh sách sinh viên gồm tên và điểm số, dựa vào bảng quy định xếp loại học lực, hãy tiến hành xếp loại tương ứng cho từng sinh viên.

Bước 1: Nhập công thức sau vào ô Xếp loại (D2): =VLOOKUP(C2;$A$10:$B$13;2;TRUE)

Giải thích:

• C2: Ô chứa điểm số cần xếp loại
• A10:B13: Bảng quy định xếp loại (đã sắp xếp tăng dần theo điểm)
• 2: Số thứ tự của cột Xếp loại trong bảng quy định
• TRUE (hoặc 1): Cho phép tìm kiếm tương đối theo khoảng giá trị

Bước 2: Nhấn Enter để hiển thị kết quả, sau đó sao chép công thức xuống các dòng còn lại để xếp loại cho toàn bộ danh sách sinh viên.

Lưu ý: Cột điểm trong bảng quy định bắt buộc phải được sắp xếp theo thứ tự tăng dần, nếu không hàm VLOOKUP sẽ trả về kết quả không chính xác.

Sử dụng hàm VLOOKUP tìm kiếm tương đối để xếp loại học lực

Trong một số trường hợp, giá trị cần dò tìm không nằm ở cột đầu tiên của bảng dữ liệu. Khi đó, ta có thể kết hợp hàm VLOOKUP với hàm QUERY để sắp xếp lại dữ liệu trước khi tra cứu.

Ví dụ 3: Bạn có bảng dữ liệu sản phẩm gồm các cột Mã sản phẩm, Tên sản phẩm, Giá, Số lượng, Tổng. Yêu cầu: Dựa vào tên sản phẩm, hãy xác định số lượng tương ứng.

Bước 1: Nhập tên sản phẩm cần tra cứu vào ô B15 (ví dụ: iPhone SE). Sau đó nhập công thức sau vào ô C15 để hiển thị kết quả: =VLOOKUP(B15;QUERY(A2:E9;"SELECT B,C,D");3;FALSE)

Giải thích:

• B15: Ô chứa tên sản phẩm cần tìm
• QUERY(A2:E9;"SELECT B,C,D")
• Trích xuất bảng dữ liệu gồm các cột Tên sản phẩm, Giá, Số lượng
• Đưa Tên sản phẩm về cột đầu tiên để VLOOKUP có thể dò tìm
• 3: Cột Số lượng trong bảng dữ liệu sau khi dùng QUERY
• FALSE (hoặc 0): Yêu cầu tìm kiếm chính xác tuyệt đối

Bước 2: Nhấn Enter để hiển thị kết quả. Kết quả thu được là 40, tương ứng với số lượng của sản phẩm iPhone SE.

Kết hợp VLOOKUP và QUERY để lấy số lượng sản phẩm

Trong một số trường hợp, bạn cần tính tổng giá trị của nhiều kết quả tra cứu cùng lúc. Khi đó, có thể kết hợp hàm VLOOKUP với ArrayFormula và SUM để xử lý dữ liệu theo dạng mảng và tính tổng tự động.

Ví dụ 4: Tính tổng doanh thu của tất cả các sản phẩm trong bảng dữ liệu sau.

Bước 1: Nhập công thức sau vào ô cần hiển thị kết quả (ô B14): =SUM(ArrayFormula(VLOOKUP(A2:A9;A2:E9;5;FALSE)))

Giải thích:

• A2:A9: Danh sách các mã sản phẩm cần tra cứu
• A2:E9: Bảng dữ liệu chứa thông tin chi tiết sản phẩm
• 5: Cột thứ 5 trong bảng dữ liệu, tương ứng với cột Tổng
• FALSE (hoặc 0): Yêu cầu tìm kiếm chính xác mã sản phẩm
• ArrayFormula: Cho phép VLOOKUP xử lý nhiều giá trị cùng lúc
• SUM: Cộng toàn bộ các giá trị trả về để tính tổng doanh thu

Bước 2: Nhấn Enter để hiển thị kết quả.

Tính tổng dữ liệu bằng VLOOKUP và SUM

Hàm IF được sử dụng cùng VLOOKUP để kiểm tra điều kiện và trả về kết quả tương ứng.

Ví dụ 5: Kiểm tra số lượng của sản phẩm có mã 1245. Nếu số lượng lớn hơn 10 thì hiển thị YES, ngược lại hiển thị NO.

Công thức sử dụng: =IF(VLOOKUP(1245;$A$2:$D$9;4;0)>10;"YES";"NO")

Giải thích: VLOOKUP tìm số lượng của sản phẩm có mã 1245, IF kiểm tra điều kiện:

• Lớn hơn 10 → YES
• Ngược lại → NO

Kết quả: Giá trị hiển thị là NO, do số lượng sản phẩm không lớn hơn 10.

Kết hợp VLOOKUP và IF để kiểm tra điều kiện

Khi làm việc với dữ liệu thực tế, ta thường gặp trường hợp thông tin được mã hóa trong chuỗi ký tự, trong đó một phần ký tự đại diện cho thông tin cụ thể. Khi đó, ta có thể kết hợp hàm LEFT hoặc RIGHT với VLOOKUP để trích xuất và tra cứu dữ liệu.

Ví dụ 6: Bạn có bảng dữ liệu sản phẩm, trong đó mã sản phẩm (MA_SP) có dạng như A1245WH, B1025BL…2 ký tự đầu của mã thể hiện hệ điều hành

Dựa vào bảng quy định bên dưới, hãy xác định hệ điều hành tương ứng cho từng sản phẩm

Công thức sử dụng:

Nhập công thức sau vào ô E2: =VLOOKUP(LEFT(A2;2);$A$13:$B$14;2;0)

Giải thích:

• LEFT(A2;2): Lấy 2 ký tự bên trái của mã sản phẩm
• A13:B14: Bảng quy định mã hệ điều hành
• 2: Cột chứa tên hệ điều hành
• 0 (FALSE): Yêu cầu tìm kiếm chính xác

Lưu ý: Nếu thông tin cần tra cứu nằm ở cuối chuỗi, hãy thay LEFT bằng RIGHT.

Tách mã sản phẩm để tra cứu bằng VLOOKUP

Ví dụ 7: Giả sử bạn chỉ biết mã sản phẩm và cần truy xuất nhanh giá bán tương ứng từ bảng dữ liệu lớn. Thay vì dò tìm trực tiếp theo cách thông thường, ta sẽ xác định vị trí của mã sản phẩm trước, sau đó lấy thông tin giá tại đúng vị trí đó.

Bước 1: Nhập công thức sau vào ô hiển thị kết quả (ô B14): =VLOOKUP(INDEX(A1:D9;MATCH(1215;A1:A9;0);1);A1:D9;3;FALSE)

Giải thích:

• MATCH(1215;A1:A9;0): Xác định vị trí dòng của mã sản phẩm 1215 trong cột A
• INDEX(A1:D9; … ;1): Lấy giá trị mã sản phẩm tương ứng tại dòng vừa tìm được
• VLOOKUP( … ;A1:D9;3;FALSE): Dò tìm mã sản phẩm và trả về giá ở cột thứ 3
• FALSE (hoặc 0): Yêu cầu tìm kiếm chính xác

Bước 2: Sau khi nhấn Enter, giá trị hiển thị là 17.490.000, đúng với giá của sản phẩm có mã 1215 (iPhone 11).

Tra cứu giá sản phẩm bằng INDEX–MATCH

Ngoài việc tra cứu dữ liệu, VLOOKUP còn có thể kết hợp với các hàm logic để kiểm tra nhiều điều kiện cùng lúc và trả về kết quả dạng Đúng/Sai (TRUE/FALSE).

Kết hợp với hàm AND

Hàm AND trả về TRUE khi tất cả các điều kiện đều đúng.

Công thức: =AND( VLOOKUP(1215;A1:D9;2;FALSE)="iPhone 11", VLOOKUP(1215;A2:D9;4;FALSE)>0)

Giải thích:

• Điều kiện 1: Sản phẩm có mã 1215 phải có tên là iPhone 11
• Điều kiện 2: Số lượng của sản phẩm lớn hơn 0

Chỉ khi cả hai điều kiện đều thỏa mãn, kết quả trả về mới là TRUE. Nếu một trong hai điều kiện sai, kết quả sẽ là FALSE.

Hàm VLOOKUP kết hợp hàm AND

Kết hợp với hàm OR

Hàm OR trả về TRUE khi ít nhất một điều kiện đúng.

Công thức: =OR(VLOOKUP(1215;A1:D9;2;FALSE)="iPhone 11", VLOOKUP(1215;A2:D9;4;FALSE)>=0)

Giải thích:

• Điều kiện 1: Tên sản phẩm là iPhone 11
• Điều kiện 2: Số lượng sản phẩm lớn hơn hoặc bằng 0
• Chỉ cần một trong hai điều kiện đúng, hàm OR sẽ trả về TRUE.

Hàm VLOOKUP kết hợp hàm OR

Hàm SUMIF được sử dụng để tính tổng các giá trị thỏa mãn một điều kiện nhất định. Khi kết hợp với VLOOKUP, hàm này giúp tổng hợp dữ liệu dựa trên kết quả tra cứu.

Ví dụ 8: Tính tổng số lượng của sản phẩm có tên là “iPhone 17” trong bảng dữ liệu sau

Công thức sử dụng

Nhập công thức sau vào ô hiển thị kết quả (ô B14): =SUMIF(B2:B9;VLOOKUP("iPhone 17";B2:E9;1;FALSE);D2:D9)

Giải thích:

• B2:B9: Vùng chứa tên sản phẩm dùng để xét điều kiện
• VLOOKUP("iPhone 17";B2:E9;1;FALSE):
• Xác định chính xác tên sản phẩm cần tính tổng
• D2:D9: Cột Số lượng dùng để cộng
• SUMIF: Thực hiện phép cộng các giá trị thỏa mãn điều kiện đã cho
• Kết quả: Giá trị hiển thị là 5, tương ứng với tổng số lượng của sản phẩm iPhone 17 trong bảng dữ liệu.

Hàm SUMIF kết hợp hàm VLOOKUP

Hàm COUNTIF được dùng để đếm số lượng giá trị thỏa mãn một điều kiện xác định. Khi kết hợp với VLOOKUP và ArrayFormula, ta có thể đếm số phần tử dựa trên kết quả tra cứu từ bảng dữ liệu.

Ví dụ 9: Đếm số sản phẩm có giá lớn hơn hoặc bằng 20.000.000.

Công thức sử dụng

Nhập công thức sau vào ô hiển thị kết quả (ô B14): =COUNTIF(ArrayFormula(VLOOKUP(C2:C9;$C$2:$D$9;1;FALSE));">=20000000")

Giải thích:

• C2:C9: Danh sách các giá trị dùng để tra cứu
• VLOOKUP(C2:C9;$C$2:$D$9;1;FALSE):
• Trả về danh sách giá sản phẩm tương ứng
• ArrayFormula: Cho phép xử lý nhiều giá trị cùng lúc
• COUNTIF(...;">=20000000"): Đếm số giá trị lớn hơn hoặc bằng 20.000.000

Kết quả trả về là 2, tương ứng với 2 sản phẩm có giá từ 20.000.000 trở lên.

Lọc và đếm sản phẩm dựa trên điều kiện giá bán

Trong thực tế, việc tra cứu dữ liệu thường không chỉ dựa trên một điều kiện đơn lẻ, mà cần đồng thời kiểm tra nhiều tiêu chí. Khi đó, ta có thể kết hợp các hàm IF, AND và VLOOKUP để xử lý và đưa ra kết quả phù hợp.

Ví dụ 10: Cho bảng dữ liệu sản phẩm gồm các cột Mã sản phẩm, Tên sản phẩm, Giá, Số lượng và Tổng.

Yêu cầu:

• Kiểm tra sản phẩm có mã 1215
• Nếu tên sản phẩm là iPhone 11 và số lượng còn lớn hơn 0 thì hiển thị trạng thái “Còn hàng”, ngược lại, hiển thị “Hết hàng”

Công thức sử dụng

Nhập công thức sau vào ô hiển thị kết quả (ô B14):

=IF(AND(VLOOKUP(1215;A1:D9;2;FALSE)="iPhone 11", VLOOKUP(1215;A1:D9;4;FALSE)>0),VLOOKUP(1215;A1:D9;2;FALSE)&" Còn hàng", "Hết hàng")

Giải thích:

VLOOKUP(1215;A1:D9;2;FALSE): Lấy tên sản phẩm ứng với mã 1215

VLOOKUP(1215;A1:D9;4;FALSE): Lấy số lượng của sản phẩm

AND(...): Kiểm tra đồng thời hai điều kiện

IF(...):

• Nếu điều kiện đúng → trả về Tên sản phẩm + “Còn hàng”
• Nếu sai → trả về “Hết hàng”
• Kết quả hiển thị là: iPhone 11 Còn hàng

Kết hợp IF, AND và VLOOKUP để kiểm tra nhiều điều kiện và xác định trạng thái sản phẩm

Khi làm việc với hàm VLOOKUP, để đảm bảo kết quả trả về chính xác và ổn định, người dùng cần chú ý một số điểm quan trọng sau:

• VLOOKUP không phân biệt chữ hoa và chữ thường trong điều kiện dò tìm. Do đó, các giá trị như iphone và iPhone được xem là giống nhau.
• Hàm VLOOKUP hỗ trợ hai kiểu tìm kiếm: Tìm kiếm chính xác (FALSE hoặc 0) và tìm kiếm tương đối (TRUE hoặc 1).
• Khi sử dụng tìm kiếm tương đối, cột dữ liệu dùng để dò tìm bắt buộc phải được sắp xếp theo thứ tự tăng dần, nếu không kết quả trả về có thể không chính xác.
• VLOOKUP chỉ có thể dò tìm từ trái sang phải, tức là cột chứa giá trị cần tìm phải nằm bên trái cột dữ liệu cần trả về.
• Trong trường hợp giá trị dò tìm xuất hiện nhiều lần, VLOOKUP chỉ trả về kết quả đầu tiên mà nó tìm thấy trong bảng dữ liệu.

Lỗi #REF! thường xuất hiện khi công thức tham chiếu đến một cột (hoặc vùng dữ liệu) không còn tồn tại trong bảng dò tìm, ví dụ bạn lỡ xóa một cột nên VLOOKUP không thể lấy dữ liệu từ cột đó nữa. Để khắc phục, bạn có thể hoàn tác thao tác vừa xóa bằng cách bấm Hoàn tác (Undo) trên thanh công cụ hoặc nhấn Ctrl + Z để khôi phục lại cột/hàng bị mất, rồi công thức sẽ hoạt động bình thường.

Công thức không thể lấy dữ liệu do cột tham chiếu đã bị xóa hoặc thay đổi

Lỗi #N/A xảy ra khi hàm VLOOKUP không tìm thấy giá trị cần dò trong bảng dữ liệu, thường do mã tìm kiếm không tồn tại, nhập sai hoặc phạm vi dò tìm chưa đúng.

Để xử lý lỗi này và tránh hiển thị thông báo lỗi trên bảng tính, bạn có thể dùng hàm IFERROR bọc bên ngoài công thức gốc. Khi đó, nếu công thức phát sinh lỗi #N/A, hàm sẽ trả về một giá trị thay thế (ví dụ: rỗng, 0 hoặc thông báo tùy chọn) thay vì hiển thị lỗi, giúp bảng tính dễ đọc và chuyên nghiệp hơn.

Giá trị cần tra cứu không tồn tại trong bảng dữ liệu

Trong quá trình sử dụng hàm VLOOKUP, đôi khi công thức vẫn đúng cú pháp nhưng kết quả lại hiển thị lỗi #VALUE!. Nguyên nhân thường xuất phát từ giá trị tra cứu không phù hợp, điển hình là khi chuỗi ký tự dùng để dò tìm vượt quá 255 ký tự mà VLOOKUP cho phép.

Để xử lý tình huống này, bạn có thể rút gọn nội dung giá trị tra cứu hoặc chuyển sang kết hợp hàm INDEX và MATCH để thay thế, giúp công thức hoạt động ổn định và linh hoạt hơn.

Công thức gặp lỗi do dữ liệu đầu vào hoặc cách nhập không hợp lệ

Việc nắm vững hàm VLOOKUP và cách kết hợp với các hàm liên quan sẽ giúp bạn xử lý dữ liệu hiệu quả và linh hoạt hơn trong Google Sheets. Đây là kiến thức nền tảng cần thiết để làm việc với bảng tính chuyên nghiệp và mở rộng sang các hàm nâng cao hơn.

Trang bị ngay máy tính bảng chính hãng tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn để học tập và làm việc với Google Sheets mượt mà hơn.