Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp

Trong bài hướng dẫn về hàm VLOOKUP này, Blog Học Excel Online sẽ cố gắng dùng từ ngữ đơn giản để giải thích những điều cơ bản nhằm giúp quá trình học cho người mới bắt đầu trở nên dễ dàng nhất có thể. Chúng ta cũng sẽ khám phá vài công thức mẫu cho thấy cách dùng thông dụng nhất của hàm VLOOKUP trong Excel.

HÀM VLOOKUP TRONG EXCEL – BẢN MIÊU TẢ ĐƠN GIẢN VÀ CẤU TRÚC

Vậy hàm VLOOKUP là gì? Để bắt đầu, nó là một hàm trong Excel. ? Nó dùng để làm gì? Nó tìm kiếm giá trị mà bạn định rõ và trả về giá trị tương ứng ở một cột khác. Nói một cách kỹ thuật hơn, hàm VLOOKUP tìm kiếm giá trị từ cột đầu tiên trong trong dải ô xác định, rồi trả về giá trị tương ứng trong cùng một hàng từ một ô khác.

Ở cách dùng thông dụng nhất, hàm VLOOKUP trong Excel tìm kiếm thông qua danh sách dữ liệu bạn tạo ra dựa trên ký hiệu nhận dạng đặc biệt rồi trả về cho bạn một mẩu thông tin tương ứng với ký hiệu nhận dạng đặc biệt đó.

Ký tự “V” trong hàm VLOOKUP là viết tắt của từ “vertical” (“thẳng đứng”). Ký tự đó được dùng để phân biệt hàm VLOOKUP và hàm HLOOKUP – đây là hàm tìm kiếm giá trị từ hàng trên cùng của mảng (H là viết tắt của từ “horizontal” (“nằm ngang”)).

Hàm VLOOKUP có sẵn trong tất cả phiên bản, gồm Excel 2013, Excel 2010, Excel 2007, Excel 2007, Excel XP và Excel 2000.

CÚ PHÁP HÀM VLOOKUP TRONG EXCEL

Cú pháp hàm VLOOKUP trong Excel có dạng như sau:

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

Nếu bạn chưa nắm rõ các ý nghĩa công thức của hàm Vlookup và cách thức chúng hoạt động thì hãy đọc bài viết này trước Hàm Vlookup trong Excel và các hướng dẫn chi tiết qua các ví dụ dễ hiểu trước khi đọc phần nội dung phía đưới bạn nhé! Nếu bạn muốn đọc để hiểu bản chất và ứng dụng vào công việc chứ không phải “cưỡi ngựa xem hoa”

Xem thêm: Các hàm excel cơ bản

VÍ DỤ HÀM VLOOKUP TRONG EXCEL

Tôi hy vọng rằng bạn đã quen dần với hàm VLOOKUP. Bây giờ, hãy khám phá vài ví dụ sử dụng công thức VLOOKUP cho dữ liệu thực.

CÁCH DÙNG HÀM VLOOKUP TRONG EXCEL TỪ MỘT BẢNG TÍNH KHÁC

Trong thực tiễn, công thức VLOOKUP rất hiếm khi được dùng để tìm kiếm dữ liệu trong cùng một bảng tính. Thông thường, bạn sẽ phải tìm kiếm và kéo dữ liệu tương ứng về từ bảng tính khác.

Để dùng hàm VLOOKUP từ bảng tính Excel khác, bạn nên nhập tên bảng tính và một dấu cảm thán trong câu lệnh table_array trước dải ô, ví dụ =VLOOKUP(40, Sheet2!A2:B15, 2). Công thức này chỉ ra rằng dải ô tìm kiếm A2:B15 nằm ở Sheet2.

Tất nhiên, bạn không cần phải nhập tên bảng tính một cách thủ công. Chỉ cần bắt đầu gõ công thức và khi gõ đến câu lệnh table_array, hãy đổi sang bảng tính cần tìm và dùng chuột để chọn dải ô.

Công thức bạn thấy trong ảnh chụp màn hình dưới đây tìm kiếm chuỗi ký tự “Product 1” (“Sản phẩm 1”) trong cột A (cột đầu tiên trong dải ô cần tìm A2:B9) ở bảng tính Giá tiền:

=VLOOKUP(“Product 1”, Prices!$A$2:$B$9, 2, FALSE)

Xin hãy chú ý rằng bạn phải đặt giá trị chuỗi ký tự cần tìm trong dấu ngoặc kép (“”) như cách bạn thường làm trong các công thức Excel.

Mẹo. Luôn luôn dùng tham chiếu ô tuyệt đối (có $) trong thông số table_array của công thức VLOOKUP là một ý tưởng tuyệt vời. Trong trường hợp này, dải ô cần tìm sẽ không thay đổi khi bạn sao chép công thức sang ô khác.

CÁCH DÙNG HÀM VLOOKUP TỪ MỘT SỔ LÀM VIỆC KHÁC

Để dùng hàm VLOOKUP giữa hai sổ làm việc Excel khác nhau, bạn nên đặt tên sổ làm việc trong dấu ngoặc vuông trước tên bảng tính.

Ví dụ, công thức dưới đây sẽ tìm kiếm giá trị “40” trong Sheet2 của sổ làm việc Numbers.xlsx:

=VLOOKUP(40, [Numbers.xlsx]Sheet2!A2:B15, 2)

Sau đây là cách dễ nhất để tạo công thức VLOOKUP trong Excel tham chiếu đến sổ làm việc khác:

Mở cả hai sổ làm việc. Bước này không bắt buộc, nhưng nó sẽ giúp việc lập công thức dễ dàng hơn bởi vì bạn sẽ không phải gõ tên sổ làm việc một cách thủ công. Bên cạnh đó, điều đó cũng sẽ bảo vệ công thức của bạn khỏi lỗi vô tình in sai.

Bắt đầu nhập công thức VLOOKUP, rồi đối với câu lệnh table_array, thì hãy đổi sang sổ làm việc khác rồi chọn dải ô cần tìm kiếm ở đó.

Ở công thức bạn thấy trong ảnh chụp màn hình dưới đây, sổ làm việc cần tìm là PriceList.xlsx và bảng tinh cần tìm là Prices.

Một khi bạn đóng sổ làm việc có bảng cần tìm thì công thức VLOOKUP của bạn sẽ vẫn chạy, nhưng nó sẽ hiển thị đầy đủ đường dẫn tới sổ làm việc cần tìm, như được diễn giải dưới đây:

Lưu ý. Nếu hoặc tên sổ làm việc hoặc tên bảng tính có khoảng trống, thì bạn nên đặt chúng trong dấu =VLOOKUP(40, ‘[Numbers.xlsx]Sheet2’!A2:B15,2)

Đừng bỏ lỡ: Tài liệu tự học Excel 2003

CÁCH SỬ DỤNG TÊN CHO DẢI Ô HAY BẢNG TRONG CÔNG THỨC VLOOKUP

Giả sử, nếu phải sử dụng cùng một dải ô cần tìm trong vài công thức VLOOKUP, thì bạn có thể tạo tên cho một dải ô rồi nhập trực tiếp tên của dải ô vào công thức thay vì phải nhập vùng cần tìm (câu lệnh table_array).

Để tạo tên cho dải ô, bạn chỉ cần chọn dải ô đó và gõ bất kỳ tên nào vào Name box (Hộp tên) – ở bên trái thanh Công thức.

Và bây giờ bạn có thể viết công thức VLOOKUP dưới đây để tìm giá của Sản phẩm 1:

=VLOOKUP(“Product 1”, Products, 2)

Hầu hết tên dải ô trong Excel đều được sử dụng cho toàn bộ sổ làm việc, nên bạn không cần định rõ tên của bảng tính trong câu lệnh table_array, dù cho dải ô bạn cần tìm nằm ở bảng tính khác. Nếu nó nằm ở sổ làm việc khác, thì bạn phải đặt tên sổ làm việc phía trước tên dải ô, ví dụ:

=VLOOKUP(“Product 1”, PriceList.xlsx!Products,2)

Những công thức như thế này này dễ hiểu hơn rất nhiều, đúng không? Bên cạnh đó, việc sử dụng tên cho dải ô có thể là một sự thay thế tuyệt vời cho tham chiếu ô tuyệt đối. Bởi vì tên dải ô không đổi khi sao chép công thức sang các ô khác, nên bạn có thể chắc rằng bạn sẽ luôn tìm đúng dải ô.

Nếu bạn đã đổi dải ô thành bảng Excel có đầy đủ tính năng (Insert tab > Table), thì bạn có thể dùng chuột chọn dải ô, và Microsoft Excel sẽ tự động thêm tên các cột (hay tên bảng trong trường hợp bạn đã chọn toàn bộ bảng) vào công thức:

Công thức đầy đủ có thể trông như thế này:

=VLOOKUP(“Product 1”, Table46[[Product]:[Price]],2)

hay thậm chí là có dạng như thế này: =VLOOKUP(“Product 1”, Table46,2)

VIỆC SỬ DỤNG KÝ TỰ ĐẠI DIỆN TRONG CÔNG THỨC VLOOKUP

Cũng như các công thức khác, bạn có thể sử dụng ký tự đại diện cho hàm VLOOKUP trong Excel:

Dấu chấm hỏi (?) để khớp với bất cứ ký tự riêng lẻ nào, và

Dấu sao (*) để khớp với bất kỳ dãy ký tự nào.

Việc sử dụng ký tự đại diện trong công thức VLOOKUP có thể rất hữu ích trong nhiều trường hợp:

Khi bạn không nhớ chính xác chuỗi ký tự bạn đang tìm kiếm.

Khi bạn muốn tìm một từ nào đó. Từ đó là một phần của nội dung ô. Hãy chú ý rằng hàm VLOOKUP tìm kiếm toàn bộ nội dung ô, như thể bạn đã chọn “Match the entire content” (“Khớp toàn bộ nội dung ô”) trong tương tác Tìm kiếm chuẩn của Excel.

Khi cột cần tìm có khoảng trống thụt đầu dòng hay cuối dòng. Nếu gặp trường hợp này, thì bạn có thể vắt óc cố tìm ra lý do tại sao công thức thông thường của bạn lại không được thực hiện.

VÍ DỤ 1. TÌM KIẾM CHUỖI KÝ TỰ BẮT ĐẦU HAY KẾT THÚC BẰNG CÁC KÝ TỰ NHẤT ĐỊNH

Giả sử, bạn muốn tìm một khách hàng nhất định trong cơ sở dữ liệu dưới đây. Bạn không thể nhớ tên của anh ấy, nhưng bạn biết rằng nó bắt đầu bằng “ack”. Vậy thì, hàm VLOOKUP dưới đây sẽ giúp bạn rất nhiều đấy:

=VLOOKUP(“ack*”,$A$2:$C$11,1,FALSE)

Một khi bạn chắc mình đã tìm đúng tên rồi, bạn có thể sử dụng công thức VLOOKUP tương tự để tìm số tiền người khách hàng ấy đã trả. Bạn chỉ cần thay đổi thông số thứ ba trong công thức thành số thứ cột mà bạn cần tìm, đó là cột C (3) trong trường hợp này:

=VLOOKUP(“ack*”,$A$2:$C$11,3,FALSE)

Sau đây là thêm vài ví dụ về việc sử dụng hàm VLOOKUP có ký tự đại diện:

=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE) – tìm tên kết thúc bằng “man”.

=VLOOKUP(“ad*son”,$A$2:$C$11,1,FALSE) – tìm tên bắt đầu bằng “ad” và kết thúc bằng “son”.

=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE) – tìm họ có năm ký tự.

Lưu ý. Để công thức VLOOKUP có ký tự đại diện chạy chính xác, bạn phải luôn thêm FALSE như một thông số cuối cùng. Nếu dải ô cần tìm của bạn có hơn một mục nhập khớp với tiêu chuẩn đại diện, thì giá trị tìm thấy đầu tiên sẽ được trả về.

VÍ DỤ 2. CÔNG THỨC VLOOKUP CÓ KÝ TỰ ĐẠI DIỆN DỰA TRÊN GIÁ TRỊ Ô

Bây giờ, hãy thảo luận thêm về một ví dụ phức tạp hơn – đó là làm thế nào để tìm kiếm giá trị ở một ô nào đó. Giả sử, bạn có mã bản quyền ỏ cột A và tên bản quyền ở cột B. Bạn cũng có một phần (vài ký tự) của một mã bản quyền nào đó ở cột C1 và bạn muốn tìm Tên Bản quyền tương ứng.

Điều này có thể được giải quyết bằng cách dùng hàm VLOOKUP có dạng như sau:

=VLOOKUP(“*”&C1&”*”, $A$2:$B$12, 2, FALSE)

Công thức này tìm kiếm giá trị ở ô C1 thông qua một dải ô xác định và trả về giá trị tương ứng ở cột B. Xin hãy chú ý rằng chúng ta sử dụng dấu và (&) trước và sau một tham chiếu ô trong thông số đầu tiên nhằm nối chuỗi ký tự.

Như bạn có thể thấy trong ảnh chụp màn hình bên dưới, hàm VLOOKUP của tôi trả về “Jeremy Hill” bởi vì mã bản quyền của anh ấy có một nhóm ký tự nằm trong ô C1:

Đồng thời, xin hãy chú ý câu lệnh table_array trong ảnh chụp màn hình trên. Nó có tên bảng (“Bảng 7”) thay vì dải ô, điều này chúng ta đã bàn ở ví dụ trên.

VIỆC SỬ DỤNG CÔNG THỨC VLOOKUP CÓ GIÁ TRỊ TÌM KIẾM CHÍNH XÁC VÀ GIÁ TRỊ TÌM KIẾM TƯƠNG ĐỐI

Và cuối cùng, hãy xem xét kỹ câu lệnh cuối cùng mà bạn nhập vào hàm VLOOKUP trong Excel – range_lookup. Vì đã được nói đến ở đầu bài hướng dẫn, câu lệnh này cực kỳ quan trọng bởi vì bạn có thể nhận được kết quả khác nhau tùy vào bạn nhập TRUE hay FALSE.

Đầu tiên, hãy xem “giá trị tìm kiếm chính xác” và “giá trị tìm kiếm tương đối” trong Microsoft Excel thật sự có nghĩa gì.

Nếu range_lookup được cài đặt là FALSE, thì công thức sẽ tìm chính xác giá trị, ví dụ, công thức sẽ tìm chính xác giá trị cần tìm khi bạn nhập giá trị đó làm thông số thứ nhất (lookup_value).

Nếu có hai hay nhiều hơn hai giá trị từ cột đầu tiên của table_array khớp với giá trị cần tìm, thì kết quả tìm thấy đầu tiên sẽ được trả về. Nếu không thể tìm thấy giá trị tìm kiếm chính xác, thì lỗi #N/A sẽ được trả về.

Ví dụ, nếu bạn sử dụng công thức =VLOOKUP(4, A2:B15,2,FALSE), nhưng dữ liệu của bạn không chứa giá trị 4 từ ô A2 đến cột A15, thì công thức sẽ trả về lỗi #N/A.

Nếu range_lookup được cài đặt thành TRUE hay được loại bỏ, thì công thức sẽ tìm sự phù hợp tương đối. Nói chính xác hơn, công thức VLOOKUP của bạn sẽ tìm kiếm sự phù hợp tuyệt đối đầu tiên và nếu sự phù hợp tuyệt đối không được tìm thấy, thì nó sẽ trả về sự phù hợp tương đối. Gía trị tìm kiếm tương đối là giá trị lớn nhất tiếp theo, chỉ nhỏ hơn lookup_value.

Lưu ý quan trọng! Nếu bạn nhập TRUE hay loại bỏ câu lệnh range_lookup, thì các giá trị ở cột đầu tiên của dải ô cần tìm phải được sắp xếp theo thứ tự tăng dần, đó là từ nhỏ nhất đến lớn nhất. Nếu không thì, hàm VLOOKUP trong Excel có thể sẽ không tìm được giá trị chính xác.

Để biết thêm tầm quan trọng của việc định rõ TRUE và FALSE, hãy thử thêm vài công thức VLOOKUP và xem kết quả chúng trả về nhé.

VÍ DỤ 1. CÁCH SỬ DỤNG HÀM VLOOKUP CÓ GIÁ TRỊ TÌM KIẾM CHÍNH XÁC TRONG EXCEL

Có thể bạn còn nhớ, để tìm kiếm sự phù hợp tuyệt đối, bạn phải đặt FALSE làm câu lệnh cuối cùng trong công thức VLOOKUP trong Excel.

Hãy chọn bảng “Animal speed” để làm ví dụ đầu tiên và tìm ra loài vật nào có thể chạy 80,5 km một giờ. Tôi tin chắc bạn sẽ chẳng gặp khó khăn gì với công thức này:

=VLOOKUP(50, $A$2:$B$15, 2, FALSE).

Hãy lưu ý rằng, dải ô cần tìm của chúng ta (cột A) chứa hai giá trị 50 lần lượt trong cột A5 và A6; và công thức trả về giá trị trong cột A5. Tại sao lại như thế? Bởi vì hàm VLOOKUP có giá trị tìm kiếm tuyệt đối sẽ trả về giá trị tìm thấy đầu tiên – giá trị khớp với giá trị cần tìm.

Lớp học Excel tại Hà Nội

VÍ DỤ 2. SỬ DỤNG HÀM VLOOKUP CÓ GIÁ TRỊ CẦN TÌM TƯƠNG ĐỐI TRONG EXCEL

Khi sử dụng công thức VLOOKUP có giá trị tìm kiếm tương đối, đó là có range_lookup được cài đặt thành TRUE hay được bỏ đi, thì điều đầu tiên bạn cần làm đó là lọc cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.

Điều này rất quan trọng bởi vì công thức VLOOKUP sẽ trả về giá trị lớn nhất tiếp theo cho giá trị cần tìm mà bạn đã định rõ rồi sẽ ngừng tìm kiếm. Nếu bạn không lọc dữ liệu một cách chính xác, thì cuối cùng bạn sẽ nhận được một kết quả lạ hay lỗi #N/A.

Và bây giờ, bạn có thể sử dụng một trong hai công thức này:

=VLOOKUP(69, $A$2:$B$15, 2, TRUE)

=VLOOKUP(69,$A$2:$B$15,2)

Như bạn có thể thấy, tôi đang cố tìm loài vật có tốc độ chạy gần 111 km một giờ nhất. Và đây là kết quả mà công thức VLOOKUP trả về:

Như bạn có thể thấy, công thức trả về “Antelope” (“Linh dương”) – nó có tốc độ chạy 98 km trên giờ, trong khi đó, chúng ta cũng có báo chạy với tốc độ 113 km trên giờ, và 70 thì gần với 69 hơn 61 , đúng không nào? Vậy, tại sao lại như thế? Bởi vì hàm VLOOKUP có giá trị tìm kiếm tương đối sẽ trả về giá trị lớn nhất tiếp theo, và giá trị này nhỏ hơn giá trị cần tìm.

Hy vọng rằng, những ví dụ này đã giúp việc sử dụng Excel dễ dàng hơn và hàm này không còn xa lạ với bạn nữa ? Bây giờ, sẽ rất tuyệt để tổng kết những điều quan trọng mà bạn đã học nhằm giúp bạn nhớ các điểm mấu chốt tốt hơn.

HÀM VLOOKUP TRONG EXCEL – NHỮNG ĐIỀU CẦN NHỚ!

Hàm VLOOKUP trong Excel không thể tìm ở bên trái. Nó luôn luôn tìm giá trị nằm trong cột cận trái của dải ô cần tìm (table_array).

Trong công thức VLOOKUP, tất cả giá trị đều không phân biệt dạng chữ, có nghĩa là ký tự viết hoa và viết thường đều được xử lý như nhau.

Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của dải ô cần tìm, thì hàm VLOOKUP sẽ trả về lỗi #N/A.

Nếu câu lệnh thứ ba (col_index_num) nhỏ hơn 1, thì công thức VLOOKUP sẽ trả về lỗi #VALUE!. Trong trường hợp, nó lớn hơn số cột trong dải ô cần tìm (table_array), thì công thức sẽ trả về lỗi #REF!.

Hãy sử dụng tham chiếu ô tuyệt đối trong câu lệnh table_array của công thức VLOOKUP để có đúng dải ô cần tìm khi xử lý công thức. Hãy cân nhắc việc sử dụng tên cho dải ô hay cho bảng trong Excel như một phương án thay thế.

Khi tìm kiếm sự phù hợp tương đối (range_lookup được cải đặt thành TRUE hay loại bỏ), hãy luôn lọc dữ liệu ở cột đầu tiên trong dải ô cần tìm theo thứ tự tăng dần.

Và cuối cùng, hãy nhớ tầm quan trọng của thông số cuối cùng. Sử dụng TRUE hay FALSE khi thích hợp và bạn sẽ không phải đau đầu nữa.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học

Xem thêm: Cách sử dụng hàm vlookup nhiều điều kiện – có ví dụ cụ thể minh họa