Buzz
Hàm VLOOKUP là một trong những công cụ phổ biến nhất trên Google Sheet, giúp bạn thực hiện thống kê và tìm kiếm dữ liệu trong bảng. Hãy cùng Mytour khám phá cách sử dụng, kết hợp hàm này với các hàm khác và giải quyết những lỗi phổ biến khi sử dụng hàm VLOOKUP trong Google Sheet!
Xem ngay video hướng dẫn cách sử dụng hàm VLOOKUP một cách dễ dàng:
1. Hàm VLOOKUP là gì? Ứng dụng của hàm VLOOKUP trong Google Sheet
- Khái niệm về Hàm VLOOKUP
Hàm VLOOKUP giúp bạn tìm kiếm giá trị trong cột bên trái của một bảng hoặc mảng, rồi trả về giá trị tương ứng từ cột bạn đã chỉ định trước. Chữ V trong VLOOKUP đại diện cho 'Vertical - Dọc.'
- Công thức của Hàm VLOOKUP
Công thức:
=VLOOKUP(giá_trị_tìm_kiếm;bảng_dữ_liệu;số_cột;[tìm_tương_đối])
Trong đó:
+ giá_trị_tìm_kiếm: (Bắt buộc). Giá trị dùng để dò tìm.
+ bảng_dữ_liệu: (Bắt buộc). Bảng chứa giá trị cần dò tìm, để ở dạng giá trị tuyệt đối với dấu $ được đứng đằng trước, ví dụ: $A$3:$E$40.
+ số_cột: (Bắt buộc). Thứ tự chính của cột chứa các giá trị dò tìm trên bảng_dữ_liệu.
Ví dụ trong bảng $A$3:$E$40, cột B chứa giá trị cần dò tìm thì số_cột ở đây sẽ là 2; bảng $C$3:$F$40, cột E chứa giá trị dò tìm, thì số_cột ở đây là 3.
+ [tìm_tương_đối]: (Không bắt buộc). Là phạm vi mà dữ liệu tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
- Ví dụ về hàm VLOOKUP
Cho bảng dữ liệu với các trường MÃ_SP, TÊN_SP, GIÁ, SỐ_LƯỢNG, TỔNG. Sử dụng hàm VLOOKUP để trích xuất thông tin tên của sản phẩm có mã là 1222.
Công thức:
=VLOOKUP(1222;$A$2:$E$9;2;0)
Ý nghĩa công thức: Hàm VLOOKUP so sánh giá trị 1222 với dải ô đầu tiên trong vùng A2:E9. Nếu dò tìm thấy kết quả 1222, ngay lập tức trả về tên của sản phẩm (tương ứng với đối số thứ 3 trong công thức).
Ví dụ về hàm VLOOKUP
- Ứng dụng của hàm VLOOKUP
Hàm VLOOKUP được sử dụng để tìm kiếm thông tin trong một trường dữ liệu hoặc danh sách dựa vào các mã định danh có sẵn.
2. Hướng dẫn cách sử dụng hàm VLOOKUP trong Google Sheet
- Các ứng dụng cơ bản
+ Tra cứu chính xác
Ví dụ: Có hai bảng dữ liệu trên một bảng tính như hình. Cần tìm số lượng tương ứng với mã sản phẩm.
Công thức:
=VLOOKUP(A14;$A$2:$E$9;3;FALSE)
Ý nghĩa công thức: Cụ thể, hàm VLOOKUP sử dụng giá trị ô A14 làm điểm tìm kiếm và tìm trong phạm vi từ ô A2 đến E9. Kết quả trả về từ cột thứ 3 trong phạm vi này, với đối số cuối cùng là FALSE (0) để đảm bảo tìm kiếm chính xác.
Kiểu tìm kiếm tương đối
+ Tìm kiếm tương đối
Áp dụng khi giá trị cần tìm kiếm trong một bảng đã được sắp xếp theo thứ tự tăng dần.
Ví dụ: Dựa vào bảng quy định xếp loại tương ứng với điểm đã cho, thực hiện xếp loại học lực cho các sinh viên có tên trong danh sách:
Công thức:
=VLOOKUP(C2;$A$10:$B$13;2;TRUE)
Ý nghĩa công thức: Xếp loại tương đối theo điểm số, ở đây chia thành 4 vùng tương ứng: dưới 5 điểm (~ YẾU), 5 - 6.4 điểm (TRUNG BÌNH), 6.5 - 7.9 điểm (KHÁ), trên 8 điểm (GIỎI).
Ví dụ về tìm kiếm tương đối
- Kết hợp với hàm QUERY
Công thức:
=VLOOKUP(F3;QUERY(A1:D9;'SELECT B,C,D');3;FALSE)
Ý nghĩa công thức: Sử dụng hàm VLOOKUP kết hợp với hàm QUERY để lấy số lượng sản phẩm theo tên.
Kết hợp với hàm QUERY
- Kết hợp với hàm SUM
Công thức:
=SUM(ArrayFormula(VLOOKUP($A$2:$A$9;$A$2:$E$9;5;FALSE)))
Ý nghĩa công thức: Tính tổng thu nhập của tất cả các sản phẩm có mã nằm trong vùng từ A2:A9.
Kết hợp với hàm SUM
- Kết hợp với hàm IF
Công thức:
=IF(VLOOKUP(1245;$A$2:$D$9;4;0)>10;'YES';'NO')
Ý nghĩa công thức: Tìm kiếm trong bảng, trả về kết quả theo điều kiện 'Nếu sản phẩm có Mã là 1245 và số lượng lớn hơn 10 thì trả về YES, ngược lại trả về NO'.
VLOOKUP kết hợp với IF
- Kết hợp với hàm LEFT, RIGHT
+ Kết hợp với hàm LEFT
Công thức:
=VLOOKUP(LEFT(A2;3);$A$13:$B$14;2;0)
Ý nghĩa công thức: Công thức này sẽ thay đổi giá trị tìm kiếm của A2 thành LEFT(A2;3).
Kết hợp với hàm LEFT
+ Kết hợp với hàm RIGHT
Công thức:
=VLOOKUP(RIGHT(A2;2);$A$17:$B$18;2;0)
Ý nghĩa công thức: Công thức này sẽ thay đổi giá trị tìm kiếm của A2 thành RIGHT(A2;2).
Kết hợp với hàm RIGHT
- Sử dụng INDEX và MATCH
Công thức:
=VLOOKUP(INDEX(A1:D9;MATCH(1215;A1:A9;0);1);A1:D9;3;FALSE)
Ý nghĩa công thức: Lấy giá của sản phẩm có mã sản phẩm là 1215.
Kết hợp với hàm INDEX và hàm MATCH
- Sử dụng AND và OR
Công thức:
=VLOOKUP(INDEX(A1:D9;MATCH(1215;A1:A9;0);1);A1:D9;3;AND(TRUE, FALSE))
=AND((VLOOKUP(1215;A1:D9;2;false)="iPhone 11");(VLOOKUP(1215;A2:D9;4;false) > 0))
Ý nghĩa công thức: Dùng hàm VLOOKUP làm 2 tham số cho hàm AND. "Xem sản phẩm có mã là 1215 có tên là iPhone 11 và số lượng có lớn 0".
Kết hợp với hàm AND
- Kết hợp với hàm OR
Công thức:
=OR((VLOOKUP(1215;A1:D9;2;false)="iPhone 11");(VLOOKUP(1215;A2:D9;4;false) >= 0))
Ý nghĩa công thức: Dùng hàm VLOOKUP làm 2 tham số cho hàm OR.
- Kết hợp với hàm OR
- Kết hợp với hàm SUMIF
Công thức:
=SUMIF(B2:B9;VLOOKUP("iPhone";B2:E9;1;FALSE);D2:D9)
Ý nghĩa công thức: Cộng dữ liệu trong bảng với điều kiện được lấy bằng hàm VLOOKUP. "Trong danh sách tên sản phẩm có con iPhone thì đếm tổng số lượng của tất cả con iPhone".
kết hợp hàm SUMIF
- Kết hợp với hàm COUNTIF
Công thức:
=COUNTIF( ArrayFormula(VLOOKUP(C2:C9;$C$2:$D$9;1;FALSE));">= 20000000")
Ý nghĩa công thức: Đếm số sản phẩm có giá tiền lớn hơn 20 triệu.
Kết hợp với hàm COUNTIF
- Kết hợp nhiều điều kiện
Công thức:
=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")
Ý nghĩa công thức: Kiểm tra số lượng sản phẩm của sản phẩm iPhone 11, nếu lớn hơn 0 trả về "Còn hàng", ngược lại trả về "Hết hàng".
Kết hợp nhiều điều kiện
3. Cách dùng hàm VLOOKUP trên 2 sheet khác nhau
Ví dụ: Cho bảng sau, dùng hàm VLOOKUP tìm số lượng điện thoại từ sheet SAN_PHAM.
Công thức:
=VLOOKUP(A2;SAN_PHAM!$A$2:$E$9;4;0)
Ý nghĩa công thức: Hàm VLOOKUP trong sheet GIA tìm giá trị trong bảng từ sheet SAN_PHAM và trả về kết quả tương ứng.
Tìm kiếm dữ liệu trên 2 bản tính khác nhau
Mẹo và lưu ý:
- Nếu trang tính chứa khoảng trắng hoặc các ký tự không phải chữ cái, tên trang tính phải được đặt trong dấu ngoặc kép, chẳng hạn như ‘Jan Sales’!$A$2:$B$6.
- Thay vì nhập trực tiếp trên trang tính vào công thức, bạn có thể chuyển sang trang tính tra cứu và chọn phạm vi ở đó. Google Sheet sẽ tự động chèn một tham chiếu với cú pháp chính xác, giúp bạn không khó khăn khi kiểm tra tên và khắc phục sự cố.
4. Các lưu ý khi sử dụng hàm VLOOKUP trong Google Sheet
- Điều kiện tìm kiếm không phân biệt chữ hoa - thường.
- Có 2 kiểu tìm kiếm khi dùng hàm VLOOKUP: Tìm kiếm tương đối, tìm kiếm tuyệt đối.
- Bảng dò tìm kết quả cần được sắp xếp trước khi áp dụng kiểu tìm kiếm tương đối.
- VLOOKUP chỉ tìm dữ liệu từ trái qua phải.
- Chỉ tìm được giá trị đầu tiên xuất hiện trong bảng.
5. Một số lỗi thường gặp khi dùng hàm VLOOKUP
- Lỗi #N/A
+ Ý nghĩa: Dò tìm không có giá trị.
Lỗi #N/A
+ Khắc phục vấn đề: Nếu gặp lỗi #N/A, bạn có thể sử dụng công thức sau để xử lý.
= IFERROR (công thức ban đầu)
Công thức này sẽ trả về TRUE nếu có lỗi trong công thức.
Kiểm tra lỗi bằng hàm ISERROR
- Sự cố #REF
+ Ý nghĩa: Cột tìm kiếm không tồn tại trong bảng chứa giá trị cần tìm.
+ Giải quyết vấn đề:
Ví dụ: Lỗi #REF! thường xuất hiện khi bạn xóa một cột từ bảng dữ liệu.
Nhấn ngay nút Hoàn tác trên Thanh công cụ Truy nhập Nhanh (hoặc nhấn CTRL+Z) để khôi phục cột hoặc hàng đã mất.
Lỗi xuất hiện do việc mất cột thứ 5.
- Sự cố #VALUE
+ Ý nghĩa: Xuất hiện lỗi khi bạn nhập công thức.
+ Giải pháp:
a. Sự cố: Tham số giá_trị_tra_cứu có độ dài lớn hơn 255 ký tự.
Giải pháp: Rút ngắn giá trị hoặc sử dụng kết hợp hàm INDEX và MATCH như một lựa chọn thay thế.
Sử dụng kết hợp hàm INDEX và MATCH
b. Sự cố: Số thứ tự cột giá trị hiển thị (Col_index_num) chứa văn bản hoặc nhỏ hơn 0.
Sự cố này có thể xảy ra do lỗi đánh máy trong Số thứ tự cột giá trị hiển thị hoặc vô tình chỉ định một số nhỏ hơn 1 làm giá trị chỉ mục (hiện tượng phổ biến nếu một hàm Google Sheet khác được lồng trong hàm VLOOKUP trả về một số, chẳng hạn như '0', làm số thứ tự cột giá trị hiển thị.
Giá trị tối thiểu cho số thứ tự cột giá trị hiển thị là 1, trong đó 1 là cột tìm kiếm, 2 là cột đầu tiên ở bên phải của cột tìm kiếm,... Vì vậy, nếu bạn đang tìm kiếm trong cột A thì số 1 sẽ tham chiếu cột đó, 2 là cột B, 3 là cột C,...
Số thứ tự cột có giá trị 0
6. Bài tập sử dụng hàm VLOOKUP
Một số bài tập về hàm VLOOKUP giúp bạn ôn tập lại kiến thức: Các bài tập sử dụng hàm VLOOKUP
7. Các câu hỏi thường gặp khi sử dụng hàm VLOOKUP
- Hàm VLOOKUP trong Google Sheet hoạt động như thế nào?
Trả lời: VLOOKUP là một công cụ tra cứu và rút thông tin từ bảng dữ liệu. Chữ V trong VLOOKUP là viết tắt của từ Vertical (nghĩa là chiều dọc), yêu cầu bảng dữ liệu được sắp xếp theo chiều dọc để hoạt động hiệu quả.
- Tại sao hàm VLOOKUP được người làm văn phòng ưa chuộng nhiều?
Trả lời: Hàm VLOOKUP là một công cụ xử lý dữ liệu quan trọng trong môi trường văn phòng, giúp người làm việc xử lý dữ liệu một cách nhanh chóng và chính xác. Hiểu biết về hàm này giúp tăng hiệu suất làm việc và cơ hội phát triển sự nghiệp.
Danh sách một số mẫu laptop đang được kinh doanh trên Mytour:Chúc mừng bạn đã hoàn thành việc sử dụng hàm VLOOKUP để lọc dữ liệu trong bảng và kết hợp nó với các hàm khác một cách thành công! Hãy tiếp tục khám phá và ứng dụng kiến thức mới của bạn!
