Hàm Vlookup Trong Excel | Cách Dùng Hàm Vlookup Chi Tiết

964 Views 0

Cách sử dụng hàm VLOOKUP từ cơ bản – Nâng cao: Định nghĩa, Cú pháp, Ví dụ và cách lồng với các hàm Excel khác nhau. Hãy cùng Group Tin Học tìm hiểu các ứng dụng trong hàm Vlookup Chi Tiết nhất vào các bài tập, và thực tiến trong excel nhé.

Hàm Vlookup là gì?

Hàm VLOOKUP là hàm dò tìm dữ liệu trong Excel. Tôi giả định rằng bạn đã có kiến thức nền về Excel và có thể sử dụng những hàm cơ bản như SUM, AVERAGE, và TODAY.

Ta sử dụng hàm nay khi tìm một giá trị nào đó, thông qua một giá trị khác. Nói 1 cách dễ hiểu hơn, khi có 1 giá trị tra cứu, chúng ta có thể thông qua nó để dò thông tin nào đó từ một mảng dữ liệu khác. Để hình dung rõ hơn, các bạn hãy theo dõi nội dung ở dưới đây nhé!

Do đó, mọi người quen gọi đây là hàm tìm kiếm theo cột.
V = Vertical: Dọc => Vlookup: Tìm kiếm theo cột
H = Horizontal: Ngang => Hlookup: Tìm kiếm theo hàng

Cách sử dụng hàm Vlookup trong excel

Cùng tìm hiểu cách sử dụng hàm vlookup trong excel và sự kết hợp giữa Vlookup và các hàng khác nhau nhé. Hàm VLOOKUP sẽ dò tìm một hàng (row) chứa giá trị mà bạn cần tìm ở cột đầu tiên (bên trái) của một bảng dữ liệu. Nếu tìm thấy, nó sẽ tìm tiếp trong hàng này, và sẽ lấy giá trị ở cột mà bạn đã chỉ định trước.

Cú pháp hàm vlookup

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

Giải thích cú pháp

lookup_value: Giá trị dùng để tìm kiếm trong cột đầu tiên của table_array, giá trị này có thể là một số, một chuỗi, hoặc là một tham chiếu.
table_array: Bảng dùng để dò tìm, có thể là một vùng tham chiếu hoặc là tên (name) của một vùng đã được đặt tên
col_index_num: Số thứ tự của các cột trong table_array, chứa kết quảmà bạn muốn tìm kiếm.
col_index_num: Số thứ tự này được tính từ trái sang phải (cột chứa lookup_value là cột thứ nhất)
range_lookup: Là một giá trị kiểu Boolean (chỉ mang giá trị TRUE hoặc FALSE) để chỉ kiểu tìm kiếm: chính xác hay tương đối.
+ TRUE (hoặc 1 là mặc định): Là kiểu dò tìm tương đối VLOOKUP sẽ tìm giá trị lookup_value đầu tiên mà nó tìm được trong cột đầu tiên của table_array. Trong trường hợp không tìm ra, nó sẽ trả về giá trị lớn nhất mà nhỏ hơn lookup_value
+ FALSE (hoặc 0): Là kiểu dò tìm chính xác VLOOKUP sẽ tìm chính xác giá trị lookup_value trong cột đầu tiên của table_array. Trong trường hợp không có, hoặc lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của table_array, nó sẽbáo lỗi #N/A!

Lưu ý khi sử dụng hàm Vlookup:

Để có kết quả chính xác khi range_lookup = TRUE, bạn phải sắp xếp các giá trị các giá trị trong cột đầu tiên của table_array từ nhỏ đến lớn.
Nếu cột đầu tiên của table_array chứa các giá trị kiểu text, bạn có thể dùng các ký tự đại diện cho lookup_value (dấu * đại diện cho nhiều ký tự/ hoặc dấu ? đại diện cho một ký tự)
Nếu lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của table_array, hàm sẽ báo lỗi #N/A!
Nếu không tìm thấy lookup_value khi range_lookup = FALSE, hàm sẽ báo lỗi #N/A!
Nếu col_index_num nhỏ hơn 1, hàm sẽ báo lỗi #VALUE!, còn nếu col_index_num lớn hơn số cột trong table_array, hàm sẽ báo lỗi #REF!

Ví dụ sử dụng hàm vlookup

Như bạn thấy trong hình phía dưới. Ta áp dụng hàm Vlookup để tìm được Phụ cấp chức vụ khi biết Chức vụ.

D3 = Vlookup(C3, $H$3:$I$7, 2, 0)

Diễn giải hàm tìm kiếm:

Điều kiện tìm kiếm (ô C3): GĐ
Vùng tìm kiếm (vùng $H$3:$I$7) : Bảng phụ cấp
Số thứ tự cột chứa giá trị cần tìm: 2. (Trong bảng phụ cấp thì cột số tiền phụ cấp có thứ 2 là 2 từ cột đầu tiên của vùng tìm kiếm).
Kiểu tìm kiếm: 0 – đây là kiểu tìm kiếm chính xác 100%

HÀM VLOOKUP KẾT HỢP LEFT

Cách sử dụng hàm VLOOKUP kết hợp LEFT để tìm kiếm giá trị trong bảng. Đây là 1 kiểu hàm lồng phổ biến trong excel và mang tính chất thực tiễn cao.

Ví dụ về cách sử dụng hàm Vlookup kết hợp Left Để học nhanh và hiệu quả, chúng ta sẽ cùng tìm hiểu ví dụ sau:

YÊU CẦU:

Tìm Tên hàng và điền vào cột C dựa vào hai dữ liệu (hình dưới):
Mã tên hàng: Chính là 2 ký tự đầu của Mã hàng (cột B)
Bảng phụ A: Chứa mã hàng và tên hàng tương ứng (Vùng H2:J5)

GỢI Ý:

Để có thể tìm được mã hàng chúng ta phải dùng hàm Vlookup kết hợp Left
Hàm Vlookup: Hàm tìm kiếm theo cột => Tìm theo 2 ký tự đầu của mã hàng.
Hàm Left: Hàm tách ký tự từ phía bên trái với số lượng ký tự lấy ra do mình quy định.

Cách sử dụng hàm vlookup VLOOKUP KẾT HỢP LEFT Xem ví dụ

CÚ PHÁP & BÀI GIẢI

C4 = Vlookup( Left (B4, 2), $H$3:$J$5, 2, 0)

Diễn giải cú pháp:

Lookup_value: Điều kiện tìm kiếm là 2 ký tự đầu của mã hàng. Ta dùng hàm Left để tách ra 2 ký tự tính từ bên trái của mỗi mã hàng.
Cú pháp: Left (B4,2)
Table_array: Vùng tìm kiếm là bảng tra tên hàng thuộc vùng ô H3:J5
Col_index_num: Số thứ tự cột chứa giá trị cần tìm là 2
[Range_lookup]: Kiểu tìm kiếm là 0 – chính xác 100%

Lưu Ý: Cần cố định vùng tìm kiếm để khi copy công thức thì vùng điều kiện không bị thay đổi. Điều đó đảm bảo công thức tính toán đúng và đủ cho tất cả các giao dịch.

HÀM VLOOKUP KẾT HỢP MATCH

Hàm Vlookup kết hợp hàm Match sẽ giúp cho công thức của bạn linh hoạt hơn rất nhiều lần. Bài viết giúp bạn hiểu tại sao lại kết hợp 2 hàm này với nhau và cách kết hợp như thế nào.

Để hiểu được lý do của việc kết hợp vlookup + match, bạn hãy xem ví dụ ở hình sau.

Yêu Cầu:

Tìm và điền tên xe vào cột D dựa vào:

Tên hãng: Cột B
Phân khối: Cột C
Bảng tên loại xe: B16:E19

Xem hình ảnh bên dưới:

Cách sử dụng hàm thông thường

Để tìm được tên xem theo các điều kiện trên ta có thể dùng nhiều cách trong đó cách phổ biến mọi người dùng là:

HÀM VLOOKUP + IF

Cú pháp:

D3 = Vlookup (B3, $B$16:$E$19, if(C3 = 100, 2, if(C3 = 110, 3, 4)), 0)

Nhiều bạn sẽ hàm vlookup kết hợp if dài hơn cú pháp mà ad gợi ý ở trên.

CÂU HỎI:

Bạn có thể dùng hàm If để kết hợp với vlookup khi chỉ có vài cột.
Nhưng nếu có hàng trăm cột, thì dùng cách này liệu có ổn?

TRẢ LỜI:

Không ổn
Bởi lẽ nếu có 100 cột thì làm 99 lần hàm if cho việc tìm số thứ tự cột chứa giá trị cần tìm.
Độ dài của hàm khi đó sẽ vượt qua giới hạn cho phép của hàm trong excel.

GIẢI PHÁP:

Sử dụng hàm Vlookup kết hợp Match sẽ xử lý triệt để bài toán này.
Hàng trăm cột cũng không thành vấn đề.

Cùng là học về hàm Excel, nhưng khi hiểu được đặc tính và linh hoạt trong vận dụng thì bạn sẽ Thăng hoa cùng Excel. Hiệu quả công việc sẽ tăng lên rất nhiều.

CÁCH VIẾT HÀM VLOOKUP KẾT HỢP MATCH

Bây giờ Trường sẽ hướng dẫn các bạn dùng hàm VLookup kết hợp hàm Match giúp tìm tên xe.

CÚ PHÁP

D3 =VLOOKUP(B3, $B$16:$E$19, MATCH(C3,$B$16:$E$16,0), 0)

DIỄN GIẢI

Điều kiện tìm kiếm: B3 (Tên hãng)
Vùng tìm kiếm: B16:E19 (Bảng tên loại xe)
Số thứ tự cột chỉ định: Dùng hàm match để tìm dựa trên phân khối của từng xe.
Kiểu tìm kiếm: 0 (Chính xác)

MẤU CHỐT HÀM VLOOKUP KẾT HỢP MATCH

Trong hình trên, bạn thấy rằng thay vì điền trực tiếp số thứ tự cột chứa giá trị cần tìm vào hàm vlookup bằng số, ví dụ:

Phân khối 100: Cột chỉ định là 2
Phân khối 110: Cột chỉ định là 3
Còn lại là 125: Cột chỉ định là 4.

Như đã phân tích trước đó nếu có nhiều phân khối thì không thể điền tay được. Mà phải dùng hàm Match để tìm số thứ tự của từng phân khối trong bảng “tên loại xe”.

MẤU CHỐT

Hàm match là hàm tìm số thứ tự của 1 giá trị trong 1 dòng/ 1 cột
Số thứ tự của số phân khối từng xe chính là số thứ tự cột chứa tên cần tìm trong bảng tên xe.

HÀM VLOOKUP KẾT HỢP COLUMNS/ COLUMN

Khi hàm vlookup kết hợp columns/ Column bạn sẽ không còn lo việc thêm/ xóa cột trong vùng tìm kiếm ảnh hưởng tới kết quả của hàm.

YÊU CẦU:

Bạn xem hình dưới, yêu cầu cần làm là tìm phụ cấp 1 của nhân viên được nhập tại ô B10.

Thông thường ta sẽ làm như sau:

B11 =VLOOKUP(B10,$A$1:$F$6,5,0)

Hàm này thì quá dễ rồi, chắc là đa số mọi người làm được.

VẤN ĐỀ:

Nếu bạn chèn / xóa cột giữa 2 cột A và E thì điều gì xảy ra?

ĐIỀU KHÔNG HAY XẢY RA:

Khi bạn xóa/ thêm cột ở giữa 2 cột A và E thì số thứ tự cột giữa cột Mã Nhân viên và Phụ cấp 1 sẽ thay đổi.

Ví dụ:

Xóa cột địa chỉ thì cột E thành cột D: Số thứ cột của hàm vlookup cần phải đổi thành 4
Thêm cột Chức vụ sau cột địa chỉ thì cột E thành cột F: Số thứ cột của hàm vlookup cần đổi thành 6.

NHƯNG:

Số thứ tự cột là 5 ta đã viết trong hàm VLookup sẽ không tự động thay đổi thành 4 hay 6 khi ta xóa/ thêm cột.

Đây chính là mấu chốt của vấn đề.

Sử dụng VLOOKUP KẾT HỢP COLUMNS/ COLUMN

Trước khi đi vào giải pháp bạn cần hiểu rõ tính năng của 2 hàm kết hợp:

Hàm Column: Tìm số thứ tự cột bất kỳ tính từ cột A
Hàm Columns: Tìm số lượng cột của 1 vùng

Hàm VLOOKUP + COLUMN

Như bạn thấy trong hình trên, thay vì nhập số 5 vào phần số thứ tự cột chỉ định.

Trường đã dùng hàm Column(E1)
Khi đó kết quả của hàm Column(E1) sẽ trả về 5 = Số thứ tự cột E tính từ cột A
Và khi bạn thêm/ xóa cột giữa A và E thì kết quả của hàm Column sẽ thay đổi theo.
Tadaaa.
Bạn không cần phải ngồi sửa lại số 5 thành 4 hay 6 nữa vì lúc đó hàm Column đã làm giúp bạn.

HÀM VLOOKUP + COLUMNS

Mục đích của cách kết hợp Vlookup với Columns tương tự với Column.

Nhưng có sự khác biệt.

Với hàm Columns, bạn sẽ đếm được số lượng cột trong một vùng dữ liệu. Với các vùng tìm kiếm có cột điều kiện tìm kiếm khác cột A thì hàm Column sẽ không phát huy tác dụng cao.

Còn với hàm columns thì giải quyết việc này triệt để.

Bạn có thể thêm/ xóa cột giữa A và E hoặc
Bạn có thể thêm 1 hay nhiều cột trước cột Mã NV

Thì hàm Columns kết hợp vlookup vẫn tìm kiếm được phụ cấp một cách chính xác.

VLOOKUP 2 ĐIỀU KIỆN TRONG EXCEL

Cách dùng VLOOKUP 2 điều kiện rất Dễ làm & hiệu quả (áp dụng được cho nhiều điều kiện). Cách 1: Dùng cột phụ, Cách 2: Công thức mảng – chỉ cần 2 cách là đủ rồi. Trường sẽ giúp bạn biết cách làm trong nháy mắt.

Vlookup 2 điều kiện dùng cột phụ

Bạn có một danh sách sản lượng sản xuất cho từng sản phẩm, từng ca.

Làm thế nào để biết được sản lượng của 1 sản phẩm nào đó trong từng ca là bao nhiêu.

Để tìm kiếm dữ liệu trong excel ta thường dùng hàm Vlookup
Nhưng Vlookup thông thường chỉ tìm được theo 1 điều kiện

Và cách đơn giản nhất để tìm kiếm nhiều điều kiện trong excel là dùng cột phụ.

Cách dùng hàm vlookup 2 điều kiện trong excel như sau:

Tạo một cột phụ trước cột sản phẩm
Nhập Công thức tại cột phụ này = [Sản phẩm] & [Ca] = B2 & C2
Lưu ý: bạn cần copy công thức cho tất cả các dòng liên quan trong cột phụ
Viết hàm vlookup với Điều kiện tìm kiếm = [Sản phẩm] & [Ca]

Bạn tải file về và thử sẽ thấy rằng khi đổi giá trị Sản phẩm hoặc Ca thì ô Sản lượng sẽ thay đổi giá trị theo.Rất linh hoạt phải không nào các bạn. Hàm vlookup 2 điều kiện khi có cột phụ sẽ trở thành hàm vlookup 1 điều kiện

Bằng cách này, các bạn có thể tìm kiếm với nhiều điều kiện khác nhau khi dùng vlookup một cách dễ dàng.

Lỗi #N/A trong hàm vlookup

Lỗi #N/A là gì? (hay lỗi #N/A trong excel có ý nghĩa gì?) là câu hỏi của không ít bạn khi sử dụng hàm trong excel đặc biệt Lỗi #N/A trong hàm vlookup.

Định nghĩa Lỗi #N/A: là lỗi được trả về trong công thức excel khi không tìm thấy giá trị.

Trong hàm vlookup và hlookup, thường gặp lỗi #N/A khi không tìm thấy điều kiện tìm kiếm trong vùng điều kiện (cụ thể là cột đầu tiên của vùng điều kiện của hàm vlookup). Cùng xem Ví dụ về lỗi #N/A trong hàm vlookup và cách khắc phục

Gặp lỗi này khi vùng dữ liệu tìm kiếm không được chọn đầy đủ

Trong bức ảnh phía dưới là một ví dụ về việc công thức trả về lỗi #N/A trong hàm vlookup.

Bảng “Danh sách điểm thi”: Ta có danh sách điểm thi với Mã sinh viên và tương ứng với tên sinh viên.
Bảng #N/A: ta biết Mã sinh viên và phải dùng hàm vlookup để tìm tên sinh viên tương ứng với Mã sinh viên cho trước.

Với 2 Mã sinh viên đầu tiên K002 và K006 đều tìm được tên bởi trong danh sách Điểm thi (cột B) đều có chứa 2 mã này. Khi đó hàm vlookup sẽ tìm được tên tương ứng với 2 mã trên.

Tuy nhiên, với mã K009 – hàm vlookup trả về giá trị #N/A – Lỗi không tìm thấy giá trị. Do trong vùng tìm kiếm bạn nhập vào không có sinh viên nào có mã là K009.

Khi gặp trường hợp này bạn cần phải mở rộng vùng tìm kiếm ($B$4:$E$11) để bổ sung những sinh viên còn lại của lớp. Ví dụ như: $B$4:$E$16 – vùng dữ liệu này chứa cả mã sinh viên K009. (tham khảo hình dưới đây)

Gặp lỗi khi viết hàm, không cố định vùng tìm kiếm trước khi copy công thức

Lấy ví dụ ở hình trên

Bạn để ý thấy trong bảng “Không cố định vùng tìm kiếm”, khi ta không cố định vùng tìm kiếm thì khi copy công thức từ ô đầu tiên xuống cho các ô còn lại thì vùng tìm kiếm sẽ thay đổi theo.

Và như vậy vùng tìm kiếm không còn đúng nữa => Dẫn tới hàm vlookup không tìm được tên cho mã K003.

Và để giải quyết lỗi này, đơn giản:

Viết công thức cho ô đầu tiên.
Không quên cố định cả dòng và cột cho vùng tìm kiếm (Khi copy công thức thì vùng vẫn giữ nguyên)
Copy công thức xuống cho các ô còn lại.

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ề cách sử dụng hàm Vlookup trong excel và cách kết hợp hàm Vlookup với các hàm khác nhé. Hãy luôn học hỏi kiến thức và vận dụng các ưu điểm của mình để kết hợp nhiều hàm khác nhau nhé.