Hàm VLOOKUP trong Excel là một trong những hàm quan trọng nhất, được sử dụng để dò và trả về dữ liệu tương ứng. Đây là một hàm hữu ích cho việc phân tích dữ liệu và giúp tiết kiệm thời gian khi làm việc với các bảng dữ liệu lớn. Tin học văn phòng PRO sẽ hướng dẫn chi tiết bạn cách sử dụng hàm VLOOKUP trong bài viết sau nhé.
Ví dụ trong bài được thực hiện trên phiên bản Excel 2016. Bạn có thể áp dụng tương tự trên các phiên bản Excel khác như: 2007, 2010, 2013, 2017 và Microsoft Excel 365.
Định nghĩa hàm VLOOKUP trong Excel
Hàm VLOOKUP được sử dụng khi cần tìm kiếm dữ liệu trong một bảng hoặc phạm vi theo hàng dọc và trả về kết quả tương ứng theo hàng ngang.
Thường thì, hàm VLOOKUP được áp dụng rất phổ biến khi tìm kiếm thông tin như tên sản phẩm, giá cả, số lượng,… dựa trên mã vạch, mã sản phẩm,… hoặc tìm kiếm tên nhân viên, xếp loại nhân viên theo các tiêu chí đã định trước.
Tuy nhiên, khi cần tìm kiếm dữ liệu trong một bảng hoặc phạm vi theo hàng ngang và trả về kết quả theo hàng dọc, ta có thể sử dụng hàm HLOOKUP.
Từ LOOKUP là Look Up có nghĩa là “tìm kiếm” trong tiếng Anh. Trong đó, V là viết tắt của Vertical – hàng dọc và H là viết tắt của Horizontal – hàng ngang.
Công thức hàm VLOOKUP trong Excel
Công thức hàm VLOOKUP:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
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 giới hạn để dò tìm.
- Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
- Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, nếu bỏ qua thì mặc định là 1.
Nếu Range_lookup = 1 (TRUE): dò tìm tương đối.
Nếu Range_lookup = 0 (FALSE): dò tìm chính xác.
Khi sao chép công thức cho các ô dữ liệu khác, bạn cần sử dụng ký tự $ để giữ nguyên Table_array và tránh việc thay đổi khi thêm vào (VD: $H$6:$J$13), hoặc có thể sử dụng phím F4 sau khi chọn bảng.
Xem thêm: Hàm SUMIF
Một số ví dụ về hàm VLOOKUP trong Excel
Bạn có thể truy cập vào đây để tải xuống các ví dụ về hàm VLOOKUP trong Excel bên dưới.
Ví dụ 1: Tính phụ cấp theo chức vụ
Trong bối cảnh đại dịch Covid-19, công ty đã quyết định trả phụ cấp cho nhân viên dựa trên chức vụ của họ như được liệt kê trong bảng 2 (B16:C21). Để tính toán mức phụ cấp tương ứng, ta sẽ sử dụng danh sách nhân viên và chức vụ của họ trong bảng 1.
Cách thực hiện là ta sẽ tìm giá trị chức vụ của nhân viên trong bảng 1, sau đó tìm kiếm giá trị đó trong cột 1 của bảng 2 từ trên xuống dưới. Khi tìm thấy giá trị, ta sẽ lấy giá trị tương ứng trong cột 2 của bảng 2 để điền vào bảng 1.
Nếu danh sách nhân viên có hàng trăm hoặc hàng nghìn người, việc làm thủ công như vậy sẽ rất khó khăn. Đây chính là lúc hàm VLOOKUP trở nên hữu ích.
Tại ô E4, ta sử dụng công thức: =VLOOKUP(D4,$B$16:$C$21,2,0)
- Dấu $ được sử dụng để cố định các dòng và cột trong bảng 2 khi sao chép công thức sang các ô khác.
- 2 là số thứ tự của cột dữ liệu.
- Range_lookup = 0 (FALSE) để tìm kiếm chính xác.
Sau đó, ta chỉ cần sao chép công thức cho các ô khác hoặc sử dụng Flash Fill và việc tính toán phụ cấp theo chức vụ đã được hoàn thành một cách nhanh chóng.
Ví dụ 2: Xếp loại học sinh theo điểm số
Sau kỳ kiểm tra, ta có kết quả bài thi của học sinh như được liệt kê trong bảng 1. Để xếp loại theo điểm số, ta sẽ sử dụng bảng 2 (B11:C15).
Tại ô E4, ta sử dụng công thức: =VLOOKUP(D4,$B$11:$C$15,2,1)
- Ký hiệu $ được sử dụng để cố định các hàng và cột trong bảng 2 khi sao chép công thức sang các ô khác.
- 2 là số thứ tự của cột dữ liệu.
- Range_lookup = 1 (TRUE) để tìm kiếm giá trị gần nhất.
Trong trường hợp này, Excel sẽ lấy giá trị của ô D4 và tìm kiếm trong bảng 2. Sau khi tìm thấy giá trị gần nhất của D4 trong bảng (ở đây là 8.5), Excel sẽ trả về kết quả tương ứng trong cột 2 là Giỏi.
Sau đó, bạn có thể sao chép công thức cho các ô khác hoặc sử dụng Flash Fill để nhanh chóng xếp loại học sinh theo điểm số.
Ví dụ 3: Dùng hàm VLOOKUP để tìm kiếm gần đúng
Ví dụ: Để xếp loại học sinh dựa trên điểm trung bình trong trường hợp sau, Range_lookup=1, ta bắt buộc phải lấy giá trị tương đối nghĩa là gần đúng, 9.1 gần với 9, 5.3 gần với 5… hay diễn đạt theo một cách khác, chúng ta có thể đưa ra tiêu chí xếp loại như sau:
- Từ 9: xếp loại giỏi ( lớn hơn hoặc bằng 9, 9 <= x)
- Từ 6.5 đến dưới 9: xếp loại khá (6.5 <= x < 9)
- Từ 5 đến dưới 6.5: xếp loại trung bình (5 <= x < 6.5)
Tại ô D4, bạn điền công thức: = VLOOKUP(C4,$B$10:$C$12,2,TRUE)
Lưu ý: Để đảm bảo kết quả hàm VLOOKUP đưa ra đúng, bạn phải sắp xếp bảng tra cứu theo thứ tự từ nhỏ đến lớn.
Ví dụ 4: Hàm VLOOKUP kết hợp với Data Validation
Đầu tiên, bạn chọn ô C10, sau đó bấm vào thẻ Data / Data Validation để chuẩn bị Data Validation.
Tiếp theo, khi hộp thoại Data Validation mở ra, bạn bấm chọn thẻ Settings. Ở mục Allow, bạn tiến hành chọn List và cuối cùng chọn Source là vùng B4:B7.
Cuối cùng, bạn điền công thức sau vào bảng Excel: =VLOOKUP(C10,B4:D7,2,0) và =VLOOKUP(C10,B4:D7,3,0) để tham chiếu đến ô chứa Data Validation.
Ví dụ 5: Tìm mã ngành học
Dựa vào bảng mã ngành, bạn hãy sử dụng hàm VLOOKUP để có thể tìm ra tên ngành học của sinh viên dựa trên ký tự đầu tiên của số báo danh.
Bạn sử dụng kết hợp hàm VLOOKUP với hàm LEFT để điền vào ô cần tìm mã ngành như công thức sau: =VLOOKUP(LEFT(C10),F4:G5,2,0)
Trong đó, hàm LEFT(C10) sẽ giúp bạn lấy ra ký tự đầu tiên trong số báo danh được lưu trong ô C10, và hàm VLOOKUP sẽ sử dụng ký tự này cho việc tra cứu trong bảng mã ngành và cho ra kết quả.
Những vấn đề thường gặp khi sử dụng hàm VLOOKUP
Lỗi #N/A (Lỗi không có giá trị)
Một điều quan trọng cần lưu ý khi sử dụng hàm VLOOKUP là nó chỉ có thể tìm kiếm các giá trị trong cột bên trái nhất của Table_array, nếu không sẽ xảy ra lỗi #N/A. Trong trường hợp này, bạn có thể cân nhắc sử dụng hàm INDEX kết hợp với hàm MATCH.
Ví dụ, nếu Table_array là A2:C10, hàm VLOOKUP sẽ tìm kiếm trong cột A. Để khắc phục, bạn có thể thay đổi Table_array thành B2:C10 để hàm VLOOKUP tìm kiếm trong cột B.
Ngoài ra, nếu không tìm thấy kết quả chính xác, hàm sẽ trả về lỗi #N/A do dữ liệu không có trong Table_array. Trong trường hợp này, bạn có thể sử dụng hàm IFERROR để thay thế #N/A bằng một giá trị khác.
Ví dụ, Rau muống không có trong bảng dò tìm nên hàm VLOOKUP sẽ không tìm thấy kết quả.
Nếu bạn chắc chắn rằng dữ liệu của mình có trong Table_array nhưng hàm VLOOKUP vẫn không tìm thấy kết quả, hãy kiểm tra xem các ô dữ liệu được tham chiếu có khoảng trắng ẩn hoặc ký tự không in. Ngoài ra, hãy đảm bảo rằng các ô dữ liệu tuân theo đúng định dạng.
Lỗi #REF!
Trong trường hợp Col_index_num lớn hơn số cột hiện có trong Table_array, kết quả sẽ trả về giá trị lỗi #REF!. Vì vậy, cần kiểm tra lại công thức để đảm bảo rằng Col_index_num không vượt quá số cột trong Table_array.
Ví dụ minh họa cho trường hợp này là khi Col_index_num có giá trị là 3, nhưng Table_array là B2:C10 chỉ có 2 cột.
Lỗi #VALUE!
Nếu số chỉ mục cột (Col_index_num) trong công thức nhỏ hơn 1, kết quả sẽ trả về giá trị lỗi #VALUE!.
Trong bảng dữ liệu (Table_array), cột 1 là cột được tìm kiếm, cột 2 là cột đầu tiên bên phải của cột tìm kiếm, và cột tiếp theo sẽ là các cột bên phải tiếp theo. Do đó, khi gặp phải lỗi này, cần kiểm tra lại giá trị của số chỉ mục cột (Col_index_num) trong công thức.
Ví dụ dưới đây minh họa cho trường hợp số chỉ mục cột (Col_index_num) bằng 0, dẫn đến kết quả trả về là giá trị lỗi #VALUE!.
Lỗi #NAME?
Khi xuất hiện thông báo lỗi #NAME?, điều đó có nghĩa là Lookup_value không được bao quanh bởi dấu ngoặc kép (“). Để tìm kiếm giá trị định dạng văn bản (Text), bạn cần sử dụng dấu ngoặc kép để Excel có thể hiểu công thức.
Ví dụ, nếu không đặt Cải xoăn trong dấu ngoặc kép (“), sẽ gây ra lỗi #NAME?. Để khắc phục, bạn chỉ cần thay thế Cải xoăn bằng “Cải xoăn”.
Các điều cần lưu ý khi sử dụng hàm VLOOKUP
Sử dụng tham chiếu tuyệt đối
Trong quá trình sao chép công thức, hãy sử dụng tham chiếu tuyệt đối cho Table_array hoặc Lookup_value bằng cách thêm ký hiệu đô la ($) vào trước các cột và hàng. Điều này giúp đảm bảo rằng công thức không bị thay đổi.
Ví dụ, nếu ta có công thức =VLOOKUP(B13,$B$2:$C$10,2,0) tại ô C13, khi sao chép cho ô C4, Table_array sẽ vẫn giữ nguyên giá trị.
Nếu không sử dụng tham chiếu tuyệt đối, Lookup_value hoặc Table_array sẽ bị thay đổi, gây sai lệch trong kết quả tìm kiếm.
Ví dụ, nếu ta có công thức =VLOOKUP(B13,B2:C10,2,0) tại ô C13, khi sao chép cho ô C4, Table_array sẽ biến thành =VLOOKUP(B14,B3:C11,2,0).
Không lưu trữ giá trị số dưới dạng văn bản
Nếu trong Table_array, các giá trị số được lưu trữ dưới dạng văn bản và giá trị tìm kiếm lại là số, thì hàm VLOOKUP sẽ trả về thông báo lỗi #N/A.
Ví dụ, chúng ta có dữ liệu trong ô A2:A5 đang ở dạng văn bản, nhưng giá trị tìm kiếm trong ô A8 lại là số.
Trong trường hợp này, hãy chuyển đổi định dạng của ô A2:A5 sang dạng số và hàm sẽ trả về kết quả chính xác.
Bảng tìm kiếm chứa các giá trị bị trùng.
Nếu bảng dữ liệu của bạn có nhiều giá trị trùng lặp, hàm VLOOKUP sẽ trả về kết quả đầu tiên mà nó tìm thấy từ trên xuống dưới.
Ví dụ, trong bảng dưới đây, có hai giá trị cho Táo là 97 và 23. Hàm VLOOKUP sẽ trả về kết quả 97 vì đó là giá trị đầu tiên nó tìm thấy.
Giải pháp 1: Nếu bạn muốn loại bỏ các giá trị trùng lặp, bạn có thể chọn Data > Remove Duplicates để xóa chúng khỏi bảng dữ liệu.
Giải pháp 2: Sử dụng Pivot Table để lọc ra danh sách kết quả.
Một số hạn chế của hàm VLOOKUP
Chỉ hỗ trợ tham chiếu từ trái sang phải
Hàm VLOOKUP chỉ tìm kiếm giá trị từ trái sang phải trong dãy dữ liệu. Nếu bạn cần thực hiện tham chiếu từ phải sang trái, bạn cần phải kết hợp nhiều công thức lại với nhau như công thức Match và Index rất phức tạp và rắc rối, gây khó hiểu cho người sử dụng.
Chỉ hoạt động hiệu quả với các giá trị riêng biệt
Hàm VLOOKUP chỉ hoạt động hiệu quả với các giá trị riêng biệt vì có xu hướng chỉ quan tâm đến giá trị tham chiếu đầu và bỏ quên các dòng khác chứa giá trị tương tự.
Số thứ tự cột tham chiếu luôn cố định
Hàm VLOOKUP chỉ cho phép bạn điền thủ công các số thứ tự của cột tham chiếu vào nội dung của công thức, làm cho các thao tác sao chép sang ô tính khác gặp nhiều khó khăn hơn. Lúc này, bạn cần sử dụng hàm Match để biến biến đổi số thứ tự liên tục.
Mặc định thiết lập “approximate match”
Điều kiện kiểm duyệt tham chiếu ở trong nội dung hàm VLOOKUP nếu không được chọn thì công thức sẽ mặc định thiết lập “approximate match”. Điều này điều này có thể gây ra kết quả không chính xác, sai lệch khi tham chiếu.
Làm tốc độ phản hồi của bảng tính bị chậm đi
Hàm VLOOKUP cũng có có thể làm giảm tốc độ phản hồi của bảng tính giảm hiệu năng của chương trình, thậm chí gây ra hiện tượng crash, gây khó chịu cho người sử dụng.
Xem thêm:
Trên đây là bài viết chia sẻ cách sử dụng hàm VLOOKUP trong Excel. Hy vọng những thông tin này sẽ giúp bạn hiểu và áp dụng hàm VLOOKUP hiệu quả cho công việc của mình.
Comments 4