Hàm Vlookup trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc (theo cột), nó giúp chúng ta thống kê, dò tìm dữ liệu theo cột một cách nhanh chóng và tiện lợi. Đây là một trong những hàm phổ biến và hữu ích nhất trong Excel, nhưng lại ít người hiểu về nó. Trong bài viết này, Hocexcelcoban.com sẽ giúp bạn hiểu và sử dụng hàm Vlookup một cách thành thạo qua các ví dụ thực tế nhất.
1. Chức năng của hàm Vlookup trong Excel.
Chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.
2. Cú pháp hàm vlookup trong excel.
VLOOKUP(LOOKUP_VALUE, TABLE_ARRAY, COL_INDEX_NUM, [RANGE_LOOKUP] )
Trong đó:
- Lookup_value (bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.
- Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.
- Row_index_num (bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.
- Range_lookup (tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.
- Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
- Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A.
3. Ví dụ về hàm VLOOKUP trong Excel.
3.1. Các trường hợp sử dụng hàm Vlookup trong Excel.
Hàm Vlookup trong Excel có 2 cách sử dụng. Khi khi lập công thức ta có thể lựa chọn 1 trong 2 bằng cách gán giá trị cho Range_lookup là TRUE (1) hoặc FALSE (0).
- Hàm Vlookup để tìm kiếm chính xác (Range_lookup = FALSE). Cách tìm kiếm này được áp dụng trong những trường hợp cần tìm kiếm kết quả khớp chính xác. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ báo lỗi. VD: Tìm kiếm tên, tuổi, quê quán, … với mã nhân viên khớp với mã nhân viên cho trước.
- Hàm Vlookup để tìm kiếm tương đối (Range_lookup = TRUE). Cách tìm kiếm này được áp dụng trong những trường hợp tìm kiếm theo khoảng giá trị kết quả khớp tương đối. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value. VD: Xếp loại học lực của học sinh theo điểm tổng kết.
3.2. Ví dụ về hàm Vlookup để tìm kiếm chính xác.
VD: Giả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, chức vụ. Một bảng khác lưu trữ mã nhân viên, quê quán, trình độ học vấn. Giờ bạn muốn điền thông tin quê quán, trình độ học vấn cho từng nhân viên thì phải làm như thế nào?
Hàm vlookup trong excel
Ảnh minh hoạ sử dụng hàm Vlookup để tìm kiếm chính xác.
Để điển thông tin quê quán cho nhân viên, tại ô E4, ta nhập công thức dò tìm chính xác như sau: =VLOOKUP(B4,$B$17:$D$26,2,0).
Trong đó:
- B4: Là giá trị cần đối chiếu.
- $B$17:$D$26: Là bảng dò tìm, địa chỉ của bảng dò tìm phải là địa chỉ tuyệt đối.
- 2: Số thứ tự cột dữ liệu trên bảng dò tìm.
- 0: Kiểu tìm kiếm chính xác.
Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những nhân viên còn lại. Quan sát hình để hiểu rõ về công thức hơn.
Sử dụng hàm vlookup tìm kiếm tuyệt đối.
Ảnh mình hoạ sử dụng hàm Vlookup để tìm kiếm chính xác trong Excel.
Chúng ta cũng thực hiện tương tự như vậy vậy để điền trình độ nhân viên, với công thức ở ô F4 là: =VLOOKUP(B4,$B$17:$D$26,3,0)
Trong đó:
- B4: Là giá trị cần đối chiếu.
- $B$17:$D$26: Là bảng dò tìm.
- 3: Số thứ tự cột dữ liệu trên bảng dò tìm.
- 0: Kiểu tìm kiếm chính xác.
Quan sát hình bên dưới để hiểu cách tìm kiếm và lấy giá trị của hàm Vlookup khi tìm kiếm chính xác.
Hàm vlookup trong excel
Ảnh minh họa sử dụng hàm Vlookup để tìm kiếm chính xác.
3.3. Hàm Vlookup để tìm kiếm tương đối.
Tìm kiếm tương đối chỉ có thể áp dụng khi giá trị cần dò tìm trong table_array đã được sắp xếp theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn có thể dùng dò tìm tương đối, khi đó nó tương tự như dùng hàm IF vô hạn vậy.
VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách:
Hàm vlookup trong excel
Ảnh minh hoạ sử dụng hàm Vlookup trong Excel để tìm kiếm tương đối.
Giờ ta sẽ sử dụng hàm VLOOKUP để nhập xếp loại cho các học sinh. Bạn để ý thấy rằng bảng Quy định xếp loại đã được sắp xếp theo thứ tự từ thấp đến cao (từ yếu đến giỏi) nên trong trường hợp này ta có thể dùng dò tìm tương đối.
Tại ô E4, ta nhập vào công thức là: =VLOOKUP(D4,$B$17:$C$20,2,1)
Trong đó:
- D4: Là giá trị cần đối chiếu.
- $B$17:$C$20: Là bảng dò tìm.
- 2: Số thứ tự cột dữ liệu trên bảng dò tìm.
- 1: Kiểu tìm kiếm tương đối.
Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những học sinh còn lại. Quan sát hình để hiểu rõ hơn về công thức và cách dò tìm tương đối.
Hàm vlookup
Hình minh hoạ sử dụng hàm Vlookup để tìm kiếm tương đối.
Dễ thấy, khi không do tìm được kết quả nào trùng khớp với giá trị dò tìm thì hàm Vlookup sẽ lấy giá trị lớn nhất kế tiếp nhỏ hơn nó.
4. Những điều cần biết khi sử dùng hàm Vlookup trong Excel.
4.1. Một số lưu ý khi sử dụng hàm Vlookup trong Excel.
- Khi tạo công thức hàm bạn cần cố định Table_array (vùng cần tìm kiếm) bằng cách thêm kí tự $ vào địa chỉ của vùng ( VD: $A$18:$B$21) để khi ta copy công thức xuống các hàng khác thì vùng cần tìm kiếm không bị thay đổi theo.
- Hàm Vlookup trong Excel chỉ có thể tìm ở cột ngoài cùng bên trái của vùng ta chọn. Nếu bạn cần tìm ở những vị trí khác, hãy sử dụng công thức Index kết hợp Match.
- Hàm Vlookup trong Excel không phân biệt được chữ hoa và thường.
- Nếu range_lookup được đặt là TRUE hoặc bỏ trống (kết quả khớp tương đối), các giá trị ở hàng đầu của table_array phải được xếp theo thứ tự tăng dần (A-Z) từ trái sang phải.
- Khi dùng hàm Vlookup để tìm kiếm tuyệt đối nhưng vùng cần tìm kiếm lại không chứa giá trị đó thì kết quả trả về sẽ là #N/A!.
4.2. Hướng dẫn sử dụng dấu chấm dấu phẩy.
Bạn có đang gặp vấn đề trong việc dùng dấu chấm “.” dấu dấu chấm phẩy “;” trong việc ngăn cách cách thành phần trong hàm, hay dấu chấm “.” dấu phẩy “,” trong việc phân cách chữ số thập phân?
Trong Excel người ta thường dấu phẩy “,” trong việc ngăn cách thành phần trong hàm và phân cách các hàng nghìn, triệu, tỷ trong số. Dấu chấm “.” để phân cách chữ số thập nhân. Nhưng trong ở một số bản phần mềm Excel khác lại dùng dấu phẩy “,” để phân cách hàng thập phân và dấu chấm “.” để phân cách các nhóm 3 chữ số (các hàng nghìn, triệu, tỷ), còn dấu “;” để phân cách các thành phần trong hàm. Điều này khiến người dùng dễ nhầm lẫn khi dùng dấu chấm và dấu phẩy trong Excel.
Trong Excel có 2 kiểu quy ước:
- Quy ước kiểu phương Tây: Dấu chấm “.” là dấu để phân cách chữ số thập phân, còn phân cách các hàng nghìn, triệu, tỷ và các thành phần khi viết hàm là dấy phẩy “,”.
- Quy ước của Việt Nam: Dấu phẩy “,” để phân cách hàng thập phân và dấu chấm “.” để phân cách các nhóm 3 chữ số (các hàng nghìn, triệu, tỷ), còn dấu “;” để phân cách các thành phần trong hàm.
Vậy làm thế nào để biết Excel của mình dùng tuần theo quy ước nào, bạn có thể thử cách sau:
- Nhập thử một số thập phân tại ô nào đó, nếu Excel không dịch số đó về bên phải thì dấu ngăn cách thập phân là dấu “.” (chấm).
- Nếu dấu ngăn cách thập phân là dấu “.” thì dấu ngăn cách phần ngàn là dấu “,” (ví dụ: 1,234.55) và dấu ngăn cách đối số trong công thức là dấu “,” (ví dụ: if(a1>b1,0,1)).
- Nếu dấu ngăn cách thập phân là dấu “,” thì dấu ngăn cách phần ngàn là dấu “.” và dấu ngăn cách trong công thức là dấu “;” (chấm phẩy).
Khi Excel của bạn tuần theo quy ước của Việt Nam bạn cần đề ý hơn khi nhập dữ liệu, lập công thức, tra cứu công thức để phù hợp.
Nhưng để tiện sử dụng và tra cứu bạn nên đổi sang quy ước của phương Tây bằng cách:
File => Opstion => Advanced => tìm và bỏ tick Use system separators => Decimal separator để là “.”, Thousands separator để là “,” => click OK để hoàn tất chỉnh sửa.
Sử dụng dấu chấm, dấu phẩy
4.3. Hướng dẫn sử dụng địa chỉ tuyệt đối khi dùng hàm Vlookup trong Excel.
Trong excel có 3 loại địa chỉ:
- Địa chỉ tương đối: Là địa chỉ bị thay đổi tương ứng với mỗi dòng và cột khi chúng ta thực hiện sao chép công thức. (VD: B5 là địa chỉ của hàng 5 cột B).
- Địa chỉ tuyệt đối: Là địa chỉ được cố định lại, không thay đổi khi ta copy công thức. (VD: $A$1- địa chỉ tuyệt đối của 1 ô, $B$17:$C$20 – địa chỉ tuyệt đối của 1 vùng)
- Để tạo địa chỉ tuyệt đối, thì bạn nhấn phím F4, lúc này sẽ có dấu đô la ($) ở trước chỉ số cột và dòng.
- Tóm lại nếu là địa chỉ tuyệt đối thì bạn thấy có dấu đô la ($) trước chỉ số cột và dòng.
- Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là địa chỉ chỉ cố định dòng hoặc cột mà thôi.
- Cố định cột: Ví dụ: $A1, thì bạn thấy chỉ số cột được cố định, còn chỉ số dòng không được cố định.
- Cố định dòng: Ví dụ: A$1 thì bạn thấy chỉ số cột không được cố định, còn chỉ số dòng cố định.
Khi sử dùng hàm Vlookup trong Excel bạn thường phải tìm kiếm cho cả cột nên việc copy công thức là không tránh khỏi. Lúc này bạn cần lưu ý để địa chỉ của vùng tìm kiếm là địa chỉ tuyệt đối để khi ta copy công thức cho những hàng khác thì vùng tìm kiếm của ta không bị thay đổi.
4.4. Đặt tên cho bảng trong Excel.
Địa chỉ các ô, các bảng, các vùng trong Excel được lưu bằng những kí tự và số rất khó nhớ, điều này gây khó khăn và tồn thời gian cho chúng ta khi phải gọi ra các bảng, các vùng dữ liệu từ nhiều nơi. Để đơn giản hoá việc này chúng ra có thể đặt tên cho các vùng dữ liệu. Khi cần sử dụng đến chúng ta chỉ cần gọi tới vùng đó thông qua tên đã đặt.
Đặt tên cho vùng dữ liệu dữ liệu bằng cách:
B1: Chọn vùng muốn đặt tên, vùng này có thể là 1 ô hoặc 1 vùng tuỳ theo mục đích sử dụng của bạn.
B2: Tại ô địa chỉ của vùng dữ liệu (góc bên trên phía bên tay trái, ngay trên cột A) ta nhập tên cho vùng dữ liệu và Enter.
Đặt tên cho vùng dữ liệu
Ảnh minh hoạ: Đặt tên cho vùng dữ liệu trong Excel.
Sau khi đặt tên cho vùng dữ liệu ta chỉ cần gọi vùng đó ra thông qua tên mà không lo địa chỉ bị sai khi copy công thức từ dòng này sang dòng khác.
Khi sử dụng hàm Vlookup ta có thể đặt tên cho cùng cần tìm kiếm mà không cần quan tâm tới địa chỉ tuyệt đối của nó.
VD: Ở ví dụ bên trên sau khi đã đặt tên cho bảng quy định xếp loại.
Từ công thức: =VLOOKUP(D4,$B$17:$C$20,2,1).
=> Ta có thể sử dụng công thức: =VLOOKUP(D13,quy_dinh,2,1).
Cả 2 công thức này đều tương đương nhau nhưng với công thức mà vùng tìm kiếm được đặt tên giúp ta dễ dàng copy cho những hàng khác và đơn giản, dễ hình dung.
Tổng kết.
Vlookup là một hàm cơ bản trong Excel, hỗ trợ rất mạnh trong việc thống kê và dò tìm dữ liệu nhưng không phải ai cũng có thể nắm vững cách sử dụng hàm này. Trên đây là hướng dẫn chi tiết cách sử dụng hàm Vlookup cơ bản nhất và một số ví dụ cụ thể hi vọng giúp ích cho các bạn. Chúc các bạn thành công!
Video hướng dẫn:
Gợi ý học tập mở rộng.
Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản
Các bài viết liên quan:
Hàm VLOOKUP nâng cao trong Excel Hàm VLOOKUP 2 điều kiện trong Excel Hàm VLOOKUP kết hợp hàm IF trong Excel Hàm VLOOKUP ngược và cách dùng hàm VLOOKUP ngược trong Excel
