Cách Dùng Hàm VLOOKUP Trong Excel Chi Tiết, Siêu Đơn Giản

By Như Naila

03/07/2023

0

1682

Cách dùng hàm VLOOKUP chi tiết nhất — Hướng dẫn cách dùng hàm VLOOKUP chi tiết nhất

Trong Excel, hàm VLOOKUP là một công cụ hữu ích giúp bạn tìm kiếm và trả về dữ liệu tương ứng. Nếu bạn chưa biết cách sử dụng hàm này, hãy tham khảo ngay hướng dẫn cách dùng hàm VLOOKUP chi tiết kèm ví dụ cụ thể trong bài viết dưới đây!

Mục lục

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 dễ dàng tìm kiếm và trích xuất dữ liệu từ một bảng dữ liệu lớn. Với VLOOKUP, bạn có thể nhanh chóng xác định một giá trị trong một cột và trả về giá trị tương ứng từ cột khác trong bảng.

Hàm VLOOKUP có ý nghĩa rất quan trọng trong việc phân tích dữ liệu và thực hiện các tác vụ tính toán trong Excel. Thay vì phải duyệt qua toàn bộ bảng dữ liệu để tìm kiếm một giá trị cụ thể, VLOOKUP cho phép bạn thực hiện việc 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 suất làm việc.

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

Sau đây là công thức của hàm VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Trong đó:

Lookup_value: là giá trị cần dò tìm.
table_array: là bảng cần dò tìm.
col_index_num: là vị trí cột cần lấy giá trị.
range_lookup: nhập số 0 để tìm chính xác hoặc nhập số 1 để tìm gần đúng (thông thường nhập số 0).

***Lưu ý:

Để kết quả trả về chính xác nhất, bạn cần sử dụng dấu $ để cố định Table_array bằng cách sử dụng nút F4 hoặc Fn+F4 sau khi chọn vùng bảng. (VD: $H$6:$J$13).
Tùy theo mỗi máy tính mà sẽ dùng dấu phẩy (,) hoặc dấu (;) cho hàm VLOOKUP.

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

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

1. Cách tính thuế nhập khẩu theo đối tượng

Ví dụ: Hãy tính thuế nhập khẩu theo Đối tượng của các mặt hàng trong bảng dưới đây:

Trong ví dụ trên, tại ô G4 gõ công thức: =VLOOKUP(D4,$F$15:$G$18,2,0)*E4*F4

Cách dùng hàm VLOOKUP để tính thuế nhập khẩu theo đối tượng

Trong đó:

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

Sau đó, bạn copy công thức cho các ô từ G5->G11, ta được kết quả như hình:

Kết quả dùng hàm VLOOKUP tính thuế nhập khẩu

Hướng dẫn cách dùng hàm VLOOKUP tính thuế nhập khẩu theo đối tượng tại đây:

Hướng dẫn dùng VLOOKUP để tính thuế nhập khẩu

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

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

Ví dụ: Để xếp loại học sinh dựa trên điểm trung bình trong bảng dưới đây thì chúng ta sẽ chọn Range_lookup=1. Bởi để xếp loại theo bảng tham chiếu thì bắt buộc phải lấy giá trị xấp xỉ (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à tiêu chí xếp loại:

Từ 9 – 10: Loại giỏi (9 ≤ x).
Từ 6.5 đến dưới 9: Loại khá (6.5 ≤ x < 9).
Từ 5 đến dưới 6.5: Loại trung bình (5 ≤ x < 6.5).
Dưới 5: Loại yếu (x<5).

Để xếp loại học sinh, bạn 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)

Ví dụ sử dụng hàm VLOOKUP tra cứu gần đúng

Ở công thức trên, hàm VLOOKUP sẽ làm việc theo nguyên tắc:

Giá trị 0≤x<4.5: Giá trị x được cho 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 cho 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 cho 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 cho là gần với 8.5 → Đưa ra kết quả xếp loại Giỏi.

Hàm VLOOKUP làm việc theo nguyên tắc trên đã thỏa mãn được tiêu chí xếp loại học sinh dựa trên điểm trung bình đề ra, từ đó đưa ra các kết quả đúng. Vì vậy, trong quá trình sử dụng hàm VLOOKUP, ngoài dựa vào công thức có sẵn, bạn cũng cần hiểu rõ nguyên tắc làm việc của hàm và đưa ra bảng tham chiếu đúng nhằm giúp hàm phát huy tối đa hiệu quả của nó.

Lưu ý: Để thực hiện tra cứu gần đúng, bảng tra cứu cần được sắp xếp theo thứ tự từ nhỏ đến lớn. Nếu không sắp xếp, hàm VLOOKUP sẽ ra cho kết quả sai.

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

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

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

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

Danh sách thả xuống bằng Data Validation

Bước 1: Chuẩn bị Data Validation bằng cách chọn ô C9 rồi bấm vào thẻ Data/Data Validation.

Tạo Data Validation để sử dụng cùng hàm VLOOKUP

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

Thiết lập vùng dữ liệu cho Data Validation

Bước 3: Nhập hàm VLOOKUP tại ô C12 và C13 lần lượt là =VLOOKUP(C9,B4:C7,2,0) và =VLOOKUP(C9,B4:D7,3,0). Trong đó C9 là Số báo danh cần dò tìm, B4:C7 và B4:D7 lần lượt là vùng dữ liệu để 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) cũng sẽ thay đổi và đưa ra giá trị tương ứng.

Xem thêm các tin đăng việc làm công sở tại website Muaban.net:

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

Ở ví dụ lần này, chúng ta sẽ dựa vào bảng mã ngành để tìm ra 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à hàm LEFT như sau: =VLOOKUP(LEFT(C9),G4:H5,2,0)

Trong đó, LEFT(C9) sẽ lấy ra ký tự đầu tiên trong số báo danh được lưu trong ô C9 là A, và hàm VLOOKUP sẽ sử dụng ký tự này cho việc tra cứu trong bảng mã ngành.

Bạn có thể xem video chi tiết về cách dùng hàm VLOOKUP ở ví dụ 3 và 4 tại đây:

5. Tra cứu ở 1 file Excel khác với hàm VLOOKUP

Để có thể dùng VLOOKUP tra cứu dữ liệu ở một file Excel khác, bạn làm như sau:

Mở 2 file cùng một màn hình. Sau đó, nhập công thức VLOOKUP ở file muốn lấy dữ liệu, trong hình là file Book2. Sau khi nhập giá trị muốn tìm kiếm là SP1002 được lưu trong ô 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)

Lưu ý: Trong trường hợp sử dụng công thức trên, bạn có thể chọn vùng bảng tham chiếu bằng cách kéo thả vùng đó tại Book1. Công thức sẽ được excel tự động điền vào cho bạn, từ đó giúp bạn hạn chế việc phải nhớ và ráp công thức phức tạp.

Cách dùng hàm VLOOKUP tra cứu ở 1 file Excel khác

Trong trường hợp File Book1 đóng, thì công thức VLOOKUP vẫn sẽ hoạt động và Excel sẽ tự động “viết lại” công thức VLOOKUP với đường dẫn tới 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 cách tra cứu ở 1 file Excel khác với hàm VLOOKUP:

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

IV. Những lưu ý khi sử dụng hàm VLOOKUP trong Excel

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

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

Để tránh kết quả bị sai lệch, trong quá trình nhập công thức, hãy đổi Table_array hoặc Lookup_value thành tham chiếu tuyệt đối bằng cách để 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 trường hợp này, khi bạn copy công thức và dán vào các ô D5:D7, bảng tham chiếu sẽ được cố định từ C13:D16, giúp cho kết quả đưa ra của bạn 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ố đang được định dạng văn bản và Lookup_value lại là dạng số thì hàm VLOOKUP sẽ trả về lỗi #N/A.

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

Không lưu trữ giá trị số dưới dạng văn bản khi dùng VLOOKUP

Giải pháp: Chuyển định dạng ô B4:B7 thành dạng số và hàm sẽ trả về kết quả bình thường.

3. Bảng dò tìm chứa các giá trị bị trùng

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

Giải pháp: Nếu bạn muốn loại bỏ giá trị trùng lặp, bạn bôi đen bảng dò tìm và chọn Data > Remove Duplicates

Giải pháp khi bị lỗi trùng lặp dữ liệu trong dùng hàm VLOOKUP

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

1. Chỉ hỗ trợ tham chiếu từ trái sang phải

Thực tế là hàm VLOOKUP chỉ hoạt động theo hướng từ trái sang phải và không hỗ trợ tìm kiếm theo chiều ngược lại. Do đó, điều này vẫn được xem là một hạn chế lớn của VLOOKUP.

Biện pháp khắc phục: Sử dụng các hàm Match và Index.

2. Chỉ hoạt động hiệu quả với các giá trị riêng biệt

VLOOKUP thường chỉ quan tâm đến giá trị tham chiếu đầu tiên và bỏ qua các dòng khác chứa giá trị tương tự. Tuy nhiên, nếu dữ liệu của bạn không có nhiều sự phân biệt, bạn sẽ cần phải thực hiện một số điều chỉnh nhỏ để VLOOKUP có thể tham chiếu đúng giá trị mong muốn.

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 bình thường.

3. Mặc định 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ởi hai dấu ngoặc vuông []), vì vậy nếu bạn không điền bất kỳ nội dung nào vào đó, công thức sẽ tự động mặc định là “approximate match” (tìm kiếm gần đúng). Do đó, bạn cần chú ý nếu bạn không muốn kết quả tham chiếu bị sai lệch.

Giải pháp: Luôn luôn chú ý đặt điều kiện kiểm duyệt là “exact match” để đạt đượ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” thì bạn cần phải kiểm tra lại.

Tham khảo ngay: Cách cố định cột trong Excel cực đơn giản và chi tiết

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

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

Giải pháp: bạn có thể sử dụng hàm Match để liên tục biến đổi số thứ tự.

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

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

Giải pháp: Bạn sẽ không bị giới hạn về số lượng sử dụng hàm VLOOKUP, tuy nhiên hãy cố gắng thay thế chúng bằng lệnh Paste Special > Paste Value (chỉ dán giá trị) để thay thế các hàm bằng giá trị cố định. Điều này sẽ giúp chương trì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à lỗi phổ biến khi sử dụng các hàm trong Excel nói chung và hàm VLOOKUP nói riêng. Hàm VLOOKUP sẽ trả về lỗi #NA nếu giá trị dùng để tra cứu không được tìm thấy trong bảng tra cứu.

Như ví dụ dưới, muốn tra cứu A1 thuộc đối tượng nào thì Table_array phải là C4:D11 để VLOOKUP tìm kiếm ở cột D. Tuy nhiên, trong hình Table_array là B4:C11 nên VLOOKUP sẽ tìm kiếm trong cột C và cho ra kết quả #N/A.

Nếu chưa tìm ra lỗi #N/A, bạn có thể khắc phục lỗi này bằng công thức sau:

= IFERROR (công thức gốc, giá trị sẽ hiển thị nếu công thức gốc có lỗi).

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

2. Lỗi #REF

Lỗi #REF xuất hiện khi cột dò tìm không có trong bảng chứa giá trị dò tìm.

Cách khắc phục:

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

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

3. Lỗi #VALUE

Lỗi #VALUE là lỗi với cách nhập công thức của bạn. Ở trường hợp này sẽ do 2 sự cố bao gồm:

Sự cố 1: tham đối giá_trị_tra_cứu có nhiều hơn 255 ký tự.

Giải pháp: Rút ngắn giá trị hoặc sử dụng kết hợp hàm INDEX và MATCH như một giải pháp thay thế.

Sự cố 2: Tham đối số_chỉ_mục_cột chứa văn bản hoặc nhỏ hơn 0.

Sự cố này có thể xảy ra khi đánh máy trong tham đối số_chỉ_mục_cột hoặc vô tình chỉ định một số nhỏ hơn 1 làm giá trị chỉ mục (hiện tượng phổ biến nếu một hàm Excel khác được lồng trong hàm VLOOKUP trả về một số, chẳng hạn như “0”, làm tham đối số_chỉ_mục_cột).

Giá trị tối thiểu cho tham đối số_chỉ_mục_cột là 1, trong đó 1 là cột tìm kiếm, 2 là cột đầu tiên ở bên phải của cột tìm kiếm… Do đó, nếu bạn đang tìm kiếm trong cột A thì số 1 sẽ tham chiếu cột đó, 2 là cột B, 3 là cột C…

Như vậy là Muaban.net đã giới thiệu cũng như hướng dẫn chi tiết cách dùng hàm VLOOKUP cơ bản. Hy vọng bài viết sẽ giúp ích cho bạn trong quá trình làm việc với dữ liệu trên Excel. Đừng quên truy cập Muaban.net mỗi ngày để không bỏ lỡ những tin đăng mới nhất về chia sẻ kinh nghiệm, việc làm kế toán nhé!

Xem thêm:

BÀI VIẾT LIÊN QUAN

BÀI VIẾT MỚI NHẤT