Hàm VLOOKUP là một trong những hàm được sử dụng rất phổ biến và hữu ích trong Excel, giúp bạn dò tìm và trả về dữ liệu tương ứng.
Bạn đang đọc: Hàm VLOOKUP trong Excel: Cách sử dụng hàm VLOOKUP, có ví dụ cụ thể
Việc sử dụng thành thạo hàm VLOOKUP sẽ giúp ích cho bạn rất nhiều, giúp tiết kiệm được một lượng lớn thời gian so với việc tính toán thủ công mà không dùng hàm.
Trong bài viết dưới đây, Bloggiamgia.edu.vn sẽ giúp bạn nắm được rõ hơn về hàm VLOOKUP trong Excel: Cách sử dụng hàm VLOOKUP, có ví dụ cụ thể. Mời bạn đọc cùng chúng tôi theo dõi bài viết về hàm VLOOKUP dưới đây.
Contents
1. Hàm VLOOKUP là gì?
Hàm VLOOKUP được sử dụng trong Excel với mục đích dò tìm dữ liệu trong một phạm vi, một bảng theo hàng dọc và trả về kết quả tham chiếu có giá trị tương ứng.
Trong thực tế, hàm VLOOKUP thường được sử dụng khi tìm kiếm tên hàng hóa, nhà cung cấp, đơn giá, số lượng… dựa trên mã sản phẩm, mã vạch… hoặc tìm tên, xếp loại học lực học sinh, đánh giá xếp loại nhân viên dựa trên một số tiêu chí được cho trước.
Trong trường hợp, bạn cần tìm dữ liệu trong một bảng theo phạm vị hàng ngang và trả về kết quả tương ứng thì bạn cần phải sử dụng hàm HLOOKUP thay vì dùng hàm VLOOKUP
Về bản chất thì hàm HLOOKUP và VLOOKUP giống nhau, chỉ khác nhau về bảng chứa chứa giá trị tham chiếu là ngang hay dọc.
Hiểu một cách đơn giản thì Look up có nghĩa là tìm kiếm trong Tiếng Anh, V – viết tắt của Vertical có nghĩa là hàng dọc, H – viết tắt của Horizontal có nghĩa là hàng ngang.
2. 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
- Table_array: Bảng giới hạn có 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 Table_array, tính từ trái sang phải.
- Range_lookup: Tìm kiếm chính xác, hay tìm kiếm tương đối đối với bảng giới hạn, tham số này không bắt buộc trong công thức và nếu không ghi thì mặc định là 1.
Nếu Range_lookup: TRUE – tương đương 1: dò tìm tương đối
Nếu Range_lookup: FALSE – tương đương 0: dò tìm tuyệt đối
- Trường hợp bạn muốn copy công thức cho ô dữ liệu khác, bạn cần phải cố định lại Table_array bằng cách thêm trực tiếp $ trước khai báo (VD: $A$17:$B$20), cột hoặc bạn cũng có thể nhấn phím F4 sau khi chọn bảng.
3. Ví dụ cách sử dụng hàm VLOOKUP trong Excel
3.1. Ví dụ 1: Sử dụng hàm VLOOKUP để đánh giá xếp loại học lực của học sinh
Cho bảng điểm như sau
| STT | Họ và tên | Điểm TB | Xếp loại |
| 1 | Nguyễn Hoàng Anh | 5.7 | |
| 2 | Trần Vân Anh | 7.8 | |
| 3 | Nguyễn Quanh Vinh | 9.2 | |
| 4 | Trần Hồng Quang | 7.0 | |
| 5 | Đỗ Thanh Hoa | 6.8 | |
| 6 | Lê Hồng Ngọc | 9.1 | |
| 7 | Đoàn Thanh Vân | 6.9 | |
| 8 | Ngô Ngọc Bích | 8.7 | |
| 9 | Hoàng Thu Thảo | 0 | |
| 10 | Đinh Minh Đức | 5.9 |
Và bảng quy định xếp loại học lực như sau
| Quy định xếp loại | |
| 0 | Yếu |
| 5.5 | Trung bình |
| 7.0 | Khá |
| 8.5 | Giỏi |
Để xếp loại học lực của học sinh trong trường hợp này, ta sẽ dùng hàm VLOOKUP. Do bảng quy định xếp loại học sinh đã được sắp xếp theo thứ tự tăng dần từ Yếu đến Giỏi nên với bảng này ta có thể dùng dò tìm tương đối.
Hai bảng này trong Excel được trình bày như sau:
Trong trường hợp này
- Giá trị cần dò tìm – Lookup_array nằm ở cột C và bắt đầu từ dòng thứ 3
- Phạm vi tìm kiếm – Table_array nằm trong phạm vi từ $A$17:$B$20
- Thứ tự cột chứa giá trị dò tìm – Col_index_num là 2
Tại ô D3, bạn nhập công thức:
=VLOOKUP(C3; $A$17:$B$20;2;1)
Đây là công thức dò tìm tương đối, bạn cũng có thể dùng công thức dò tìm tuyệt đối trong trường hợp này, hoặc nếu bảng xếp loại chưa được sắp xếp theo thứ tự. Khi đó công thức tại ô D3 sẽ là:
=VLOOKUP(C3; $A$17:$B$20;2;0)
Excel sẽ lấy Điểm TB ở cột C3 và dò trong bảng Quy định xếp loại. Khi thấy giá trị gần nhất của ô C3 trong bảng (ở đây là 5.5), Excel sẽ trả về kết quả tương ứng ở cột 2 là Trung bình.
Sau đó, bạn nhấp chuột vào ô D3 sẽ thấy xuất hiện ô vuông nhỏ ở góc dưới phía bên phải, bạn chỉ cần nhập vào đó và kéo xuống hết bảng để sao chép công thức xếp loại cho các học sinh còn lại.
Cuối cùng, ta được kết quả xếp loại học sinh sau đây
Lưu ý, khi sử dụng công thức VLOOKUP trong trường hợp này bạn phải cố định bảng Table_array để bảng giới hạn không bị thay đổi khi bạn kéo sao chép công thức.
3.2. Ví dụ 2: Dùng hàm VLOOKUP dò tìm tuyệt đối lấy dữ liệu
Giả sử bạn có một bảng dữ liệu theo dõi thi công của nhân viên có lưu trữ dữ liệu ngày thi công, mã nhân viên. Giờ bạn muốn điền tên nhân viên và hệ số công việc cho khớp với dữ liệu thi công của từng ngày thì phải làm như thế nào?
Cho bảng dữ liệu thi công công việc như sau
Nếu bạn muốn điền thông tin nhân viên cho khớp với mã nhân viên và ngày thi công, tại ô C3 bạn nhập vào công thức dò tìm tuyệt đối như sau
=VLOOKUP($B3;$A$12:$B$14;2;0)
Sau đó nhấn Enter và sao chép công thức cho các ô còn lại trong bảng
Để điền thông tin Hệ số công việc cho từng nhân viên, tại ô D3 bạn nhập vào công thức dò tìm tuyệt đối
=VLOOKUP($B3;$A$12:$C$14;3;0)
Sau đó, nhấn Enter và kéo xuống để sao chép công thức cho các ô còn lại trong bảng và được kết quả dưới đây
4. Một số lỗi thường gặp khi sử dụng hàm VLOOKUP
4.1. Lỗi #N/A
Lỗi #N/A là một lỗi thường gặp khi sử dụng hàm VLOOKUP, NA trong tiếng Anh có nghĩa là “Not Available”. Khi bắt gặp lỗi này bạn cần phải kiểm tra ngay những thứ sau đây để khắc phục lỗi nhanh chóng nhất.
Tìm hiểu thêm: Mách bạn cách lựa chọn đèn chùm cổ điển phù hợp cho từng không gian
- Lookup_value – lỗi chính tả trong giá trị được tìm kiếm
Việc đầu tiên bạn cần làm là kiểm tra lỗi chính tả của giá trị cần tra cứu
Một lỗi mà nhiều người dùng Excel thường hay mắc phải đó là giá trị cần tìm thừa dấu cách ở cuối. Đối với trường hợp này thì bạn không thể dùng mắt thường để tìm ra được. Khi đó, bạn nên dùng hàm LEN để phát hiện lỗi thừa dấu cách, ký tự khoảng trắng trong một ô.
Ví dụ
Trong ví dụ trên, nếu chỉ nhìn bằng mắt thường thì A18 và A19 có số ký tự bằng nhau, nhưng thực tế số ký tự của A19 là 5, còn của A18 là 4.
Để đảm bảo khắc phục được lỗi này thì tốt nhất bạn nên dùng hàm TRIM để loại bỏ các khoảng trắng trong ô giá trị dò tìm. Khi đó, công thức ví dụ là
=VLOOKUP(TRIM(lookup_value); table_array; col_index_num; [range_lookup])
4.2. Lỗi #N/A khi sử dụng hàm VLOOKUP để dò tìm giá trị tương đối
Nếu bạn dùng hàm VLOOKUP với tham số tra cứu là gần đúng ([range_lookup] là 1 – TRUE hoặc bỏ qua), thì lỗi #N/A có thể xảy ra do 1 trong 2 lỗi sau đây
Lookup_value nhỏ hơn giá trị nhỏ nhất trong Table_array.
Cho ví dụ dưới đây, mức doanh thu là 95%, nhỏ hơn số nhỏ nhất trong cột chứa dữ liệu tra cứu là 100.
Cột chứa dữ liệu tra cứu trong Table_array không được sắp xếp theo thứ tự tăng dần.
Lấy ví dụ bảng xếp loại học sinh ở ví dụ 1, ta thấy Table_array không được sắp xếp theo thứ tự tăng dần, nên khi dùng công thức VLOOKUP tại ô E3 xuất hiện lỗi #N/A.
4.3. Lỗi #N/A không phải lỗi
Một ràng buộc khi sử dụng hàm VLOOKUP là nó chỉ có thể dò tìm giá trị trong cột ngoài cùng của bảng Table_array. Nếu giá trị dò tìm không xuất hiện trong cột này thì sẽ xuất hiện lỗi #N/A.
4.4. Lỗi #REF!
Nếu Col_index_num lớn hơn số cột trong Table_array, lúc đó kết quả sẽ xuất hiện lỗi #REF!. Để khắc phục lỗi này bạn phải kiểm tra lại công thức xem Col_index_num có nhỏ có nhỏ hơn hoặc bằng số cột
trong Table_array không.
4.5. Lỗi #VALUE!
Lỗi #VALUE! xuất hiện khi sử dụng hàm VLOOKUP trong Excel nếu Lookup_value có chứa từ 265 ký tự trở lên.
Một trường hợp nữa có thể xuất hiện lỗi #VALUE! là công thức có Col_index_num nhỏ hơn 1
Trong ví dụ dưới đây, Col_index_num nhỏ hơn 1 dẫn đến kết quả xuất hiện lỗi #VALUE!
4.6. Lỗi #NAME?
Lỗi #NAME? xảy ra nếu Lookup_value thiếu dấu ngoặc kép (“”) khi công thức được dùng để tìm kiếm giá trị định dạng dạng văn bản (Text).
Hoặc lỗi #NAME? cũng có thể xảy ra nếu bạn vô tình viết sai chính tả tên hàm.
Hàm VLOOKUP được sử dụng rất phổ biến và khá quan trọng trong Excel. Để đảm bảo sử dụng đúng và không bị mắc lỗi thì bạn nên lưu ý những điều sau đây
Nếu bảng của bạn có nhiều cột hoặc nhiều mục tương tự, chỉ khác nhau chữ hoa và chữ thường thì công thức VLOOKUP sẽ trả về giá trị được tìm kiếm đầu tiên.
Để giải quyết cho vấn đề này thì bạn nên sử dụng kết hợp các công thức Excel khác để có thể thực hiện tra cứu theo chiều dọc như LOOKUP, SUMPRODUCT, INDEX/MATCH kết hợp với hàm EXACT.
>>>>>Xem thêm: Top 20 mẫu phối màu sơn cho nhà cấp 4, tô điểm cho căn nhà
Khi bạn copy và dán công thức cho một ô khác, để đảm bảo rằng công thức không bị thay đổi bạn phải cố định Table_array và cột chứa Lookup_value lại bằng cách đặt dấu $ trước các cột hoặc hàng.
Như ví dụ dưới đây, công thức tại ô E3 là
=VLOOKUP($D3;$B$17:$C$20;2;1)
Khi copy công thức cho ô E4 thì Table_array sẽ giữ nguyên và cột D cũng được cố định.
Nếu bạn không chuyển thành tham chiếu tuyệt đối thì Table_array và Lookup_value sẽ bị thay đổi và ảnh hưởng đến kết quả.
Nếu trong Table_array, dữ liệu số đang để dưới dạng văn bản và Lookup_value được định dạng số thì khi đó lỗi #N/A sẽ xuất hiện.
Do đó, bạn phải đảm bảo rằng định dạng ở Table_array và Lookup_value phải giống nhau cho các dữ liệu số.
Table_array chứa những giá trị trùng lặp:
Nếu Table_array chứa những giá trị trùng lặp với nhau thì hàm VLOOKUP sẽ trả kết quả về giá trị đầu tiên mà nó tìm được khi tham chiếu từ trên xuống.
Trong trường hợp này bạn có 2 cách để giải quyết
- Loại bỏ giá trị trùng lặp bằng cách bôi đen Table_array và chọn Data > Remove Duplicates
- Dùng Pivot Table để lọc ra danh sách kết quả
Table_array cần được sắp xếp trước khi áp dụng tìm kiếm tương đối
Như đã nói ở trên, nhiều bạn sử dụng hàm VLOOKUP để dò tìm tương đối nhưng kết quả lại xuất hiện lỗi #N/A và không hiểu được lý do tại sao.
Do đó, trước khi áp dụng tìm kiếm tương đối thì bạn nên sắp xếp dữ liệu trong Table_array theo thứ tự tăng dần để tránh gặp phải lỗi này.
5. Kết luận
Hàm VLOOKUP là một hàm được sử dụng rất phổ biến và hữu ích trong Excel, giúp bạn dò tìm và trả về dữ liệu tương ứng.
Để hàm VLOOKUP được sử dụng đúng như mong đợi thì bạn cần phải nắm rõ và hiểu được bản chất của tất cả các tham số trong hàm và tránh được một số lỗi thường gặp khi sử dụng.
Bài viết trên đã cung cấp toàn bộ thông tin về hàm VLOOKUP trong Excel: Cách sử dụng hàm VLOOKUP, có ví dụ cụ thể. Hy vọng qua bài viết trên bạn sẽ hiểu rõ hơn về cách sử dụng hàm VLOOKUP trong Excel
