Hàm Vlookup là gì?
Trong Excel hàm Vlookup 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 là gì?
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 Vlookupcủ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. Sử dụng hàm Vlookup để tìm kiếm chính xác là gì?
Ví dụ: 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, quê quán. Một bảng khác có sẵn mã nhân viên và cột quê quán bị bỏ chống. Giờ bạn muốn điền thông tin quê quán cho từng nhân viên thì phải làm như thế nào?
Hàm Vlookup là gì
Hình 1: Hàm Vlookup là gì?
Để điển thông tin quê quán cho nhân viên, tại ô G4, ta nhập công thức dò tìm chính xác như sau: =VLOOKUP(F4,$B$4:$D$10,3,0)
Trong đó:
- F4: Là giá trị cần đối chiếu.
- $B$4:$D$10: Là bảng dò tìm, địa chỉ của bảng dò tìm phải là địa chỉ tuyệt đối.
- 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.
Sau khi điền xong công thức cho ô G4, 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.
Hàm Vlookup là gì
Hình 2: Hàm Vlookup là gì?
3.3. Hàm Vlookup để tìm kiếm tương đối là gì.
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.
Ví dụ: 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 là gì
Hình 3: Hàm Vlookup là gì?
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$14:$C$17,2,1)
Trong đó:
- D4: Là giá trị cần đối chiếu.
- $B$14:$C$17: 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 là gì
Hình 4: Hàm Vlookup là gì?
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 dò tìm tương đối 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 là gì?
4.1. Hàm Vlookup tìm kiếm từ phải qua trái.
Hàm Vlookup luôn tra cứu giá trị ở cột ngoài cùng bên trái của bảng và trả về giá trị tương ứng từ cột bên phải.
Hàm Vlookup là gì
Hình 5: Hàm Vlookup là gì?
Lưu ý: Trong ví dụ này, hàm VLOOKUP không thể tra cứu Trình độ và trả về Mã NV. Hàm VLOOKUP chỉ nhìn sang bên phải. Nếu muốn dò tìm ngược lại, đừng lo lắng bạn có thể sử dụng LOOKUP trong Excel để thực hiện tra cứu ngược.
Bài viết tham khảo: Hàm Vlookup ngược, cách dùng Vlookup ngược.
4.2. Sử dụng địa chỉ tuyệt đối khi dùng hàm Vlookup.
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.3. Hàm Vlookup trả về giá trị đầu tiền được tìm thấy.
Nếu cột ngoài cùng bên trái của bảng chứa các giá trị trùng lặp nhau thì sẽ lấy giá trị đầu tiên được tìm thấy. Ví dụ, hãy xem hình minh họa bên dưới.
Hàm Vlookup là gì
Hình 6: Hàm Vlookup là gì?
Giải thích: Hàm VLOOKUP trả về quê của Nguyễn Huy Tưởng, không phả trả về quê của Nguyễn Huy Trạch.
4.4. Hàm vlookup không phân biệt chữ hoa chữ thường.
Thực hiện tra cứu không phân biệt chữ hoa chữ thường. Ví dụ, ở bên dưới tra cứu NGUYỄN HUY (ô G4) ở cột ngoài cùng bên trái của bảng.
Hàm Vlookup là gì
Hình 7: Hàm Vlookup là gì?
Giải thích: Hàm VLOOKUP không phân biệt chữ hoa chữ thường nên nó sẽ tra cứu NGUYỄN HUY hoặc Nguyễn Huy hoặc nguyễn huy, v.v. Kết quả là hàm VLOOKUP trả về tiền quê của Nguyễn Huy Tưởng (trường hợp đầu tiên).
4.5. Hàm vlookup bị lỗi #N/A khi tìm kiếm.
Khi dò tìm nếu hàm VLOOKUP không thể tìm thấy kết quả phù hợp, nó sẽ trả về lỗi # N / A.
- Nếu bạn tìm kiếm chính xác, thi giá trị bạn tìm phải khớp với trong bảng dò tìm.
- Nếu bạn tìm kiếm tương đối thì giá trị bạn tìm phải lớn hợp giá trị nhỏ nhất trong bảng dò tìm.
Lỗi này rất thường gặp nếu bạn không nắm chắc về cách sử dụng hàm.
Để tìm hiều nguyên nhân và cách khắc phục lỗi này bạn tham khảo bài viết: Hàm Vlookup bị lỗi #N/A
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 2 điều kiện trong Excel Hàm VLOOKUP nhiều điều kiện trong Excel Hàm VLOOKUP nâng cao trong Excel Hàm VLOOKUP kết hợp hàm IF trong Excel Hàm VLOOKUP kết hợp hàm SUM và SUMIF Hàm VLOOKUP ngược và cách dùng hàm VLOOKUP ngược trong Excel Hàm VLOOKUP bị lỗi #N/A, nguyên nhân, cách khắc phục. Hàm VLOOKUP trong Google Sheet
