Có nhiều bạn đang gặp khó khăn khi sử dụng hàm VLOOKUP trong Excel, đặc biệt là không phân biệt được cách dùng giữa VLOOKUP và HLOOKUP. Ở bài viết này, Azgad sẽ giúp bạn hiểu rõ hơn khi nào dùng hàm dò tìm giá trị và những lưu ý trong hàm Excel này nhé!
Hàm VLOOKUP trong Excel
Khái niệm
Vlookup trong Excel là hàm dùng để dò tìm và trả về giá trị tương ứng một cách nhanh chóng trong một bảng, một phạm vi cho trước theo phương thức hàng dọc (theo cột).
Cấu trúc VLOOKUP trong Excel
=VLOOKUP (Lookup_value; Table_array; Col_index_num)
Trong đó:
- Lookup_value
: Giá trị cần dò tìm, có thể điền giá trị trực tiếp hoặc tham chiếu tới một ô trên bảng tính.
- Table_array
: Bảng chứa giá trị cần dò tìm.
- Col_index_num
: Số thứ tự của cột lấy dữ liệu trong bảng chứa giá trị cần dò tìm, tính từ trái qua phải.
Lưu ý: Hướng dẫn được thực hiện với phiên bản Excel 2016. Bạn hoàn toàn có thể thực hiện các thao tác tương tự trên các phiên bản Excel 2003, 2007, 2010 và 2013 và trên các dòng laptop có hệ điều hành khác nhau.
Các ví dụ về hàm VLOOKUP trong Excel
Để hiểu rõ hơn về hàm dò tìm giá trị này, Azgad sẽ sử dụng ví dụ minh họa về Bảng điểm trung bình nhóm 1 năm 2020 – 2021 như sau:
Ví dụ hàm VLOOKUP
Hàm VLOOKUP khi tìm kiếm chính xác giá trị
Ví dụ 1: Điền thông tin Ngành học theo từng Mã NH tương ứng vào bảng điểm trung bình nhóm 1 năm 2020 – 2021 được cho như trên:
Để giải quyết được ví dụ trên, trước hết chúng ta cần phải xác định được các tham số của hàm VLOOKUP, cụ thể:
- Lookup_value (D4)
: Giá trị cần dò tìm.
- Table_array (B15:C17)
: Bảng chứa giá trị cần dò tìm.
- Col_index_num (2)
: Số thứ tự của cột lấy dữ liệu trong bảng 2.
Lưu ý: Ở bảng Table_array cần cố định giá trị để khi copy công thức sẽ không bị thay đổi.
Nhập công thức vào ô địa chỉ =VLOOKUP(D4,$B$15:$C$17,2).
Ví dụ 1 hàm VLOOKUP
Sau đó nhấn Enter, bạn sẽ được kết quả. Tiếp theo, để điền Ngành học cho các ô còn lại, bạn click chuột vào góc phải bên dưới của ô địa chỉ, kéo thả chuột hết cột để copy công thức, bạn sẽ nhận được kết quả như hình bên dưới:
Kết quả hàm VLOOKUP
Công thức hàm VLOOKUP để tìm kiếm tương đối
Ví dụ 2: Điền thông tin Xếp loại theo Điểm TB tương ứng vào bảng điểm trung bình nhóm 1 năm 2020 – 2021 được cho như trên:
Trước hết chúng ta cũng cần phải xác định được các tham số của VLOOKUP, cụ thể:
- Lookup_value (F4)
: Giá trị cần dò tìm.
- Table_array (E15:F18)
: Bảng chứa giá trị cần dò tìm.
- Col_index_num (2)
: Số thứ tự của cột lấy dữ liệu trong bảng 2.
Lưu ý: Tìm kiếm tương đối chỉ áp dụng khi giá trị cần dò tìm trong Table_array được sắp xếp theo thứ tự. Ở bảng Table_array cần cố định giá trị để khi copy công thức sẽ không bị thay đổi.
Nhập công thức vào ô địa chỉ =VLOOKUP(F4,$E$15:$F$18,2)
Ví dụ 2 hàm VLOOKUP
Nhấn Enter, bạn sẽ có kết quả từ hàm dò tìm. Thực hiện thao tác kéo thả tương tự trên, kết quả sẽ hiện như hình bên dưới:
Kết quả hàm VLOOKUP
Xác định giá trị với hàm VLOOKUP trên hai sheet dữ liệu
Ví dụ 3: Điền thông tin Ngành học theo từng Mã NH tương ứng vào bảng điểm trung bình nhóm 1 năm 2020 – 2021 được cho ở hai sheet dữ liệu sau:
Bảng điểm trung bình nhóm 1
Bảng ngành học nhóm 1
Các tham số của hàm VLOOKUP, cụ thể:
- Lookup_value (D4)
: Giá trị cần dò tìm ở Sheet 6.
- Table_array (A4:B6)
: Bảng chứa giá trị cần dò tìm ở Sheet 5.
- Col_index_num (2)
: Số thứ tự của cột lấy dữ liệu trong bảng 2 Sheet 5.
Lưu ý: Ở bảng Table_array cần cố định giá trị để khi copy công thức sẽ không bị thay đổi.
Nhập công thức vào ô địa chỉ =VLOOKUP(D4,Sheet5!$A$4:$B$6,2).
Ví dụ 3 hàm Vlookup trên hai Sheet
Sau đó, thực hiện thao tác tương tự hai ví dụ trên, màn hình sẽ hiển thị kết quả.
Trích xuất dữ liệu bằng hàm VLOOKUP
Ví dụ 4: Trích xuất dữ liệu Ngành học dựa trên Bảng 1 như bên bên dưới.
Đầu tiên, bạn cần thực hiện các thao tác được nêu ở Ví dụ 1 để lấy mã Ngành học cho Bảng 1.
Sau đó, ở ô địa chỉ E21, bạn chỉ cần nhập =E4, rồi nhấn Enter, kéo thả cho cho các ô còn lại, kết quả sẽ ra như sau:
Ví dụ 4 hàm VLOOKUP
Một số lưu ý khi dùng dùng hàm VLOOKUP
Hàm VLOOKUP sử dụng địa chỉ ô tuyệt đối
Nếu bạn muốn copy địa chỉ ô, ở bảng Table_array hoặc Lookup_value cần tham chiếu tuyệt đối bằng cách nhấn F4 để công thức không bị thay đổi. Nếu không tham chiếu tuyệt đối, khi copy công thức kết quả tìm kiếm sẽ bị sai lệch.
-
F4 (1 lần): Cố định cột và cố định dòng => $cột$dòng.
Ví dụ: $A$1 là cố định cột A và dòng 1
-
F4 (2 lần): Cố định dòng không cố định cột => cột$dòng.
Ví dụ: A$1 là cố định dòng 1
-
F4 (3 lần): Cố định cột, không cố định dòng => $cộtdòng.
Ví dụ: $A1 là cố định cột A
Hàm VLOOKUP được tìm kiếm từ trên xuống dưới
Khi trong Table_array có các giá trị trùng nhau, hàm VLOOKUP sẽ trả về giá trị đầu tiên được tìm thấy từ trên xuống dưới.
Ví dụ 5: Tìm Điểm TB của Sinh viên nhóm 1 có ngành học là Dược Y
Theo bảng 1 cho thấy, có hai sinh viên Ngành Dược Y nhưng kết quả khi dùng VLOOKUP chỉ hiện thị Điểm TB của bạn sinh viên đầu tiên từ trên xuống dưới, cụ thể như hình:
Ví dụ 5 hàm VLOOKUP
Hàm VLOOKUP tìm kiếm không phân biệt chữ hoa hay chữ thường
Khi điều kiện tìm kiếm được viết dưới dạng chữ hoa hay chữ thường thì kết quả VLOOKUP trả về đều giống nhau.
Ví dụ 6: Dùng Ví dụ 5
Ở ví dụ này, bạn có thể thấy, ở ô địa chỉ C15 Azgad đã ghi “DƯỢC Y” thay vì “Dược y” như bảng 1, nhưng khi dùng hàm VLOOKUP dò tìm kiếm, kết quả vẫn trả về đúng yêu cầu.
Ví dụ 6 hàm VLOOKUP
Hàm VLOOKUP sẽ trả về lỗi #NA
Đây là lí do vì sao cột đầu tiên bên trái của vùng tìm kiếm phải là cột chứa điều kiện cần tìm kiếm. Khi giá trị cần tra cứu không được tìm thấy, kết quả sẽ trả về lỗi #NA.
Ví dụ 7: Dùng Ví dụ 5
Các bạn có thể thấy, Azgad lấy Table_array không phù hợp làm hàm VLOOKUP không tìm thấy được giá trị cần tìm kiếm nên trả về #NA như mẫu dưới đây:
Ví dụ 7 hàm VLOOKUP bị lỗi
Dùng đúng toán tử liên kết trong hàm VLOOKUP
Ở các dòng máy tính khác nhau, dấu phẩy (,) hay dấu chấm phẩy (;) trong Excel giúp liên kết các tham số cũng sẽ khác nhau. Vì thế, bạn cần xem máy tính của mình phù hợp với toán tử nào để dùng cho đúng bạn nhé!
Như vậy, Azgad vừa hướng dẫn bạn cách sử dụng hàm VLOOKUP để dò tìm giá trị theo cột cũng như những điểm cần lưu ý khi dùng hàm này. Azgad hy vọng bạn đã nắm được phần nào kiến thức và dễ dàng giải quyết được các bài toán về VLOOKUP nhé!
Tác giả: Hoàng Thị Cẩm Tiên
