Tất tần tật về cách dùng hàm VLOOKUP trong Excel

Hàm VLOOKUP là hàm tra cứu dữ liệu cực kỳ hữu dụng trong Excel. Nếu bạn chưa biết cách dùng hàm VLOOKUP thì theo dõi ngay bài viết này. Phần mềm Marketing sẽ hướng dẫn bạn cách sử dụng hàm VLOOKUP trong Excel chi tiết nhất.

I. Hàm VLOOKUP là gì ?

Hàm VLOOKUP là hàm dùng để tra cứu dữ liệu trong một bảng, một phạm vi theo chiều dọc và trả kết quả dữ liệu về theo hàng ngang tương ứng.

Hàm VLOOKUP rất hữu dụng cho các trường dữ liệu dạng tìm kiếm thông tin và sắp xếp chúng dựa trên tiêu chí có sẵn trên bảng tham chiếu.

Hàm VLOOKUP được ghép từ chữ V – Vertical: hàng dọc và LOOK UP: tìm kiếm. Một hàm tra cứu dữ liệu khác tương tự đó chính là hàm HLOOKUP trong Excel – hàm tìm kiếm dữ liệu theo hàng ngang.

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

Công thức của hàm VLOOKUP như sau: =VLOOKUP(Lookup_value; Table_array; Col_index_num; Range_lookup)

Trong đó:

• Lookup_value: Giá trị cần tìm kiếm, bạn có thể điền giá trị trực tiếp hoặc tham chiếu tới ô chứa dữ liệu.
• Table_array: Bảng dữ liệu dùng để tham chiếu
• Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng dữ liệu tham chiếu, nếu bỏ qua trường này thì mặc định là cột 1
• Range_lookup: Lựa chọn dạng tìm kiếm dữ liệu. Nếu range_lookup = 1(TRUE): tìm kiếm dữ liệu tương đối, nếu range_lookup = 0(FALSE): tìm kiếm dữ liệu chính xác).

Thông thường, đối với trường dữ liệu trong bảng tham chiếu sẽ sử dụng dạng tham chiếu tuyệt đối để không bị sai dữ liệu khi sao chép công thức.

III. Ví dụ cụ thể cách dùng hàm VLOOKUP

1. Sử dụng hàm VLOOKUP tính phụ cấp cho nhân viên theo chức vụ

Để hỗ trợ nhân viên trong giai đoạn suy thoái kinh tế, công ty A quyết định sẽ cấp phụ cấp cho nhân viên theo từng chức vụ cụ thể như bảng 2 (B16:C21). Lúc này, dựa vào danh sách nhân viên cùng với chức vụ có sẵn ở bảng 1, ta sẽ xác định mức phụ cấp tương ứng.

Lúc này, bạn cần dò tìm giá trị tương ứng với chức vụ của nhân viên tại bảng 1 và tham chiếu vào mức phụ cấp tương ứng đối với các chức vụ tại bảng 2. Khi đối chiếu đúng giá trị, mức phụ cấp sẽ được điền vào mã nhân viên tương ứng.

Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$16:$C$21,2,0)

• Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
• 2 là số thứ tự của cột dữ liệu.
• Range_lookup = 0 (FALSE) để dò tìm chính xác.

Tiếp theo, bạn sao chép công thức sang các ô còn lại để xác định được mức phụ cấp cho các nhân viên.

Bài viết liên quan: Cách cố định ô tham chiếu trong Excel

2. Dùng VLOOKUP để xếp loại học sinh theo điểm số

Sau khi kết thúc học kỳ, điểm trung bình của các học sinh sẽ được tổng hợp tại file Excel như bảng 1. Tiêu chí xếp loại học lực của học sinh được sắp xếp tại bảng 2 (B11:C15)

Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$11:$C$15,2,1)

• Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
• 2 là số thứ tự của cột dữ liệu.
• Range_lookup = 1 (TRUE) để dò tìm điểm số có giá trị gần nhất.

Excel sẽ tiến hành tìm kiếm dữ liệu tại bảng 2. Nếu thấy giá trị của D4 gần nhất với giá trị trong bảng sẽ trả về kết quả tương ứng tại cột 2. 

Với cách tính điểm này, bạn cũng có thể sử dụng hàm IF trong Excel để đảm bảo độ chính xác nhất.

IV. Cách dùng hàm VLOOKUP giữa 2 sheet

Nếu như dữ liệu tham chiếu của bạn nằm trên 2 sheet khác nhau, bạn cũng có thể sử dụng công thức VLOOKUP để tham chiếu dạng dữ liệu này. Khi đó, công thức hàm VLOOKUP khi tham chiếu dữ liệu giữa 2 sheet như sau: =VLOOKUP(lookup_value, Sheet!range, col_index_num, [range_lookup])

Điểm khác biệt đó chính là sử dụng tham số Sheet!range thay vì table_array như công thức ban đầu. Sheet!range là vùng dữ liệu được chọn tại bảng tính lấy dữ liệu.

Ví dụ cụ thể: 

Cho 2 sheet khác nhau với: Sheet 1 (nhanvien) là danh sách nhân viên đang cần nhập số tiền thưởng, sheet 2 (thuong) là điều kiện xếp loại ứng với tiền thưởng.

Đầu tiên, thiết lập công thức hàm VLOOKUP bằng cách chọn 1 ô cho 1 nhân viên cần tính tiền thưởng. Tại đây chọn ô D3 và nhập công thức:

=VLOOKUP(C3,thuong!$B$4:$C$6,2,FALSE)

Trong đó:

• Lookup_value: là ô chứa dữ liệu C3
• Table_array: trong trường hợp này là Sheet!range. Bạn có thể nhập tay phạm vi tham chiếu trong bảng tính thứ 2 hoặc chuyển sang bảng tính thứ 2 giữ Ctrl và kéo chọn vùng dữ liệu.
• Col_index_num: Là 2 vì chúng ta muốn sao chép một giá trị từ cột C, là cột thứ 2 trong trường Table_array
• Range_lookup: Được đặt thành FALSE để tìm kiếm kết quả khớp chính xác.

Lưu ý rằng, khi dùng sheet!range, bạn cần nhập đúng tên sheet chứa dữ liệu để đảm bảo dữ liệu được tham chiếu chính xác. Ví dụ: Tham chiếu B10:C15 tại sheet tailieu, ta có cách viết: tailieu!$B$10:$C$15.

Bài viết liên quan: Các hàm cơ bản trong Excel dành cho nhân viên văn phòng

V. Cách sử dụng hàm VLOOKUP giữa 2 file khác nhau

Tương tự như các dùng hàm VLOOKUP giữa 2 sheet, để đối chiếu dữ liệu giữa 2 file khác nhau bạn cũng có thể làm được. Để dễ hiểu hơn, bạn xem ví dụ sau:

Ta tạo 2 file khác nhau, với file 1 (hocsinh) là danh sách học sinh cần điền xếp loại và file 2 (xeploai) là điều kiện để xếp loại tương ứng với số điểm.

• Bước 1: Tại ô D2 trên file 1, bắt đầu nhập công thức: =VLOOKUP(C2;

• Bước 2: Chuyển sang file 2 (đã được tạo từ trước với tên là dulieu.xlsx), chọn vùng dữ liệu A4:B7 bằng cách nhấn phím Ctrl kéo từ ô A4 đến B7.

• Bước 3: Quay trở lại file 1 và nhập thêm “2, TRUE” vào công thức thì ta được kết quả sau

Sau đó, kéo sao chép công thức sang các ô để hoàn thành bảng tính bằng cách sử dụng hàm VLOOKUP giữa 2 file khác nhau.

VI. Các lỗi thường gặp khi sử dụng hàm VLOOKUP

Khi sử dụng hàm VLOOKUP, nếu không cẩn thận bạn có thể sẽ gặp những lỗi sau đây:

1. Lỗi #N/A

Lỗi #N/A được trả về khi hàm tính VLOOKUP không tìm thấy dữ liệu trong bảng tính tham chiếu. Lỗi này thường liên quan đến vùng tham chiếu table_array. Khi đó, điều bạn cần làm đó chính là đối chiếu lại bảng tham chiếu xem có bị sai định dạng hay thay đổi bảng tham chiếu hay không. Lỗi phổ biến nhất khi tham chiếu đó chính là không sử dụng tham chiếu tuyệt đối ($) tại bảng tham chiếu.

2. Lỗi #REF

Lỗi #REF xảy ra khi Col_index_num lớn hơn số cột có trong table_array. Ví dụ bảng tham chiếu B10:C15 (bao gồm 2 cột) nhưng Col_index_num lại là 3. Khi đó phép tính sẽ báo lỗi #REF.

3. Lỗi #VALUE

Lỗi #VALUE xảy ra khi cột Col_index_num nhỏ hơn 1 trong công thức tính. Trong bảng giá trị Table_array có tối thiểu là 1 cột, vì thế Col_index_num chỉ có thể nhỏ nhất là 1. Khi đó, nếu đối chiếu công thức, Col_index_num nhỏ hơn 1 thì công thức sẽ báo lỗi #VALUE.

4. Lỗi #NAME?

Lỗi #NAME? xuất hiện khi Lookup_value thiếu dấu ngoặc kép (“). Để tìm kiếm giá trị định dạng văn bản (Text), bạn dùng dấu ngoặc kép để Excel có thể hiểu công thức.

VII. Lưu ý khi sử dụng hàm VLOOKUP trong Excel

Khi dùng hàm VLOOKUP, bạn cần lưu ý những yếu tố sau để thực hiện hàm tính một cách chính xác nhất.

• Sử dụng tham chiếu tuyệt đối: Khi sao chép công thức hãy biến Table_array hoặc Lookup_value thành tham chiếu tuyệt đối bằng cách đặt dấu đô la ($) trước các cột và hàng để công thức không bị thay đổi.
• 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 để dưới 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.
• Bảng dò tìm chứa giá trị bị trùng: Nếu bảng biểu 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. Khi này, bạn có thể xóa dữ liệu trùng lặp trước khi chọn bảng dữ liệu.
• Hàm tính VLOOKUP không phân biệt chữ hoa, chữ thường.

Kết luận

Trên đây, Phần mềm Marketing đã hướng dẫn bạn chi tiết cách sử dụng hàm VLOOKUP trong Excel. Đây là hàm tính cực kỳ hữu dụng và phổ biến khi làm việc với Excel. Hy vọng những thông tin trên giúp bạn tối ưu thời gian và dùng hàm VLOOKUP một cách hiệu quả. Cảm ơn bạn đã đọc bài viết này.