Hướng Dẫn Sử Dụng Hàm VLOOKUP Trong Excel Một Cách Chi Tiết, Rất Dễ Hiểu

Hàm VLOOKUP trong Excel là một công cụ tuyệt vời giúp bạn tìm kiếm và lấy dữ liệu tương ứng. Nếu bạn chưa rõ cách sử dụng hàm này, hãy xem ngay hướng dẫn cách sử dụng hàm VLOOKUP chi tiết với ví dụ cụ thể trong bài viết dưới đây!

I. Ý nghĩa của hàm VLOOKUP

Hàm VLOOKUP là một công cụ hữu ích trong Excel, giúp người dùng nhanh chóng tìm kiếm và lấy dữ liệu từ một bảng lớn. Với VLOOKUP, bạn có thể dễ dàng xác định một giá trị trong một cột và nhận về giá trị tương ứng từ cột khác trong bảng.

Hàm VLOOKUP đóng vai trò quan trọng trong việc phân tích dữ liệu và thực hiện các tính toán trong Excel. Thay vì phải lục tìm qua toàn bộ bảng dữ liệu để tìm một giá trị cụ thể, VLOOKUP cho phép bạn làm điều này chỉ với một công thức đơn giản. Điều này giúp tiết kiệm thời gian và nâng cao hiệu quả công việc.

II. Công thức của hàm VLOOKUP trong Excel

Dưới đây là công thức của hàm VLOOKUP:

***Xin lưu ý:

• Để có được kết quả chính xác nhất, bạn nên sử dụng ký hiệu $ để cố định Table_array bằng cách nhấn F4 hoặc Fn+F4 sau khi chọn vùng dữ liệu. (VD: $H$6:$J$13).
• Tùy thuộc vào từng máy tính, bạn sẽ sử dụng dấu phẩy (,) hoặc dấu chấm phẩy (;) cho hàm VLOOKUP.

III. Hướng dẫn sử dụng hàm VLOOKUP trong Excel

Để giúp bạn hiểu rõ hơn về cách sử dụng hàm VLOOKUP, hãy tham khảo một số ví dụ mà Mytour đã chia sẻ dưới đây!

1. Phương pháp tính thuế nhập khẩu theo từng đối tượng

Ví dụ: Hãy tính thuế nhập khẩu dựa vào Đối tượng của các sản phẩm trong bảng dưới đây:

Trong ví dụ này, tại ô G4 hãy nhập công thức: =VLOOKUP(D4,$F$15:$G$18,2,0)*E4*F4

Trong đó:

• VLOOKUP: Là hàm được sử dụng để tìm kiếm thuế suất thuế nhập khẩu (%) trong Bảng quy định thuế.
• D4: Là đối tượng cần tìm, ở đây là các mã từ 1,2,3,4 tương ứng trong Bảng quy định thuế.
• $F$15:$G$18: Là bảng giới hạn dò tìm, cụ thể là F15:G18, nhưng có F4 cố định địa chỉ ô để khi sao chép công thức xuống các ô G5->G11 thì công thức vẫn giữ nguyên.
• 2: Thứ tự cột giá trị cần lấy, trong trường hợp này là cột thuế nhập khẩu.
• 0: Trong trường hợp này, ta lấy giá trị tuyệt đối nên chọn 0 hoặc False.
• E4: Là đơn giá sản phẩm để tính thuế nhập khẩu.
• F4: Là số lượng sản phẩm.

Tiếp theo, bạn sao chép công thức cho các ô từ G5->G11, ta sẽ nhận được kết quả như hình:

Tham khảo: Cách sử dụng hàm COUNTIF trong Excel một cách chi tiết và đơn giản

2. Cách sử dụng hàm VLOOKUP để tìm kiếm gần đúng

Ví dụ: Để phân loại học sinh dựa trên điểm trung bình trong bảng dưới đây, chúng ta sẽ chọn Range_lookup=1. Vì để phân loại theo bảng tham chiếu, bắt buộc phải lấy giá trị gần đúng, 9.2 gần với 9, 5.6 gần với 6 (làm tròn số thập phân trong toán học)… Dưới đây là các tiêu chí phân loại:

• Từ 9 – 10: 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).
• Dưới 5: Xếp loại yếu (x<5).

Để phân loại học sinh, bạn hãy sử dụng hàm VLOOKUP tại ô D4: =VLOOKUP(C4,$C$13:$D$16,2,1) hoặc =VLOOKUP(C4,$C$13:$D$16,2,TRUE)

Trong công thức trên, hàm VLOOKUP sẽ hoạt động theo nguyên tắc:

Giá trị 0≤x<4.5: Giá trị x được xem là gần với 0 → Đưa ra kết quả xếp loại Yếu.

Giá trị 4.5≤x<6.5: Giá trị x được xem là gần với 4.5 → Đưa ra kết quả xếp loại Trung bình.

Giá trị 6.5≤x<8.5: Giá trị x được xem là gần với 6.5 → Đưa ra kết quả xếp loại Khá.

Giá trị 8.5≤x: Giá trị x được xem là gần với 8.5 → Đưa ra kết quả xếp loại Giỏi.

Hàm VLOOKUP hoạt động theo nguyên tắc trên đã đáp ứng tiêu chí xếp loại học sinh dựa trên điểm trung bình, từ đó đưa ra các kết quả chính xác. Do đó, khi sử dụng hàm VLOOKUP, ngoài việc dựa vào công thức có sẵn, bạn cũng cần nắm rõ nguyên tắc hoạt động của hàm và cung cấp bảng tham chiếu phù hợp để tối ưu hóa hiệu quả của nó.

Lưu ý: Để thực hiện việc tra cứu gần đúng, bảng tham chiếu cần được sắp xếp theo thứ tự tăng dần. Nếu bảng không được sắp xếp, hàm VLOOKUP sẽ cho kết quả không chính xác.

Hướng dẫn cách sử dụng hàm VLOOKUP để tra cứu giá trị gần đúng:

3. Cách sử dụng hàm VLOOKUP kết hợp với Data Validation trong Excel

Trong ví dụ về cách sử dụng hàm VLOOKUP này, chúng ta sẽ tham chiếu đến ô có Data Validation để khi thay đổi giá trị trong danh sách thả xuống (dropdown validation), kết quả trả về từ hàm VLOOKUP cũng sẽ tự động thay đổi.

Bước 1: Thiết lập Data Validation bằng cách chọn ô C9 rồi nhấn vào tab Data/Data Validation.

Bước 2: Trong hộp thoại Data Validation, chọn tab Settings. Sau đó, chọn List tại mục Allow và cuối cùng chọn Source là vùng B4:B7.

Bước 3: Nhập công thức VLOOKUP vào ô C12 và C13 với các công thức lần lượt là =VLOOKUP(C9,B4:C7,2,0) và =VLOOKUP(C9,B4:D7,3,0). Ở đây, C9 là Số báo danh cần tìm, B4:C7 và B4:D7 lần lượt là vùng dữ liệu dùng để tra cứu tên và điểm theo số báo danh. Kết quả như hình:

Khi bạn thay đổi giá trị Số báo danh tại ô C9, các giá trị Tên (C12) và Điểm (C13) sẽ tự động thay đổi theo và đưa ra giá trị tương ứng.

4. Ví dụ về việc tìm mã ngành học bằng hàm VLOOKUP

Trong ví dụ này, chúng ta sẽ dựa vào bảng mã ngành để xác định tên ngành học dựa trên ký tự đầu tiên của số báo danh.

Trong ví dụ này, bạn cần kết hợp hàm VLOOKUP với hàm LEFT theo công thức: =VLOOKUP(LEFT(C9),G4:H5,2,0)

Trong đó, LEFT(C9) sẽ trích xuất ký tự đầu tiên từ số báo danh trong ô C9, ở đây là A, và hàm VLOOKUP sẽ dùng ký tự này để tra cứu trong bảng mã ngành.

5. Tra cứu dữ liệu trong một file Excel khác bằng hàm VLOOKUP

Để sử dụng hàm VLOOKUP tra cứu dữ liệu từ một file Excel khác, bạn thực hiện như sau:

Mở cả hai file trên cùng một màn hình. Sau đó, nhập công thức VLOOKUP vào file mà bạn muốn lấy dữ liệu, trong ví dụ là file Book2. Khi đã nhập giá trị cần tìm là SP1002 ở ô A2, bạn chọn vùng dữ liệu cần tra cứu trong file Book1 như sau:

=VLOOKUP(A2,[Book1]vd5!$A$2:$B$9,2,0)

Chú ý: Khi sử dụng công thức trên, bạn có thể chọn vùng tham chiếu bằng cách kéo thả tại Book1. Excel sẽ tự động hoàn tất công thức cho bạn, giúp giảm thiểu việc phải nhớ và ghép nối các công thức phức tạp.

Nếu file Book1 đã được đóng, công thức VLOOKUP vẫn sẽ hoạt động và Excel sẽ tự động điều chỉnh công thức VLOOKUP với đường dẫn đến file dữ liệu như sau:

=VLOOKUP(A2,’C:\Users\Admin\Documents\[Book1.xlsx]vd5′!$A$2:$B$9,2,0)

Hướng dẫn tra cứu dữ liệu từ một file Excel khác bằng hàm VLOOKUP:

Xem ngay: Cách sử dụng hàm RIGHT trong Excel một cách chi tiết nhất

IV. Những điểm cần lưu ý khi áp dụng hàm VLOOKUP trong Excel

Để hàm VLOOKUP có thể trả về kết quả chính xác, bạn nên ghi nhớ một số lưu ý dưới đây:

1. Sử dụng tham chiếu tuyệt đối

Để tránh sai sót trong kết quả, khi nhập công thức, hãy chuyển Table_array hoặc Lookup_value thành tham chiếu tuyệt đối bằng cách thêm dấu đô la $ trước các cột và hàng, để công thức không bị thay đổi.

Ví dụ: =VLOOKUP(C4,$C$13:$D$16,2,1)

Trong tình huống này, khi bạn sao chép công thức và dán vào các ô D5:D7, vùng tham chiếu sẽ được giữ nguyên từ C13:D16, đảm bảo rằng kết quả bạn nhận được luôn chính xác.

2. 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ố được định dạng dưới dạng văn bản và Lookup_value lại là số, hàm VLOOKUP sẽ trả về lỗi #N/A.

Như trong ví dụ dưới đây, dữ liệu tại ô B4:B7 đang ở định dạng văn bản trong khi Lookup_value tại ô C10 là dạng số => kết quả trả về là #N/A.

Giải pháp: Chuyển đổi định dạng ô B4:B7 thành số và hàm sẽ trả về kết quả chính xác.

3. Bảng dò tìm có các giá trị trùng lặp

Nếu bảng của bạn có nhiều giá trị trùng nhau, hàm VLOOKUP sẽ trả về kết quả đầu tiên mà nó phát hiện từ trên xuống dưới. Như trong ví dụ dưới, bảng có hai giá trị cho Mì gói là 10 và 6, hàm VLOOKUP sẽ trả về 10 vì đó là giá trị đầu tiên nó tìm thấy.

Giải pháp: Để loại bỏ các giá trị trùng lặp, bạn chọn toàn bộ bảng dò tìm và vào Data > Remove Duplicates

V. 5 hạn chế của hàm VLOOKUP

1. Chỉ hỗ trợ tìm kiếm từ trái sang phải

Hàm VLOOKUP chỉ cho phép tìm kiếm theo hướng từ trái qua phải và không thể thực hiện tìm kiếm ngược lại. Đây vẫn được coi là một trong những hạn chế lớn của VLOOKUP.

Giải pháp: Sử dụng các hàm Match và Index.

2. Chỉ hoạt động hiệu quả với các giá trị duy nhất

VLOOKUP thường chỉ xem xét giá trị tham chiếu đầu tiên và sẽ không tính đến các hàng khác có cùng giá trị. Nếu dữ liệu của bạn không có nhiều sự phân biệt, bạn cần thực hiện một số điều chỉnh nhỏ để VLOOKUP có thể tham chiếu đúng giá trị cần thiết.

Giải pháp: Tạo một bảng Pivot Table chứa các giá trị duy nhất, từ đó bạn có thể sử dụng hàm VLOOKUP một cách thông thường.

3. Mặc định là thiết lập “approximate match”

Điều kiện kiểm duyệt tham chiếu trong hàm VLOOKUP là tùy chọn (được biểu thị bằng hai dấu ngoặc vuông []), do đó nếu bạn không nhập gì vào đây, công thức sẽ tự động mặc định là “approximate match” (tìm kiếm gần đúng). Bạn cần lưu ý điều này nếu không muốn kết quả tham chiếu bị sai lệch.

Giải pháp: Hãy luôn chắc chắn rằng điều kiện kiểm duyệt được đặt thành “exact match” để có được kết quả tham chiếu chính xác nhất. Tuy nhiên, nếu bạn vẫn muốn sử dụng thiết lập mặc định là “approximate match”, hãy kiểm tra lại để đảm bảo độ chính xác.

4. Số thứ tự cột tham chiếu luôn cố định

Việc nhập số thứ tự của cột tham chiếu vào công thức bằng tay gây khó khăn và bất tiện khi bạn sao chép sang các ô khác hoặc khi bảng tham chiếu bị dịch chuyển.

Giải pháp: Bạn có thể áp dụng hàm Match để linh hoạt thay đổi số thứ tự cột.

5. Làm giảm tốc độ phản hồi của bảng tính

Một số ý kiến cho rằng việc sử dụng quá nhiều hàm VLOOKUP để tham chiếu có thể làm chậm tốc độ phản hồi của bảng tính và gây ra các vấn đề.

Giải pháp: Bạn không bị giới hạn về số lượng hàm VLOOKUP sử dụng, nhưng hãy cân nhắc thay thế chúng bằng lệnh Paste Special > Paste Value (chỉ dán giá trị) để chuyển đổi các hàm thành giá trị cố định. Điều này sẽ giúp bảng tính hoạt động hiệu quả hơn.

VI. Một số lỗi thường gặp khi sử dụng hàm VLOOKUP

1. Lỗi #NA

Lỗi #NA là một trong những lỗi thường gặp khi sử dụng các hàm trong Excel, đặc biệt là hàm VLOOKUP. Khi giá trị cần tra cứu không có trong bảng tra cứu, hàm VLOOKUP sẽ trả về lỗi #NA.

Chẳng hạn, nếu bạn muốn tra cứu giá trị tại A1, thì Table_array cần phải là C4:D11 để VLOOKUP có thể tìm kiếm trong cột D. Nhưng nếu Table_array là B4:C11, hàm sẽ tìm kiếm trong cột C và trả về kết quả #N/A.

Nếu bạn vẫn chưa xác định được nguyên nhân gây ra lỗi #N/A, hãy thử áp dụng công thức sau để khắc phục:

= IFERROR (công thức gốc, giá trị sẽ hiển thị khi có lỗi xảy ra trong công thức gốc).

Ví dụ: = IFERROR (VLOOKUP(D5,$C$5:$D$12,2,0), NA).

2. Lỗi #REF

Lỗi #REF xảy ra khi cột cần tra cứu không tồn tại trong bảng dữ liệu.

Cách khắc phục:

• Nếu lỗi #REF là do việc xóa cột => Bạn có thể nhấn nút Hoàn tác trên thanh công cụ hoặc sử dụng phím tắt CTRL + Z để khôi phục cột hoặc hàng đã xóa.
• Nếu lỗi #REF phát sinh từ việc VLOOKUP tham chiếu tới dải ô không chính xác => Bạn hãy điều chỉnh phạm vi để lớn hơn hoặc giảm số cột tra cứu cho phù hợp với dải tham chiếu.

Trong ví dụ sau, công thức =VLOOKUP(C4,$C$13:$D$16,3,1) sẽ trả về lỗi #REF! vì Col_index_num là 3, trong khi Table_array C13:D16 chỉ có 2 cột.

3. Lỗi #VALUE

Lỗi #VALUE xuất hiện do cách nhập công thức không chính xác. Trong trường hợp này, có hai sự cố chính:

• Sự cố 1: giá trị tra cứu có độ dài vượt quá 255 ký tự.

Giải pháp: Rút ngắn giá trị hoặc kết hợp hàm INDEX và MATCH để thay thế.

• Sự cố 2: Tham đối số chỉ mục cột chứa giá trị không hợp lệ hoặc nhỏ hơn 0.

Lỗi này có thể phát sinh khi bạn nhập không chính xác tham đối số chỉ mục cột hoặc vô tình chỉ định một số nhỏ hơn 1. Điều này thường xảy ra khi một hàm Excel khác được nhúng trong hàm VLOOKUP trả về giá trị không đúng, như “0”, cho tham đối số này.

Giá trị tối thiểu cho tham đối số chỉ mục cột là 1, trong đó 1 tương ứng với cột tìm kiếm, 2 là cột đầu tiên bên phải cột tìm kiếm, và cứ như vậy. Nếu bạn đang tra cứu trong cột A, thì số 1 sẽ chỉ cột đó, 2 sẽ chỉ cột B, 3 sẽ chỉ cột C...

Vậy là Mytour đã trình bày và hướng dẫn chi tiết về cách sử dụng hàm VLOOKUP cơ bản. Hy vọng rằng bài viết này sẽ hữu ích cho bạn trong quá trình làm việc với dữ liệu trên Excel. Hãy nhớ ghé thăm Mytour hàng ngày để không bỏ lỡ những tin tức mới nhất về chia sẻ kinh nghiệm và việc làm kế toán nhé!