Hàm VLOOKUP trong Excel là một hàm thông dụng, được dùng để dò tìm và trả về các dữ liệu tương ứng. Bài viết dưới đây Phamemfree sẽ cung cấp những kiến thức bổ ích về hàm này kèm theo ví dụ chi tiết cho các bạn hiểu rõ hơn.
Hàm VLOOKUP là hàm gì?
Hàm VLOOKUP được ứng dụng khi bạn muốn dò dữ liệu theo hàng dọc trong một bảng nào đó và trả về dữ liệu tương ứng với nó theo hàng ngang. LOOKUP là Look Up, trong tiếng anh có nghĩa là tìm kiếm nghĩa, V là từ viết viết tắt của hàng dọc (Vertical).
Trong công việc, VLOOKUP được dùng rất nhiều để tìm tên, số lượng, đơn giá của sản phẩm… dựa vào mã sản phẩm. Hàm cũng được áp dụng tương tự trong việc xếp loại nhân viên dựa trên những tiêu chí đề ra.
Ngược lại với VLOOKUP, HLOOKUP được sử dụng khi dò tìm dữ liệu trong bảng theo hàng ngang, trả về thông tin tương ứng theo hàng dọc. Hai hàm này thường được kết hợp với nhau trong trích xuất dữ liệu.
Công thức hàm VLOOKUP
Công thức tiêu chuẩn của hàm VLOOKUP trong Excel:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
Trong đó:
- Lookup_value: Là giá trị cần dò, có thể điền trực tiếp một giá trị hoặc tham chiếu tới một ô tính trên Excel.
- Table_array: Phạm vi bảng dữ liệu để dò tìm.
- Col_index_num: Thứ tự của cột lấy dữ liệu, tính từ trái qua.
- Range_lookup: Tìm kiếm chính xác hoặc tìm kiếm tương đố. Nếu bỏ qua giá trị này thì mặc định là 1.
- Nếu Range_lookup = 1 (TRUE): Thực hiện dò tìm tương đối.
- Nếu Range_lookup = 0 (FALSE): Dò tìm dữ liệu chính xác.
Ví dụ minh họa cách dùng hàm VLOOKUP
Dưới đây là 2 ví dụ đơn giản miêu tả cách dùng hàm Vlookup để bạn dễ hình dung hơn
Tính phụ cấp theo từng chức vụ
Do tình hình dịch bệnh, công ty quyết định phụ cấp cho nhân viên theo chức vụ tương ứng như bảng dưới đây. Lúc này, dựa trên danh sách nhân viên và chức vụ trong bảng thứ nhất, ta dễ dàng xác định được mức phụ cấp tương ứng cho từng người trong bảng thứ 2
Cách sử dụng hàm như sau:
- Tại ô tính E4, bạn sử dụng công thức: =VLOOKUP(D4,$B$16:$C$21,2,0)
- Dấu $ có tác dụng giữ nguyên các dòng, các cột của bảng khi sao chép tới các ô khác.
- Thứ tự của cột dữ liệu là 2
- Range_lookup = 0 (FALSE) để tiến hành dò tìm chính xác.
Có nghĩa là, bạn sử dụng giá trị của cột Chức Vụ trong bảng 1, sau đó dò tìm hàng tương ứng với từng chức vụ (trong cột Bảng tra phụ cấp) ở cột Phụ cấp
Để áp dụng cho các ô khác, bạn chỉ cần copy và paste công thức hoặc sử dụng tính năng Flash Fill để điền nhanh chóng.
Ví dụ dùng VLOOKUP để xếp loại học sinh
Sau các bài kiểm tra, kết quả bài thi của học sinh như bảng dưới đây. Yêu cầu xếp loại theo điểm số dựa vào dữ liệu trên bảng 2.
- Điền công thức: =VLOOKUP(D4,$B$11:$C$15,2,1) vào ô E4
- Dấu $ được sử dụng để cố định địa chỉ bảng dữ liệu khi copy công thức qua ô khác.
- 2 là số thứ tự cột dữ liệu.
- Range_lookup = 1 để dò tìm điểm có giá trị gần nhất với yêu cầu.
Excel sẽ dùng điểm trung bình và tiến hành dò tìm trong bảng 2. Nguyễn Văn A, có điểm là 9.1, giá trị này gần nhất với giá trị 8.5 và xếp loại tương ứng là Giỏi.
Sao chép công thức cho các ô phía dưới bằng cách dùng Flash Fill và bạn là hoàn thành xong yêu cầu của bài.
Các lỗi hay gặp khi dùng hàm VLOOKUP
Trong quá trình tính toán với Vlookup, chắc chắn bạn sẽ gặp phải một vài lỗi mà không biết lý do. Phamemfree đã liệt kê một vài lỗi thường gặp giúp bạn
Không có giá trị #N/A
#NA là lỗi dò tìm không có giá trị.
Cách khắc phục:Nếu chưa tìm ra lỗi #NA thì bạn có thể sử dụng công thức sau để sửa
=IFERROR (giá trị sẽ hiện nếu công thức gốc có lỗi sai).
Ví dụ:
= IFERROR (VLOOKUP(B5,$G$2:$H$14,2,0)) Tìm giá trị lỗi
Lỗi #REF! không dò tìm được giá trị
Lỗi này xảy ra khi cột dò tìm không có trong bảng dữ liệu dò tìm.
Hướng dẫn khắc phục: Điều chỉnh phạm vi bảng dò tìm sao cho khớp với tham chiếu phạm vi.
Ví dụ: Col_index_num là 4, trong khi Table_array là B2:C10 chỉ có 2 cột. Để dùng hàm Vlookup bạn cần phải điều chỉnh phạm vi bảng sang B2:E10 để khớp với dữ liệu là 4 cột.
Sai giá trị Col_index gây ra lỗi #VALUE!
Nếu giá trị của Col_index_num nhỏ hơn 1, bạn sẽ lập tức bị báo lỗi #VALUE!.
Hướng dẫn khắc phục: Khi gặp lỗi này, kiểm tra lại Col_index_number trong công thức đã đúng hay chưa.
Ví dụ: Công thức để Col_index_num bằng 0, do đó tại ô C13 hiện #VALUE!.
Sai định dạng giá trị #NAME?
Lỗi #NAME? xảy ra khi Lookup_value thiếu ngoặc kép. Với giá trị là định dạng văn bản, bạn cần đề chúng trong ngoặc kép khi dùng công thức.
Khắc phục: Thêm dấu ngoặc kép(“) vào giá trị để Excel hiểu công thức
Ví dụ: Cải xoăn thiếu ngoặc kép và xuất hiện lỗi #NAME?. Bạn thay Cải xoăn thành “Cải xoăn”.
Một số lưu ý khi dùng VLOOKUP
3 lưu ý cần phải nắm để cho ra các kết quả chính xác khi dùng hàm Vlookup
Sử dụng tham chiếu tuyệt đối
Trong quá trình tính toán, bạn sẽ phải copy và paste công thức nhiều lần. Do vậy hãy để Table_array và Lookup_value thành tham chiếu tuyệt đối. Sử dụng ký tự “$” trước các cột và hàng.
Ví dụ minh họa: công thức ô C13 là =VLOOKUP(B13,$B$2:$C$10,2,0). Khi copy công thức này sang ô C4 vẫn sẽ được giữ nguyên.
Nếu không để tham chiếu tuyệt đối, dữ liệu sẽ bị thay đổi thành =VLOOKUP(B14,B3:C11,2,0).
Không lưu trữ giá trị số ở định dạng văn bản
Nếu trong Table_array, các số đang được để ở dạng văn bản nhưng Lookup_value lại để dạng số, bạn sẽ gặp phải lỗi #N/A.
Ví dụ: Tại ô A2:A5 được định dạng là Text nhưng ô A8 lại ở dạng số. Bạn cần phải chuyển định dạng của A2:A5 sang số để Excel thực hiện tính toán.
Bảng dò tìm chứa nhiều giá trị bị trùng nhau
Nếu dữ liệu của bạn chứa các giá trị trùng nhau, hàm VLOOKUP sẽ sử dụng giá trị đầu tiên từ trên xuống.
Trong ví dụ dưới đây có 2 giá trị của Táo là 97, 23. Hàm sẽ trả kết quả số Táo bán được là 97 thay vì 23 hoặc 100.
Giải pháp 1: Loại bỏ các giá trị trùng lặp bằng cách bôi đen bảng dữ liệu >> chọn Data > > Remove Duplicates
Giải pháp 2: Sử dụng tính năng Pivot Table để lọc danh sách kết quả.
Qua những ví dụ chi tiết phía trên mong rằng các bạn đã nắm được cách dùng hàm VLOOKUP trong Excel để trích xuất và tính toán. Để có thể sử dụng thành thạo Excel trong công việc, chúng ta cần phải tìm hiểu thêm về nhiều thủ thuật máy tính khác nữa. Đừng quên theo dõi Phamemfree để học hỏi thêm các hàm tính toán trong Excel nhé!
>> Bạn đang xem bài viết tại chuyên mục Thủ thuật của Phamemfree.
Thông tin liên hệ:
Email: phamemfree.com@gmail.com
Website: https://phamemfree.com
