Cách dùng hàm Vlookup trong Excel dò dữ liệu kèm ví dụ thực tế

Cùng AntBook tìm hiểu cách sử dụng hàm VLOOKUP trong Excel để dò tìm và tra cứu dữ liệu hiệu quả, các lỗi thường gặp và mẹo sử dụng để nâng cao hiệu suất công việc.

1. Hàm Vlookup trong Excel là gì? Dùng để làm gì?

1.1 Khái niệm hàm Vlookup Excel là gì?

Hàm VLOOKUP là một công cụ cực kỳ hữu ích trong Excel, giúp người dùng tra cứu dữ liệu một cách nhanh chóng và hiệu quả.

Tên gọi “VLOOKUP” xuất phát từ hai từ trong tiếng Anh: “V” là viết tắt của “Vertical” (hàng dọc) và “LOOKUP” nghĩa là tìm kiếm.

Hàm Vlookup Excel giúp người dùng tra cứu dữ liệu một cách nhanh chóng

Hàm này được sử dụng khi bạn cần dò tìm một giá trị cụ thể trong cột đầu tiên của bảng dữ liệu, sau đó trả về một giá trị tương ứng từ cột khác trong cùng hàng. Vậy ứng dụng thực tế trong đời sống có thể sử dụng hàm Vlookup này ra sao?

1.2 Hàm Vlookup trong Excel dùng để làm gì?

Hàm VLOOKUP phục vụ nhiều mục đích khác nhau trong công việc, nhưng thường được dùng để tra cứu giá trị dựa vào một mã định danh có sẵn.

Trong thực tế, hàm này rất phổ biến trong việc tìm kiếm tên sản phẩm, đơn giá, số lượng dựa trên mã sản phẩm hoặc mã vạch. Ngoài ra, bạn cũng có thể sử dụng hàm VLOOKUP để tìm thông tin nhân viên, xếp loại nhân viên theo các tiêu chí đã thiết lập.

Hàm Vlookup rất phổ biến trong việc tìm kiếm tên sản phẩm, đơn giá

Nếu bạn cần dò tìm dữ liệu theo hàng ngang thay vì hàng dọc, hãy sử dụng hàm HLOOKUP. Hàm này hoạt động tương tự như VLOOKUP nhưng được thiết kế để tra cứu dữ liệu theo hàng ngang và trả về giá trị từ các hàng bên dưới.

2. Công thức sử dụng hàm Vlookup trong Excel chuẩn

Hàm VLOOKUP trong Excel được viết với cú pháp sau:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Trong đó:

• lookup_value: Giá trị cần dò tìm, có thể là một số, văn bản hoặc tham chiếu đến một ô chứa giá trị trong bảng tính.
• table_array: Bảng hoặc phạm vi dữ liệu chứa giá trị cần tìm, cần cố định bằng dấu $ nếu dự kiến sao chép công thức sang ô khác (ví dụ: $H$6:$J$13).
• col_index_num: Số thứ tự của cột chứa giá trị cần trả về, được tính từ cột đầu tiên trong table_array.
• [range_lookup]: Tham số tùy chọn để xác định kiểu dò tìm:
  • TRUE hoặc 1: Dò tìm tương đối (khi dữ liệu đã được sắp xếp theo thứ tự tăng dần hoặc giảm dần).
  • FALSE hoặc 0: Dò tìm tuyệt đối (chỉ trả về giá trị khớp hoàn toàn).

Công thức của hàm Vlookup

Lưu ý khi sử dụng:

• Dò tìm tương đối (TRUE) chỉ nên áp dụng khi các giá trị trong cột đầu tiên của table_array đã được sắp xếp. Nếu không, kết quả có thể không chính xác.
• Dò tìm tuyệt đối (FALSE) sẽ trả về giá trị chính xác và được ưu tiên khi bảng dữ liệu chưa được sắp xếp hoặc không thể sắp xếp.

Mẹo khi cố định vùng dữ liệu:

Khi sao chép công thức sang nhiều ô, cần cố định table_array bằng dấu $ (như $H$6:$J$13) hoặc nhấn phím F4 sau khi chọn phạm vi dữ liệu để tự động thêm dấu $. Việc này giúp giới hạn bảng dò tìm, tránh lỗi khi kéo công thức.

3. Ví dụ thực tế về cách dùng hàm Vlookup trong Excel

3.1 Xếp loại học sinh với cách dùng hàm Vlookup Excel

Giả sử bạn có bảng kết quả thi của học sinh (Bảng 1), và muốn xếp loại học sinh dựa trên điểm số bằng cách sử dụng một bảng tham chiếu (Bảng 2) từ ô B11 đến C15. Trong bảng tham chiếu này, cột B chứa điểm số và cột C chứa xếp loại tương ứng.

Ví dụ về xếp loại học sinh với hàm Vlookup

Ở ô E4, bạn có thể sử dụng công thức sau để xác định xếp loại của học sinh:
=VLOOKUP(D4, $B$11:$C$15, 2, 1)

Giải thích công thức này:

• D4: Là ô chứa điểm số của học sinh cần xếp loại.
• $B$11:$C$15: Là bảng tham chiếu chứa các điểm số và xếp loại, với dấu $ để cố định vùng dữ liệu. Dấu $ giúp khi sao chép công thức, Excel vẫn giữ nguyên bảng tham chiếu mà không bị thay đổi.
• 2: Là số thứ tự của cột trong bảng tham chiếu mà bạn muốn lấy kết quả trả về (ở đây là cột xếp loại, cột thứ 2).
• 1 (TRUE): Chỉ ra rằng Excel sẽ tìm giá trị điểm số gần nhất trong bảng tham chiếu, thay vì yêu cầu khớp chính xác.

Công thức xếp loại học sinh giỏi dựa vào hàm Vlookup

Trong ví dụ này, nếu điểm số ở ô D4 là 8.5, Excel sẽ dò tìm trong bảng tham chiếu và nhận thấy điểm gần nhất là 8.5, sau đó trả về kết quả “Giỏi” từ cột thứ 2 của bảng tham chiếu.

Sau khi nhập công thức vào ô E4, bạn có thể dễ dàng sao chép công thức này xuống các ô bên dưới hoặc sử dụng tính năng Flash Fill để tự động áp dụng công thức cho các ô khác trong cột E. Nhờ vậy, bạn có thể xếp loại học sinh nhanh chóng và hiệu quả theo điểm số.

Bảng xếp loại học sinh giỏi dựa vào hàm Vlookup

3.2 Hàm VLOOKUP để tìm kiếm gần đúng trong Excel

Giả sử bạn muốn xếp loại học sinh dựa trên điểm trung bình của họ, và bạn cần sử dụng hàm VLOOKUP với chế độ tìm kiếm gần đúng (Range_lookup = 1). Trong trường hợp này, hàm sẽ tìm giá trị gần đúng nhất với điểm trung bình của học sinh.

Ví dụ, bạn có các tiêu chí xếp loại như sau:

• Từ 9 trở lên: Xếp loại Giỏi (9 <= x)
• Từ 6.5 đến dưới 9: Xếp loại Khá (6.5 <= x < 9)
• Từ 5 đến dưới 6.5: Xếp loại Trung bình (5 <= x < 6.5)

Ở ô D4, bạn sẽ sử dụng công thức sau để xếp loại học sinh dựa trên điểm trung bình có trong ô C4:
=VLOOKUP(C4, $B$10:$C$12, 2, TRUE)

Giải thích công thức:

• C4: Là ô chứa điểm trung bình của học sinh cần xếp loại.
• $B$10:$C$12: Là bảng tham chiếu chứa các điểm số và xếp loại tương ứng. Dấu $ giúp cố định bảng này khi sao chép công thức sang ô khác.
• 2: Là số thứ tự của cột trong bảng tham chiếu từ đó bạn muốn trả về kết quả (ở đây là cột xếp loại, tức cột thứ 2).
• TRUE: Cho phép hàm tìm giá trị gần đúng, nghĩa là nếu điểm trung bình không có trong bảng, hàm sẽ lấy giá trị gần nhất nhỏ hơn hoặc bằng giá trị cần tìm.

Ví dụ về tìm kiếm giá trị gần đúng trong Excel

Lưu ý quan trọng: Để hàm VLOOKUP hoạt động chính xác, bảng tham chiếu phải được sắp xếp theo thứ tự từ nhỏ đến lớn (điểm số từ thấp đến cao).

3.3 Tính phụ cấp theo chức vụ nhân viên

Trong bối cảnh dịch Covid-19, công ty quyết định cấp phụ cấp cho nhân viên dựa trên chức vụ của họ, như được thể hiện trong bảng 2 (B16:C21). Để xác định mức phụ cấp của mỗi nhân viên, ta sẽ sử dụng hàm VLOOKUP để dò tìm chức vụ của từng người từ bảng 1 và sau đó tra cứu mức phụ cấp tương ứng trong bảng 2.

Ví dụ về cách tính phụ cấp cho nhân viên với hàm Vlookup

Cách thực hiện như sau: Bạn sẽ dò tìm chức vụ của nhân viên trong bảng 1. Sau đó, tìm giá trị chức vụ đó trong cột 1 của bảng 2 (từ trên xuống dưới). Khi tìm thấy chức vụ, bạn sẽ lấy mức phụ cấp tương ứng từ cột 2 của bảng 2 và điền vào bảng 1.

Với một danh sách nhân viên lớn, từ vài trăm đến một ngàn người, việc làm thủ công sẽ rất tốn thời gian và công sức. Đó chính là lúc hàm VLOOKUP phát huy tác dụng, giúp bạn tự động hóa quy trình tính phụ cấp này.

Công thức cụ thể mà bạn sẽ nhập tại ô E4 là:
=VLOOKUP(D4, $B$16:$C$21, 2, 0)

Giải thích công thức:

• D4: Là ô chứa chức vụ của nhân viên mà bạn cần dò tìm.
• $B$16:$C$21: Là bảng chứa thông tin chức vụ và mức phụ cấp. Dấu $ giúp cố định các ô trong bảng khi bạn sao chép công thức.
• 2: Là số thứ tự của cột dữ liệu mà bạn muốn lấy kết quả từ bảng 2 (ở đây là cột phụ cấp, tức cột thứ 2).
• 0 (FALSE): Tham số này cho phép hàm dò tìm chính xác giá trị chức vụ, thay vì tìm gần đúng.

Công thức tính phụ cấp cho nhân viên với hàm Vlookup

Sau khi nhập công thức vào ô E4, bạn chỉ cần sao chép công thức cho các ô còn lại trong cột hoặc sử dụng tính năng Flash Fill để hoàn thành việc tính phụ cấp cho toàn bộ danh sách nhân viên một cách nhanh chóng và chính xác.

Kết quả tính phụ cấp cho nhân viên với hàm Vlookup

4. Lưu ý về các lỗi thường gặp trong hàm Vlookup Excel

4.1 Hàm Vlookup trong excel lỗi #N/A

Một trong những hạn chế của hàm VLOOKUP là nó chỉ có thể tìm kiếm giá trị trong cột ngoài cùng bên trái của bảng (Table_array). Nếu bạn cố gắng tìm giá trị ở cột khác ngoài cột đầu tiên, hàm sẽ trả về lỗi #N/A. Để khắc phục, bạn có thể sử dụng kết hợp hàm INDEX và MATCH.

Ngoài ra, nếu hàm VLOOKUP không tìm thấy giá trị chính xác trong bảng dữ liệu, nó cũng sẽ trả về lỗi #N/A. Khi gặp lỗi này, bạn có thể sử dụng hàm IFERROR để thay thế lỗi #N/A bằng một giá trị khác, như thông báo lỗi hoặc một giá trị mặc định.

Nếu bạn chắc chắn rằng giá trị cần tìm có trong bảng nhưng hàm VLOOKUP không thể tìm thấy, bạn cần kiểm tra xem các ô dữ liệu tham chiếu có bị khoảng trắng ẩn hoặc ký tự không in nào không. Ngoài ra, hãy chắc chắn rằng tất cả các ô dữ liệu đều có định dạng đúng.

Hàm VLOOKUP không tìm thấy giá trị chính xác trong bảng dữ liệu

4.2 Hàm Vlookup trong excel lỗi #REF!

Lỗi #REF! xảy ra khi bạn nhập giá trị Col_index_num (số cột) lớn hơn số cột có sẵn trong Table_array. Để khắc phục, bạn cần kiểm tra lại công thức và chắc chắn rằng Col_index_num không vượt quá số cột trong bảng dữ liệu.

4.3 Hàm Vlookup trong excel lỗi #VALUE!

Lỗi #VALUE! xuất hiện khi Col_index_num có giá trị nhỏ hơn 1 trong công thức. Trong hàm VLOOKUP, cột đầu tiên trong Table_array là cột tìm kiếm (cột 1), cột tiếp theo là cột 2, v.v… Vì vậy, nếu bạn gặp lỗi #VALUE!, hãy kiểm tra lại giá trị của Col_index_num để đảm bảo rằng nó là một số hợp lệ.

4.4 Hàm Vlookup trong excel lỗi #NAME?

Lỗi #NAME? thường xảy ra khi bạn tìm kiếm giá trị dạng văn bản mà không bao quanh giá trị đó bằng dấu ngoặc kép (“”). Để tìm giá trị là văn bản, bạn cần đặt giá trị trong dấu ngoặc kép, ví dụ: “Hà Nội” để Excel nhận diện đúng công thức.

Lỗi #NAME khi thiếu dấu ngoặc kép

5. Một số lưu ý khi sử dụng hàm Vlookup trong Excel

Khi sao chép công thức, bạn nên sử dụng tham chiếu tuyệt đối cho Table_array hoặc Lookup_value. Điều này có thể thực hiện bằng cách thêm dấu đô la ($) trước cột và hàng (ví dụ: $A$1). Nếu không, khi sao chép công thức, các giá trị này sẽ thay đổi, dẫn đến kết quả không chính xác.

Sử dụng tham chiếu tuyệt đối cho bảng hoặc giá trị tìm kiếm

Nếu dữ liệu trong Table_array là số nhưng được lưu dưới dạng văn bản và giá trị bạn cần tìm kiếm (Lookup_value) là số, hàm VLOOKUP sẽ không tìm được kết quả và trả về lỗi #N/A.

Nếu bảng dò tìm có nhiều giá trị giống nhau, hàm VLOOKUP sẽ chỉ trả về kết quả của giá trị đầu tiên mà nó tìm thấy, bắt đầu từ trên xuống dưới. Điều này có thể làm sai lệch kết quả nếu bạn có các giá trị trùng lặp trong bảng.

6. Một số hạn chế của hàm Vlookup trong Excel

Hàm VLOOKUP chỉ có thể tìm kiếm giá trị từ trái sang phải trong dãy dữ liệu. Điều này có nghĩa là bạn không thể sử dụng nó để tham chiếu giá trị từ phải sang trái. Nếu cần làm vậy, bạn phải kết hợp VLOOKUP với các hàm phức tạp khác như MATCH và INDEX, điều này có thể gây khó khăn và khiến công thức trở nên rắc rối.
Hàm VLOOKUP chỉ hoạt động tốt khi các giá trị trong bảng là duy nhất, bởi vì nó chỉ tìm kiếm giá trị tham chiếu đầu tiên và bỏ qua các dòng có giá trị giống nhau. Điều này có thể dẫn đến kết quả không chính xác khi có nhiều giá trị tương tự trong bảng.
Khi sử dụng hàm VLOOKUP, bạn phải nhập thủ công số thứ tự của cột tham chiếu trong công thức. Điều này có thể gây khó khăn khi sao chép công thức sang các ô khác. Để giải quyết vấn đề này, bạn có thể sử dụng hàm MATCH để thay đổi số thứ tự cột một cách linh hoạt.

Sử dụng kết hợp hàm MATCH để sao chép công thức sang ô khác

Nếu không có thiết lập cụ thể cho tham chiếu trong công thức, hàm VLOOKUP sẽ mặc định tìm kiếm theo phương thức “approximate match” (dò tìm gần đúng). Điều này có thể dẫn đến kết quả không chính xác hoặc sai lệch nếu dữ liệu không được sắp xếp đúng cách hoặc không có giá trị khớp hoàn toàn.
Khi sử dụng nhiều hàm VLOOKUP trong một bảng tính, hiệu suất của Excel có thể bị giảm, dẫn đến tốc độ phản hồi chậm và đôi khi thậm chí gây ra hiện tượng Excel bị crash. Điều này có thể gây khó chịu và làm giảm trải nghiệm sử dụng.

Hàm VLOOKUP trong Excel là công cụ mạnh mẽ giúp bạn tra cứu dữ liệu nhanh chóng và chính xác. Tuy nhiên, để tận dụng tối đa hàm này, bạn cần lưu ý các lưu ý và hạn chế khi sử dụng, như việc sử dụng tham chiếu tuyệt đối hay tránh dữ liệu bị trùng lặp. Với những mẹo và lưu ý trên, bạn sẽ dễ dàng áp dụng hàm VLOOKUP trong các tình huống thực tế và nâng cao hiệu quả công việc của mình.

Trên thực tế, kiến thức Excel còn vô vàn mẹo hay mà có thể bạn chưa biết. Tìm hiểu ngay cuốn sách Excel từ AntBook. Chắc chắn đây sẽ là người bạn đồng hành giúp bạn nâng cao hiệu suất một cách rõ rệt ấy. Liên hệ với AntBook để tự tin chinh phục tin học văn phòng nhé!