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 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.
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 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 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 thì 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 sẽ trả về lỗi #N/A.
3. Ví dụ về hàm VLOOKUP trong Excel.
3.1. Sử dụng 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ình 1: Sử dụng 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$13:$D$19,2,0)
Trong đó:
- B4: Là giá trị cần đối chiếu.
- $B$13:$D$19: 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.
Hình 2: 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$13:$D$19,3,0)
Trong đó:
- B4: Là giá trị cần đối chiếu.
- $B$13:$D$19: 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ị khi tìm kiếm chính xác.
Hình 3: Tìm kiếm chính xác trong Excel.
3.2. 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ình 4: Sử dụng hàm Vlookup trong Excel để tìm kiếm tương đối.
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ình 5: Sử dụng hàm Vlookup để tìm kiếm tương đối.
Ở ví dụ trên, Trong bảng quy định xếp loại không có giá trị nào trùng khớp với 7.5 nên sẽ tìm giá trị lớn nhất kế tiếp nhỏ hơn 7.5 là 6.5 => Giá trị trả về tương ứng với 6.5 là Khá.
=> Khi không dò 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ó.
3.3. Hàm Vlookup tìm kiếm nhiều điều kiện.
Cách đơn giản nhất để tìm kiếm trong Excel với 2 điều kiện là sử dụng cột phụ. Từ 2 hay nhiều điều kiện, mình tổng hợp lại vào 1 cột mới và dùng hàm Vlookup để tìm kiếm với điều kiện dò tìm là cột ta vừa tạo.
VD: Bạn có một danh sách sản lượng sản xuất cho từng sản phẩm, từng ca. Làm thế nào để biết được sản lượng của 1 sản phẩm nào đó trong từng ca là bao nhiêu?
Các bước thực hiện:
B1: Tạo cột phụ bằng cách ghép 2 điều kiện với toán tử “&”.
Hình 6: Tìm kiếm 2 điều kiện sử dụng cột phụ.
B2: Viết hàm với điều kiện tìm kiếm là cột phụ vừa tạo.
Để tìm kiến sản lượng của 1 sản phẩm theo ca tại ô H8 ta nhập công thức: =VLOOKUP(H6&H7,$B$5:$E$9,4,0)
Trong đó:
- H6&H7: Là giá trị cần đối chiếu.
- $B$5:$E$9: Là bảng dò tìm bao gồm cả cột phụ
- 4: 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 dưới để hiểu hơn về các thành phần của công thức chúng ta vừa tạo.
Hình 7: Hàm Vlookup sử dụng cột phụ.
Nếu muốn tìm sản lượng của sản phẩm khác hoặc ca khác bạn chỉ cần nhập sản phẩm và ca cần tìm vào bảng tìm kiếm như trên hình mà không cần tạo lại công thức.
Trên đây là 1 cách tìm kiếm nhiều điều kiện trong Excel, để xem thêm cách sử dùng hàm Vlookup để tìm kiếm với nhiều điều kiện hơn, bạn tham khảo bài viết này: Hàm Vlookup nhiều điều kiện.
Nếu bạn thành thạo Excel vào muốn 1 cách tìm kiếm nâng cao, chuyên nghiệp hơn, tham khảo bài viết này: Hàm Vlookup 2 điều kiện dùng công thức mảng.
3.4. Hàm Vlookup kết hợp hàm IF tìm kiếm trên nhiều bảng.
Trong Excel hàm VLOOKUP là một hàm dùng để tra cứu kết quả trả về theo hàng dọc. Hàm IF là một hàm dùng để yêu cầu Excel kiểm tra một điều kiện và trả về một giá trị nếu điều kiện được đáp ứng, hoặc trả về một giá trị khác nếu điều kiện đó không được đáp ứng.Vậy nếu kết hợp 2 hàm này với nhau thì sẽ như thế nào? Và cách kết hợp ra sao?
Hãy cùng Học Excel Cơ Bản tìm hiểu cách tham chiếu ngược từ phải qua trái trong excel qua bài viết này nhé: Hàm VLOOKUP kết hợp hàm IF trong Excel đơn giản, hiệu quả.
3.5. Hàm Vlookup kết hợp hàm SUM hoặc hàm SUMIF.
Nếu bạn thao tác dữ liệu số trên Excel, thì thông thường bạn không phải chỉ tìm kiếm và xuất các dữ liệu liên quan từ một bảng khác mà còn phải kết hợp tìm kiếm và tính tổng các con số từ một vài cột hay hàng. Để thực hiện thao tác này, bạn có thể kết hợp hàm VLOOKUP với hàm SUM hoặc nâng cao hơn bạn có thể kết hợp hàm VLOOKUP với hàm SUMIF.
Nhưng để kết hợp được những hàm này với nhau không phải là điều dễ dàng, hãy cùng Học Excel Cơ Bản tìm hiểu cách kết hợp nhưng hàm này qua bài viết này nhé: Hàm VLOOKUP kết hợp hàm SUM hoặc hàm SUMIF.
3.6. Hàm Vlookup ngược để tìm kiếm từ phải qua trái.
Như các bạn đã biết hàm Vlookup là 1 hàm dò tìm rất hiệu quả trong Excel, nhưng hàm này có nhược điểm là hướng tìm kiếm từ trái qua phải. Vậy trong trường hợp phải tham chiếu ngược lại, tức là từ phải qua trái thì chúng ta làm thế nào?
Hãy cùng Học Excel Cơ Bản tìm hiểu cách tham chiếu ngược từ phải qua trái trong excel qua bài viết này nhé: Hàm Vlookup ngược, cách dùng VLOOKUP ngược.
3.7. Hàm Vlookup và hàm Hlookup tìm kiếm trong Excel.
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. Nhưng không phải lúc nào ta cũng cần tìm kiếm theo hàng dọc với một vài trường hợp cụ thể ta cần phải tìm kiếm giá trị theo dòng và trả về phương thức hàng ngàng. Trong Excel ngoài hàm Vlookup còn có hàm Hlookup để hỗ trợ chúng ta tìm kiếm theo hàng ngang.
Hãy cùng Học Excel Cơ Bản tìm hiểu cách tìm kiếm theo hàng ngang trong excel qua bài viết này nhé: Hàm Vlookup và hàm Hlookup tìm kiếm trong Excel.
4. Một số lưu ý khi sử dụng hàm Vlookup.
4.1. Hàm vlookup tìm kiếm từ phải qua trái.
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ình 8: Hàm Vlookup tìm kiếm từ phải qua trái.
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ình 9: Hàm Vlookup trả về giá trị đầu tiên tìm được tìm thấy
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ình 10: Vlookup không phân biệt chữ hoa và chữ thường.
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.
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
4.6. Hướng dẫn đặt tên cho bảng.
Đị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.
Hình 11: Đặ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$14:$C$17,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 nhiều điều kiện 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.