Đăng 7 tháng trước
499.285
Bạn đang đọc: Hàm VLOOKUP trong Excel: Cách sử dụng và ví dụ cụ thể
Mục lục bài viết
Hàm VLOOKUP là một hàm đặc biệt hữu ích trong Excel giúp bạn dò và trả về dữ liệu tương ứng. Điện máy XANH 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 triển khai trên phiên bản Excel năm nay. Bạn hoàn toàn có thể vận dụng tương tự như trên các phiên bản Excel khác như : 2007, 2010, 2013, 2017 và Microsoft Excel 365 .
1Hàm VLOOKUP là gì?
Hàm VLOOKUP được sử dụng khi bạn cần dò tìm dữ liệu trong một bảng, một phạm vi theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang tương ứng.
Trong trong thực tiễn, hàm VLOOKUP cực kỳ thông dụng khi tìm tên mẫu sản phẩm, đơn giá, số lượng, .. dựa trên mã vạch, mã loại sản phẩm, … hoặc tìm tên nhân viên cấp dưới, xếp loại nhân viên cấp dưới dựa trên các tiêu chuẩn trên .
trái lại, khi bạn cần dò tìm dữ liệu trong một bảng, một khoanh vùng phạm vi theo hàng ngang và trả về dữ liệu tương ứng theo hàng dọc thì hãy sử dụng hàm HLOOKUP .
LOOKUP là Look Up nghĩa là tìm kiếm trong tiếng Anh. V là viết viết tắt của Vertical – hàng dọc và H là viết tắt của Horizontal – hàng ngang.
2Công thức hàm VLOOKUP
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 đúng chuẩn .
Nếu bạn copy công thức cho các ô dữ liệu khác, bạn cần sử dụng dấu USD để cố định và thắt chặt Table_array đề số lượng giới hạn dò tìm bằng cách thêm trực tiếp trước khai báo ( VD : USD H USD 6 : USD J $ 13 ), cột hoặc sử dụng nút F4 sau khi chọn bảng .
Xem thêm: Hàm SUMIF
3Ví dụ hàm VLOOKUP
Bạn hoàn toàn có thể truy vấn vào đây để tải về các file ví dụ bên dưới
Ví dụ 1: Tính phụ cấp theo chức vụ
Do tình hình Covid-19, công ty quyết định hành động phụ cấp cho nhân viên cấp dưới theo chức vụ tương ứng như bảng 2 ( B16 : C21 ). Lúc này, dựa vào list nhân viên cấp dưới cùng với chức vụ có sẵn ở bảng 1, ta sẽ xác lập mức phụ cấp tương ứng .
Cách thực thi là bạn sẽ dò tìm giá trị của chức vụ của nhân viên cấp dưới tại bảng 1, sau đó dò tìm tại cột 1 trong bảng 2 từ trên xuống dưới. Khi bạn tìm thấy giá trị, bạn sẽ lấy giá trị tương ứng tại cột 2 của bảng 2 để điền vào bảng 1 .
Với một list nhân viên cấp dưới vài trăm, một ngàn người thì bạn không hề làm thủ công bằng tay như vậy. Đó chính là cách hàm VLOOKUP phát huy công dụng .
Tại ô E4, bạn điền công thức : = VLOOKUP ( D4, USD B USD 16 : USD C USD 21,2,0 )
- Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy 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) để dò tìm chính xác.
Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã triển khai xong việc tính phụ cấp theo chức vụ nhanh gọn .
Ví dụ 2: Xếp loại học sinh theo điểm số
Sau kỳ kiểm tra, ta có hiệu quả bài thi tương ứng với học viên như bảng 1. Ta cần xếp loại theo điểm số dựa trên bảng 2 ( B11 : C15 ) .
Tại ô E4, bạn điền công thức : = VLOOKUP ( D4, USD B USD 11 : USD C USD 15,2,1 )
- Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy 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) để dò tìm điểm số có giá trị gần nhất.
Excel sẽ lấy điểm số ở cột D4 và dò trong bảng 2. Khi thấy giá trị gần nhất của D4 trong bảng ( ở đây là 8.5 ), Excel sẽ trả về hiệu quả tương ứng ở cột 2 là Giỏi .
Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã triển khai xong việc xếp loại học viên theo điểm số nhanh gọn .
4Các lỗi thường gặp khi dùng hàm VLOOKUP
Lỗi #N/A
Một ràng buộc của hàm VLOOKUP là nó chỉ hoàn toàn có thể tìm các giá trị trên cột ngoài cùng bên trái trong Table_array, nếu không sẽ Open lỗi # N / A. Lúc này bạn hãy xem xét sử dụng hàm INDEX phối 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 không tìm thấy hiệu quả khớp đúng mự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 hoàn toàn có thể sử dụng hàm IFERROR để đổi # N / A thành giá trị khác .
Như ví dụ 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 bạn chắc như đinh 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 tra lại rằng các ô dữ liệu được tham chiếu không có khoảng chừng trắng ẩn hoặc ký tự không in. Ngoài ra, hãy bảo vệ rằng các ô dữ liệu tuân theo đúng định dạng .
Lỗi #REF!
Nếu Col_index_num lớn hơn số cột trong Table_array, bạn sẽ nhận được giá trị lỗi # REF !. Lúc này, bạn hãy kiểm tra lại công thức để bảo vệ Col_index_num bằng hoặc nhỏ hơn số cột trong Table_array .
Như ví dụ bên 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 công thức, bạn sẽ nhận giá trị lỗi # VALUE ! .
Trong Table_array, cột 1 là cột tìm kiếm, cột 2 là cột tiên phong ở bên phải của cột tìm kiếm, v.v… Vì vậy khi Open lỗi này, hãy kiểm tra lại giá trị Col_index_number trong công thức .
Như ví dụ bên dưới, Col_index_num bằng 0 dẫn đến việc Open lỗi # VALUE ! .
Lỗi #NAME?
Lỗi # NAME ? Open khi 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 dùng dấu ngoặc kép để Excel hoàn toàn có thể hiểu công thức .
Như ví dụ bên dưới, Cải xoăn không bỏ vào dấu ngoặc kép ( ” ) sẽ làm Open lỗi # NAME ?. Bạn sửa lỗi bằng cách thay Cải xoăn thành ” Cải xoăn ” .
5Một số lưu ý khi dùng hàm VLOOKUP
Sử dụng tham chiếu tuyệt đối
Trong quy 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 ( USD ) trước các cột và hàng để công thức không bị đổi khác .
Như ví dụ dưới, ta có công thức tại ô C13 là = VLOOKUP ( B13, USD B USD 2 : USD C USD 10,2,0 ). Khi copy công thức cho ô C4 Table_array sẽ giữ nguyên .
Nếu không quy đổi thành tham chiếu tuyệt đối, Lookup_value hoặc Table_array sẽ bị biến hóa, làm tác dụng tìm kiếm bị xô lệch .
Như ví dụ dưới, ta có công thức tại ô C13 là = VLOOKUP ( B13, B2 : C10, 2,0 ). Khi copy công thức 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ư ví dụ 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ề hiệu quả thông thường .
Bảng dò tìm chứa những giá trị bị trùng
Nếu bảng biểu của bạn chứa nhiều giá trị trùng nhau, hàm VLOOKUP sẽ trả về tác dụng tiên phong mà nó tìm thấy từ trên xuống dưới .
Như ở ví dụ dưới, trong bảng ta có 2 giá trị ứng với Táo là 97 và 23. Hàm VLOOKUP sẽ trả về tác dụng 97 vì đó là giá trị tiên phong nó tìm thấy
Giải pháp 1: Nếu bạn muốn loại bỏ giá trị 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ả
Trên đây là bài viết san sẻ cho bạn cách dùng hàm VLOOKUP trong Excel. Mong rằng những thông tin này đã giúp ích cho bạn trong việc hiểu và sử dụng hàm VLOOKUP cho việc làm của mình .
Source: https://bem2.vn
Category: TỔNG HỢP
