Hàm Vlookup trong Excel được phần đông người chú ý và khá phổ biến, vì vậy hôm nay Blog Phần Mềm sẽ cùng tìm và phân tích chi tiết cách dùng hàm Vlookup, một số lỗi thường gặp và cách khắc phục của hàm này nhé. Nào cùng theo dõi bài viết!
Nội dung bài viết
Hàm Vlookup trong Excel để làm gì?
Hàm Vlookup là một hàm sử dụng để tra cứu thông tin cần tìm trong bảng, một phạm vi theo hàng hoặc cột, bảng nào đó.
Xem thêm: Cách dùng hàm COUNTIF trong Excel kèm ví dụ dễ hiểu
Ví dụ: Tra cứu một vài lượng iPhone 11 được bán trong tháng 8/2020 của tổ chức cổ phần Thế Giới Di Động, hoặc tra cứu tên cửa hàng trưởng của Thế Giới Di Động tại TPHCM.
Công thức của hàm Vlookup là: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Trong đó:
– Lookup_value: là kết quả cần dò tìm
– table_array: là bảng cần dò tìm
– col_index_num: là vị trí cột cần lấy giá trị
– range_lookup: nhập vào số 0 để tìm chuẩn xác và nhập vào số 1 để tìm gần đúng (thông thường nhập số 0)
Công thức hàm Vlookup trong Excel
Trước khi vào tìm hiểu hàm Vlookup khô khan thì mình đi mua sắm tí nhé! Hôm nay cùng mình đi mua snack nghen!
Khi bạn vào một cửa hàng để mua snack, việc trước tiên là tìm được line (hàng) có snack trong khu vực nào của cửa hàng, vị trí chuẩn xác chưng bày snack trong line đó, và quyết định mua, một là mua snack bất kì bạn gặp ngẫu nhiên, miễn là snack bạn có thể mua (tương đối), hai là mua snack đúng loại bạn thích từ thương hiệu, loại, kích thước và hương vị (giá trị tuyệt đối).
Từ đó chúng ta có công thức mua snack như sau: Tìm được line có snack > Biết hàng đó trong khu vực nào của cửa hàng > Vị trí chính sát chưng bày snack trong line đó > đưa ra quyết đinh mua snack theo hướng tương đối hay tuyệt đối.
1. Tìm được line có snack > giá trị cần dò tìm (Lookup_value) => G6
2. Trong khu vực nào có chứa snack của shop > Bảng chứa giá trị cần dò tìm (Table_array) => (B1:D4)
3. Vị trí chuẩn xác chưng bày snack trong line đó > Vị trí cột cần lấy giá trị trong bảng chứa kết quả cần dò tìm (Col_index_num) => 2
Lưu ý: Đếm số thứ tự cột trong bảng chứa giá trị cần dò tìm bạn phải đếm cột trái sang phải, vị trí trước tiên bên trái của bảng bạn đặt số 1 và đếm theo trình tự tăng dần đến hết bảng.
4. Đưa ra quyết định mua tương đối hay tuyệt đối > Tìm kiếm tương đối hoặc tuyệt đối (Col_index_num) =>1
Lưu ý:
Tìm kiếm tương đối hoặc tuyệt đối thể hiện bằng số 1 hoặc 0 hoặc chữ TRUE và FALSE (1=TRUE hoặc 0=FALSE)
Nói dễ hiểu giá trị tương đối là khi ta đi mua snack là mua chứ không phải đúng thương hiệu mình thích, loại, vị, cỡ ,…! Còn nếu ngược bạn phải mua đúng snack nhãn hiệu mình thích, loại, vị, cỡ,… Thì bạn hãy chọn tuyệt đối cho bí quyết của mình nè!
Các bạn gõ dấu “=” sau đấy nhập VLOOKUP(
Và ta có công thức hoàn chỉnh cho công cuộc đi mua snack: = Vlookup(G6,B1:D4,2,1)
Vì sao phải khóa bảng đối chiếu giá trị tìm kiếm?
Khóa bảng tìm kiếm để máy tính hiểu rằng chỉ tìm giá trị trong bảng đã khóa, không tìm kết quả ra ngoài.
Trong trường hợp này hàm công thức đang tự đối chiếu kết quả rỗng ngoài bảng nên sẽ báo lỗi ngay khi mà bạn Enter đấy!
Bị lỗi #N/A vậy nên cần khóa bảng để tìm đúng kết quả và tránh mắc lỗi ở các trường hợp căn bản. Cẩn thận bạn nhé!
Cách thực hiện khóa bảng đối chiếu dữ liệu:
- Nhấn F4 hoặc Fn+ F4 (cho một vài Laptop) để khóa bảng chứa kết quả cần dò tìm.
- Khóa bảng chứa giá trị cần dò tìm sẽ tạo điều kiện cho công thức của bạn tìm đúng giá trị trong bảng đã khóa không đối chiếu ra ngoài ô khác. Và tránh lỗi #NA khi không tìm thấy kết quả cần tìm
- Khi khóa bảng sẽ có biểu thức hiển thị như sau: $G$2:$H$14.
Các lỗi thường gặp khi sử dụng hàm Vlookup
Lỗi #N/A
Một ràng buộc của hàm VLOOKUP là nó chỉ có khả năng tìm các thành quả trên cột ngoài cùng bên trái trong Table_array, nếu không sẽ hiển thị lỗi #N/A. Lúc này bạn hãy cân nhắc dùng hàm INDEX kết hợp hàm MATCH.
Như ví dụ bên dưới, Table_array là A2:C10, nên hàm VLOOKUP sẽ tìm kiếm trong cột A. Để sửa trường hợp này, bạn đổi Table_array thành B2:C10, hàm VLOOKUP sẽ tìm kiếm trong cột B.
Ngoài ra, nếu như không tìm thấy mục đích khớp chính xác sẽ trả về hàm sẽ trả về lỗi #N/A do dữ liệu không có trong Table_array. Lúc này bạn có thể sử dụng hàm IFERROR để đổi #N/A thành kết quả khác.
Như VD bên dưới, “Rau muống” không có trong bảng dò tìm nên hàm VLOOKUP sẽ không tìm thấy.
Nếu như bạn chắc chắn rằng dữ liệu có trong Table_array của mình và hàm VLOOKUP không tìm được được, hãy kiểm duyệt lại rằng các ô dữ liệu được tham chiếu không chuẩn bị khoảng trắng ẩn hoặc ký tự không in. Hơn nữa, hãy đảm bảo rằng các ô dữ liệu tuân theo đúng định dạng.
Lỗi #REF!
Nếu như Col_index_num lớn hơn số cột trong Table_array, bạn có thể nhận được thành quả lỗi #REF!. Lúc này, bạn hãy kiểm tra lại công thức để đảm bảo Col_index_num bằng hoặc nhỏ hơn số cột trong Table_array.
Như VD phía dưới, Col_index_num là 3, trong khi Table_array là B2:C10 chỉ có 2 cột.
Lỗi #VALUE!
Nếu Col_index_num nhỏ hơn 1 trong bí quyết, bạn có thể nhận thành quả lỗi #VALUE!.
Trong Table_array, cột 1 là cột tìm kiếm, cột 2 là cột trước tiên ở bên phải của cột tìm kiếm, v.v… thế nên khi xuất hiện lỗi này, hãy kiểm tra lại giá trị Col_index_number trong công thức.
Như VD phía dưới, Col_index_num bằng 0 dẫn đến việc xuất hiện lỗi #VALUE!.
Lỗi #NAME?
Lỗi #NAME? xuất hiện khithủ thuật excel Lookup_value thiếu dấu ngoặc kép (“). Để tìm kiếm giá trị định dạng văn bản (Text), bạn sử dụng dấu ngoặc kép để Excel có khả năng hiểu bí quyết.
Như VD bên dưới, Cải xoăn không bỏ vào dấu ngoặc kép (“) sẽ làm xuất hiện lỗi #NAME?. Bạn sửa lỗi bằng việc thay Cải xoăn thành “Cải xoăn”.
Một số lưu ý khi dùng hàm Vlookup
Dùng tham chiếu tuyệt đối
Trong quá trình copy công thức, hãy biến Table_array hoặc Lookup_value thành tham chiếu tuyệt đối bằng cách đặt dấu đô la ($) trước các cột và hàng để công thức không bị chỉnh sửa.
Như ví dụ dưới, ta có cú pháp tại ô C13 là =VLOOKUP(B13,$B$2:$C$10,2,0). Khi copy công thức cho ô C4 Table_array sẽ giữ nguyên.
Nếu không chuyển đổi thành tham chiếu tuyệt đối, Lookup_value hoặc Table_array sẽ bị chỉnh sửa, làm kết quả tìm kiếm bị sai lệch.
Như VD dưới, ta có bí quyết tại ô C13 là =VLOOKUP(B13,B2:C10,2,0). Khi copy bí quyết 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, dữ liệu số đang để dưới dạng văn bản và Lookup_value lại là dạng số thì hàm VLOOKUP sẽ trả về lỗi #N/A.
Như VD dưới, ta có dữ liệu tại ô A2:A5 đang ở dạng văn bản nhưng Lookup_value tại ô A8 đang ở dạng số.
Trong trường hợp này, hãy chuyển định dạng ô A2:A5 thành dạng số và hàm sẽ trả về kết quả thông thường.
Bảng dò tìm chứa những kết quả bị trùng
Nếu như bảng biểu của bạn chứa nhiều giá trị trùng nhau, hàm VLOOKUP sẽ trả về kết quả trước tiên mà nó tìm thấy từ cao xuống thấp.
Như ở VD dưới, trong bảng ta có 2 kết quả ứng với Táo là 97 và 23. Hàm VLOOKUP sẽ trả về kết quả 97 vì đấy là giá trị đầu tiên nó tìm thấy
Xem thêm: 3 cách dùng hàm MIN, MAX trong Excel có thể bạn chưa biết
Giải pháp 1: nếu bạn mong muốn loại bỏ kết quả trùng lặp, bạn bôi đen bảng dò tìm và chọn Data > Remove Duplicates
Giải pháp 2: sử dụng Pivot Table để lọc ra danh sách kết quả
5 điểm hạn chế của hàm Vlookup
Chỉ hỗ trợ tham chiếu từ trái sang phải
Phần đông người dùng cho rằng Vlookup chỉ công việc theo chiều từ trái sang phía phải, chứ không có chiều trái lại. Một số khác cho rằng để có thể dùng Vlookup tham chiếu từ phải sang thì bắt buộc phải kết hợp nhiều công thức lại với nhau.
Thực tế mà nói, việc kết hợp công thức là một thao tác khá là rắc rối và không hề dễ dàng gì. Vì vậy đây vẫn được coi là một hạn chế lớn của Vlookup
Giải pháp: dùng công thức Match & Index
Chỉ hoạt động đạt kết quả tốt với các kết quả riêng biệtVlookup có xu thế chỉ quan tâm đến kết quả tham chiếu đầu và bỏ quên các dòng khác chứa kết quả tương tự. Nếu dữ liệu của bạn vẫn chưa có quá là nhiều sự phân hóa thì bạn bắt buộc phải lách luật một tí để Vlookup có khả năng tham chiếu được kết quả bạn cần.
Giải pháp: Tạo một bảng Pivot chứa các giá trị riêng biệt và khi đó bạn sẽ sử dụng hàm Vlookup một cách thông thường
Số thứ tự cột tham chiếu luôn cố định
Việc số thứ tự của cột tham chiếu được điền thủ công vào thông tin của bí quyết khiến cho thực hành các bước sao chép sang ô tính khác gặp nhiều chông gai và bất tiện.
Giải pháp: bạn sẽ dùng số thứ tự biến đổi liên tục thông qua hàm Match
Mặc định thiết lập “approximate match”
Nếu như để ý kỹ, bạn sẽ thấy rằng điều kiện kiểm duyệt tham chiếu ở trong nội dung hàm Vlookup là yếu tố tùy chọn (được ký hiệu bằng hai dấu ngoặc vuông []) nên nếu bạn không nhập nội dung gì vào đó thì bí quyết sẽ tự mặc định là “approximate match”
Bạn thật sự cần phải quan tâm nếu không mong muốn mục đích tham chiếu trở nên sai lệch
Giải pháp: Luôn quan tâm phải thiết lập là “exact match” để có kết quả tham chiếu chính xác nhất, tuy nhiên nếu bạn vẫn mong muốn chọn cài đặt mặc định “approximate match” thì phải thật sự cẩn thận
Làm tốc độ góp ý của bảng tính bị chậm đi
Một vài ý kiến cho rằng việc sử dụng quá là nhiều hàm Vlookup để tham chiếu sẽ làm giảm hiệu suất của chương trình, thậm chí gây ra hiện tượng crash.
Giải pháp: Bạn không bị giới hạn số lượng hàm Vlookup cần dùng, thế nhưng cố gắng thay thế chúng bằng lệnh Paste Special để thay thế các hàm bằng kết quả cố định, điều đấy sẽ giúp chương trình hoạt động có kết quả tốt hơn.
Tổng kết
Hi vọng với bài viết sẻ chia này các bạn sẽ nắm được chi tiết về hàm Vlookup trong Excel và cách dùng. Trong thời gian tới Blog Phần Mềm sẽ chia sẻ thêm về hàm Hlookup và các hàm kết hợp với hàm Vlookup và Hlookup thường được sử dụng trong công việc. Mọi người hãy tiếp tục theo dõi nhé! Cảm ơn các bạn đã theo dõi bài viết của blogphanmem.vn
Nguồn: Tổng hợp
