Hàm VLOOKUP trong Excel

Excel

KHÁI NIỆM

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, đây có thể nói là hàm excel khá thông dụng và tiện lợi với những người hay sử dụng Excel để thống kê, dò tìm dữ liệu theo cột chính xác.

Cú Pháp Hàm Vlookup

=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])

Trong đó

– Lookup_value: Giá trị cần dò tìm.

– Table_array: Bảng giới hạn để dò tìm, bạn cần F4 để Fix cố định giá trị cho mục đích copy công thức tự động.

– Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm.

– Range_lookup: Là giá trị Logic (TRUE=1, FALSE=0) quyết định so chính xác hay so tương đổi với bảng giới hạn.

+ Nếu Range_lookup = 1 (TRUE): So tương đối.
+ Nếu Range_lookup = 0 (FALSE): So chính xác.
+ Nếu bỏ qua đối này thì Excel hiểu là Range_lookup = 1

Ví dụ

Khi bạn vào một cửa hàng để mua snack, việc đầu tiên là tìm được line (hàng) có snack trong khu vực nào của cửa hàng, vị trí chính xác chưng bày snack trong line đó, và quyết định mua, một là mua snack bất kì bạn gặp ngẫu nhiên, miễn là snack bạn sẽ mua (tương đối), hai là mua snack đúng loại bạn thích từ thương hiệu, loại, kích thước và hương vị (giá trị tuyệt đối).

Từ đó chúng ta có công thức mua snack như sau: Tìm được line có snack > Biết hàng đó trong khu vực nào của cửa hàng > Vị trí chính sát chưng bày snack trong line đó > Đưa ra quyết đinh mua snack theo hướng tương đối hay tuyệt đối.

1. Tìm được line có snack > Giá trị cần dò tìm (Lookup_value) => G6

Giá trị cần dò tìm trong hàm Vlookup

2. Trong khu vực nào có chứa snack của cửa hàng > Bảng chứa giá trị cần dò tìm (Table_array) => (B1:D4)

Bảng giá trị cần tìm trong hàm Vlookup

3. Vị trí chính xác chưng bày snack trong line đó > Vị trí cột cần lấy giá trị trong bảng chứa giá trị cần dò tìm (Col_index_num) => 2

Vị trí cần dò tìm trong hàm Vlookup

Lưu ý: Đếm số thứ tự cột trong bảng chứa giá trị cần dò tìm bạn phải đếm cột trái sang phải, vị trí đầu tiên bên trái của bảng bạn đặt số 1  và đếm theo thứ tự tăng dần đến hết bảng.

4. Đưa ra quyết định mua tương đối hay tuyệt đối > Tìm kiếm tương đối hoặc tuyệt đối (Col_index_num) =>1

Lưu ý: 

Tìm kiếm tương đối hoặc tuyệt đối thể hiện bằng số 1 hoặc 0 hoặc chữ TRUE và FALSE (1=TRUE hoặc 0=FALSE)

Nói một cách dễ hiểu giá trị tương đối là khi ta đi mua snack là mua chứ không phải đúng thương hiệu mình thích, loại, vị, cỡ ,…! Còn nếu ngược bạn phải mua đúng snack thương hiệu mình thích, loại, vị, cỡ,…   thì bạn hãy chọn tuyệt đối cho công thức của mình nè!

Chọn giá trị tuyệt đối, tương đối trong hàm Vlookup

Các bạn gõ dấu “=” sau đó nhập VLOOKUP(

Và ta có công thức hoàn chỉnh cho công cuộc đi mua snack: = Vlookup(G6,B1:D4,2,1)

Giá trị tìm được

Tại sao phải khóa bảng đối chiếu giá trị tìm kiếm? 

Khóa bảng tìm kiếm để máy tính hiểu rằng chỉ tìm giá trị trong bảng đã khóa, không tìm giá trị ra ngoài.

Trong trường hợp này hàm công thức đang tự đối chiếu giá trị rỗng ngoài bảng nên sẽ báo lỗi ngay khi bạn Enter đấy!

Lỗi không khóa bản tìm kiếm

Bị lỗi #N/A vậy nên cần khóa bảng để tìm đúng giá trị và tránh mắc lỗi ở các trường hợp cơ bản. Cẩn thận bạn nhé!

lỗi #N/A khi không khóa bản đối chiếu dữ liệu

Cách thực hiện khóa bảng đối chiếu dữ liệu: 

  • Nhấn F4 hoặc Fn+ F4 (cho một số Laptop) để khóa bảng chứa giá trị cần dò tìm.
  • Khóa bảng chứa giá trị cần dò tìm sẽ giúp cho công thức của bạn tìm đúng giá trị trong bảng đã khóa không đối chiếu ra ngoài ô khác. Và tránh lỗi #NA khi không tìm thấy giá trị cần tìm 
  • Khi khóa bảng sẽ có biểu thức hiển thị như sau: $G$2:$H$14.

Ví dụ 2

 Bạn hãy tính thuế nhập khấu theo Đối tượng của các mặt hàng dưới đây:

Ví dụ tính thuế nhập khẩu các mặt hàng theo đối tượng

Trong ví dụ trên, tại ô G5 ta gõ công thức: =VLOOKUP(D5;$D$17:$F$20;2;1)*E5

Trong đó:

  • Vlookup: là hàm dùng để tìm kiếm ra thuế nhập khẩu tại Bảng quy định thuế.
  • D5: Giá trị là đối tượng cần tìm ở đây là các đối tượng từ 1,2,3,4.
  • $D$17:$F$20: Bảng giới hạn dò tìm, chính là D17:F20 nhưng được F4 để Fix cố định giá trị để Copy công thức xuống các ô G6->G12.
  • 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
  • 1: Trường hợp này Bách lấy giá trị tương đối nên chọn là 1, trường hợp này ta cũng có thể chọn là O.
  • *E5: Chính là đơn giá sản phẩm để tính ra thuế nhập khẩu.

Công thức tính

Với công thức trên, kết quả ta được là:

Copy công thức xuống các ô G6->G12 ta được kết quả như ảnh trên.

Sẽ có bạn hỏi mình, làm thế nào xác định được Range_lookup=1 hay Range_lookup=0?

Theo đúng định nghĩa của Microsoft Office Range_lookup=1 khi chúng ta cần tìm giá trị tương đối nghĩa là gần đúng.

Ví dụ: Để xếp loại học sinh dựa trên điểm trung bình trong trường hợp dưới đây thì Range_lookup=1, vì để xếp loại theo bảng tham chiếu thì ta bắt buộc phải lấy giá trị tương đối nghĩa là gần đúng, 9.1 gần với 9, 5.3 gần với 5 …

Họ và tênĐiểm trung bìnhXếp loại
Nguyễn Văn Tài9.1Giỏi
Nông Văn Cống5.3Trung Bình
Tống Văn Tần6.6 Khá

 Bảng tham chiếu:

Điểm trung bìnhXếp loại
9Giỏi
5Trung Bình
6.5Khá

Và Range_lookup=O khi chúng ta cần tìm giá trị đúng tuyệt đối, ví dụ tính thuế nhập khẩu trên chẳng hạn.

Một số lỗi thường gặp và cách khắc phục

1. Lỗi #NA

  • #NA: dò tìm không có giá trị.
  • Cách khắc phục:

Nếu chưa phát hiện lỗi #NA  thì bạn có thể khắc phục lỗi #NA 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).

Lấy ví dụ trên minh họa: 

= IFERROR (VLOOKUP(B5,$G$2:$H$14,2,0)”Tìm giá trị lỗi”)

2. Lỗi #REF

  • #REF: cột dò tìm không có trong bảng chứa giá trị dò tìm.
  • Cách khắc phục 

Ví dụ lỗi #REF! xảy ra do việc xóa cột.

-> Bạn có thể bấm ngay nút Hoàn tác trên Thanh công cụ Truy nhập Nhanh (hoặc nhấn CTRL+Z) để khôi phục hàng hoặc cột đó.

Ví dụ: VLOOKUP với tham chiếu dải ô không chính xác.

-> Điều chỉnh phạm vi có 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. =VLOOKUP(A8,A2:E5,5,false) sẽ là một phạm vi tham chiếu hợp lệ, như làm =VLOOKUP(A8,A2:D5,4,FALSE).

3. Lỗi #VALUE

  • #VALUE: lỗi với cách nhập công thức của bạn.
  • Cách Khắc phục 

a. Sự cố: 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ế.

b. Sự cố: Số thứ tự cột giá trị hiển thị (Col_index_num) chứa văn bản hoặc nhỏ hơn 0.

  • Sự cố này có thể xảy ra do lỗi đánh máy trong Số thứ tự cột giá trị hiển thị 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 số thứ tự cột giá trị hiển thị.
  • Giá trị tối thiểu cho số thứ tự cột giá trị hiển thị 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, v.v.. Vì vậy, 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, v.v..

Hi vọng với bài viết chia sẻ này các bạn sẽ nắm được chi tiết về hàm Vlookup và cách sử dụng.

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *