Vlookup trong Excel được sử dụng khá phổ biến, vì bất cứ một bảng giá trị nào cũng cần được dò tìm và lọc qua trước khi điền bảng. Tuy nhiên không phải ai cũng biết sử dụng hàm Vlookup, nó được xem là một hàm khó trong Excel. Bài viết dưới đây của chúng tôi sẽ hướng dẫn cho bạn cách sử dụng hàm này và một số ví dụ chi tiết về nó.
Hàm Vlookup trong Excel được hiểu như thế nào?
Hàm Vlookup trong Excel là một trong những hàm công thức trong trang tính Excel. Nó được xem là công cụ hỗ trợ cho người dùng thực hiện hàng loạt các phép tính mà không cần phải tốn quá nhiều thời gian.
Bạn có thể sử dụng hàm Vlookup để thực hiện thao tác dò tìm dữ liệu trong một bảng, hay phạm vi theo hàng dọc và trả về dữ liệu theo các hàng tương ứng mỗi khi bạn thực hiện điền bảng.
Hàm này được sử dụng nhiều tại các doanh nghiệp, công ty, cửa hàng trong việc tìm tên sản phẩm, đơn giá, số lượng, mã sản phẩm, tên nhân viên, xếp loại nhân viên,… dựa trên mã vạch đơn hàng hoặc số thứ tự, mã nhân viên. Bạn chỉ cần áp dụng hàm cho toàn bảng tính là có thể hoàn thành các thao tác tìm kiếm nhanh chóng, thuận tiện.
Ngược lại, nếu như muốn dò tìm dữ liệu trong một bảng, một phạm vi theo hàng ngang và cho ra kết quả ở các cột tương ứng, bạn có thể tìm hiểu và tham khảo thêm về hàm HLOOKUP.
Vì chúng là hàm quốc tế nên được đặt theo chuẩn Tiếng Anh, LOOKUP là Look Up, có nghĩa là tìm kiếm. Chữ V trong Vlookup là viết viết tắt của Vertical, có nghĩa là hàng dọc. Hàm Vlookup có thể sử dụng chung, kết hợp các hàm khác trong phép tính như: Sum; If…
Hàm Vlookup trong Excel và công thức chuẩn xác
Hàm Vlookup được sử dụng theo đúng công thức quy định, không được loại trừ hay bỏ qua bất kỳ yếu tố nào trong công thức. Nếu như bạn tự ý bỏ bớt chi tiết, hàm sẽ không hoạt động. Công thức chuẩn xác nhất của hàm Vlookup như sau:
Ngay tại ô giá trị cần tính, bạn gõ: “=Vlookup (Lookup_value; Table_array, Col_index_num; Range_lookup)”. Bạn có thể sử dụng phím F4 để cố định các giá trị trong ô.
Tùy thuộc vào phiên bản Excel của mỗi người mà hàm Vlookup có thể khác biệt một chút. Dấu phẩy được thay thế bằng dấu chấm phẩy, bạn chỉ cần chú ý đến gợi ý công thức khi bạn gõ “Vlookup” vào ô tính. Các kí hiệu trong công thức được hiểu như sau:
- Lookup_value: chỉ giá trị dùng để tìm kiếm nằm ở vị trí ô nào.
- Table_array: Điều kiện chính để dò tìm giá trị tìm kiếm, cột đầu tiên trong table_array là cột để tìm giá trị tìm kiếm. Mục giá trị này có thể nằm trong cùng hoặc khác sheet với Lookup_value, hoặc ở một file khác. Chúng thường được hiển thị dưới dạng địa chỉ tuyệt đối.
- Col_index_num: Là thứ tự của cột lấy dữ liệu trên bảng giá trị Table_array . Cột đầu tiên được chọn sẽ tính là cột 1 và lần lượt tính tiếp cho các cột tiếp theo.
- Range_lookup: Trả kết quả tìm kiếm True or False, có thể đúng hoặc sai. True có nghĩa là tìm kiếm tương đối, tương ứng với 1. False có nghĩa là tìm kiếm tuyệt đối, tương ứng với 0.
Những ví dụ dễ hiểu của hàm Vlookup
Công thức của hàm Vlookup trong Excel khá trừu tượng và bao gồm nhiều thuật ngữ bằng Tiếng Anh. Bạn cần thực hành và tìm hiểu thêm các ví dụ để hiểu rõ thao tác. Bạn có thể tham khảo một số ví dụ được chúng tôi tổng hợp dưới đây:
Hàm Vlookup trong Excel bài tập 1
Do diễn biến phức tạp của dịch Covid-19, công ty TNHH X đã quyết định phụ cấp cho nhân viên theo chức vụ tương ứng như dữ liệu từ (B16:C21) (cho trước). Dựa vào danh sách nhân viên cùng với chức vụ đã được cho sẵn ở bảng, bạn có thể xác định mức phụ cấp tương ứng.
Bạn thực hiện dò tìm giá trị của chức vụ của nhân viên tại theo bảng giá trị tương ứng, sau đó dò tìm tại cột 1 trong bảng dữ liệu từ trên xuống dưới. Khi tìm thấy giá trị, bạn lấy giá trị điền vào các ô là hoàn thành công thức. Đây chính là cách dò tìm thủ công nhất, tìm và điền tên từng người.
Tuy nhiên, khi gặp một danh sách nhân viên vài trăm, vài ngàn người thì việc dò tìm này trở nên mất thời gian và thiếu tính chính xác. Đó chính là khi bạn nên sử dụng hàm Vlookup trong Excel. Bạn gõ theo công thức “=Vlookup(D4,$B$16:$C$21,2,0)” tại ô đầu tiên cần điền giá trị.
Trong đó, dấu $ được tạo ra bằng cách nhấn F4 vào khu vực đó, có tác dụng cố định các dòng, các cột của bảng khi bạn sao chép công thức sang các ô khác. 2 là vị trí cần dò tìm của cột dữ liệu. Tiếp theo, bạn chỉ cần sao chép công thức sang các ô tiếp theo hoặc sử dụng tính năng Flash Fill là có thể tính được tiền phụ cấp nhanh chóng, chính xác.
Hàm Vlookup trong Excel bài tập 2
Sau khi kiểm tra HK1 xong, ta có kết quả bài thi tương ứng với học sinh là giá trị cho trước. Bạn cần xếp loại điểm số của học sinh theo các mức “Giỏi”, “Khá” và “Trung bình” dựa trên bảng (B11:C15).
Một trường có rất nhiều học sinh, bạn không thể tự mình tính theo cách thức phổ thông. Tại ô cần điền giá trị, bạn nhập công thức của hàm: “=Vlookup(D4,$B$11:$C$15,2,1)”. Lúc này, hàm Range_lookup có giá trị bằng 1 để dò tìm kết quả chính xác nhất.
Thuật toán của Excel sẽ tiến hành lấy điểm số ở cột điểm và dò theo danh sách phân loại. Khi tìm thấy được giá trị gần nhất, Excel sẽ cho ra kết quả phân loại học lực. Cuối cùng, bạn sao chép công thức và fill bảng tính là có thể in bảng xếp loại học lực cho học sinh trong trường.
Hàm Vlookup trong Excel và những lỗi thường gặp
Trong quá trình sử dụng hàm Vlookup, bạn có thể gặp các lỗi cơ bản và chúng được hiển thị bằng ký hiệu ngay tại ô bị lỗi. Thông thường, các lỗi này sẽ do nhập sai công thức, cụ thể là:
Hiển thị #N/A
Hàm Vlookup trong Excel có một hạn chế đó chính là nó chỉ tìm các giá trị trên cột ngoài cùng bên trái trong Table_array, nếu ngoài vùng này sẽ xuất hiện lỗi #N/A. Bạn có thể sửa lỗi này bằng cách sử dụng kết hợp công thức từ các hàm như: SUM, INDEX, hàm MATCH,…
Ví dụ như bạn cần tìm kiếm dữ liệu trong cột B nhưng lại điền Table_array là A2:C10, hàm Vlookup sẽ xuất hiện lỗi. Lúc này, bạn cần đổi Table_array thành B2:C10, hàm Vlookup sẽ chuyển qua dò dữ liệu ở cột B.
Bên cạnh đó, nếu như hàm không tìm ra kết quả nào khớp với lệch thì lỗi #N/A cũng xuất hiện, do dữ liệu không có trong Table_array. Bạn có thể dùng thêm hàm IFERROR để đổi giá trị #N/A. Bạn cần kiểm tra và đảm bảo các ô dữ liệu không có khoảng trắng ẩn, ký tự không in hoặc khác định dạng.
Hiển thị #REF!
Lỗi này xảy ra khi Col_index_num lớn hơn số cột trong Table_array. Khi đó, bạn cần kiểm tra công thức và số cột trong bảng để chắc chắn rằng Col_index_num bằng hoặc nhỏ hơn số cột trong Table_array. Lỗi này chỉ có duy nhất một trường hợp, bạn kiểm tra lại số cột là có thể sửa lỗi nhanh chóng.
Hiển thị #VALUE!
Lỗi #VALUE! hay còn được gọi là lỗi thiếu dữ liệu, xảy ra khi Col_index_num nhỏ hơn 1. Trong Table_array, cột 1 được mặc định là cột 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, cột 3 là cột thứ 2 bên phải của cột tìm kiếm,…. Chính vì thế, lỗi này sẽ đến từ giá trị Col_index_number trong công thức.
Hiển thị #NAME?
Lỗi hiển thị này thường xuyên xuất hiện, xảy ra khi hàm Lookup_value thiếu dấu ngoặc kép (“). Đối với hàm Vlookup trong Excel, hệ thống chỉ hiểu công thức khi bạn dùng dấu ngoặc kép tìm kiếm giá trị định dạng văn bản. Người dùng rất hay quên dấu ngoặc kép khi điền công thức, nhất là khi sử dụng chung các hàm kết hợp.
Điều nên chú ý khi dùng hàm Vlookup trong Excel
Bất cứ một hàm công thức nào trong Excel cũng đều có quy định riêng. Bạn có thể dựa theo các lưu ý từ nhà phát hành để hạn chế bị lỗi hiển thị khi sử dụng hàm Vlookup. Bạn cần phải lưu ý và hạn chế những mục sau:
Cần cố định ô giá trị Vlookup trong Excel
Trong quá trình sao chép và fill công thức, Excel sẽ mặc định dùng những giá trị kế tiếp nhau để thay thế, điều này sẽ tạo nên lỗi hiển thị. Chính vì thế bạn cần biến Table_array hoặc Lookup_value thành tham chiếu tuyệt đối, bằng cách nhấn phím F4 để đặt dấu đô la ($) trước các cột và hàng .
Nếu như không sử dụng tham chiếu để cố định ô giá trị, dữ liệu trong Lookup_value hoặc Table_array sẽ bị thay đổi, khiến kết quả tìm kiếm sai lệch do thuật toán của Excel khi fill giá trị giữa các ô.
Thay đổi các giá trị số thành dữ liệu văn bản
Trong công thức Vlookup, nếu như trong Table_array, phần dữ liệu số đang để dưới dạng văn bản, nhưng Lookup_value lại hiển thị là dạng số, hàm sẽ trả về kết quả #N/A. Bạn cần đồng bộ các định dạng trước khi áp dụng công thức Vlookup trong Excel để đảm bảo chúng không bị lỗi và bạn phải mất thời gian sửa chữa.
Lọc trước các giá trị bị trùng trong bảng
Nếu như bảng dữ liệu bạn nhập và tiến hành tìm kiếm chứa nhiều giá trị trùng nhau, hàm Vlookup trong Excel sẽ mặc định trả về kết quả đầu tiên mà nó tìm thấy theo thứ tự từ trên xuống dưới.
Bạn cần loại bỏ hết giá trị trùng lặp trong bảng để sửa kết quả, bạn bôi đen bảng dò tìm, chọn “Data”, sau đó nhấn vào mục “Remove Duplicates”. Ngoài ra, bạn cũng có thể dùng công cụ “Pivot Table” để lọc kết quả thủ công.
Không cần quan tâm đến chữ hoa và chữ thường
Hàm Vlookup trong Excel không thực hiện tra cứu dữ liệu dựa trên định dạng chữ hoa và chữ thường. Điều này có nghĩa là cho dù hai bảng dữ liệu cần điền của bạn khác nhau, ví dụ như Mỹ Duyên và MỸ DUYÊN, thì kết quả cuối cùng cho ra vẫn chính xác tuyệt đối.
Kết luận
Hàm Vlookup trong Excel là một trong những công cụ hỗ trợ cho các ngành nghề chuyên dụng như kế toán doanh nghiệp, quản lý doanh nghiệp, phòng nhân sự,…. Bài viết trên của chúng tôi đã cung cấp cho bạn các thông tin về công thức và lưu ý khi dùng hàm Vlookup, bạn có thể tham khảo để hạn chế tối đa các lỗi hiển thị, chúc bạn thành công!
