Ứng dụng hàm VLOOKUP trong Excel và những cách thức sử dụng nở rộ

Blog Siêu Marketing sẽ đưa ra hướng dẫn sử dụng hàm VLOOKUP trong Excel bằng lối diễn đạt đơn giản, nhằm đơn giản hóa quá trình làm quen với công cụ này cho những người mới bắt đầu. Ngoài ra, chúng ta cùng nhau tìm hiểu một số công thức điển hình để thấy cách thức sử dụng hàm VLOOKUP phổ biến nhất.

GIẢI THÍCH ĐƠN GIẢN VỀ HÀM VLOOKUP VÀ CẤU TRÚC CỦA NÓ TRONG EXCEL

Hàm VLOOKUP là gì? Điểm khởi đầu, đó là một chức năng trong Excel. Công việc của nó là gì? Nó tìm một giá trị bạn chỉ định và sau đó trả lại kết quả ở một cột đối diện. Để nói một cách chi tiết hơn, hàm VLOOKUP sẽ tìm kiếm giá trị trong cột đầu tiên của một dãy ô được xác định và trả về giá trị ở một ô tương ứng trong cùng hàng đó.

Trong việc sử dụng thông dụng, Excel dùng hàm VLOOKUP để duyệt qua danh sách dữ liệu bạn cung cấp dựa theo mã nhận dạng cụ thể và sau đó trả lại thông tin phù hợp với mã đó.

Chữ “V” trong VLOOKUP là viết tắt của “vertical” nghĩa là “dọc”. Chữ này giúp phân biệt giữa VLOOKUP và HLOOKUP, hàm tương tự tìm kiếm thông tin từ hàng đầu tiên của một mảng (chữ “H” đại diện cho “horizontal” tức là “ngang”).

Bạn có thể tìm thấy hàm VLOOKUP trong mọi phiên bản Excel, bao gồm Excel 2013, Excel 2010, Excel 2007, Excel XP và Excel 2000.

CẤU TRÚC CỦA HÀM VLOOKUP TRONG EXCEL

Cấu trúc tổng quát của hàm VLOOKUP trong Excel như sau:

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

Nếu bạn vẫn chưa hiểu rõ về tác dụng của hàm VLOOKUP và cách thức hoạt động của nó, hãy theo dõi bài viết “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 tiếp tục phần nội dung sau. Bài này sẽ giúp bạn hiểu sâu hơn về bản chất và cách ứng dụng nó vào công việc, không chỉ là nhìn qua lướt nhanh.

CÁC VÍ DỤ ĐIỂN HÌNH VỀ HÀM VLOOKUP TRONG EXCEL

Hy vọng là bạn đã bắt đầu quen thuộc với hàm VLOOKUP. Bây giờ, chúng ta sẽ xem xét một số ví dụ cụ thể về việc áp dụng công thức VLOOKUP để xử lý dữ liệu thực tế.

CÁCH ÁP DỤNG HÀM VLOOKUP TRONG EXCEL TỪ MỘT SHEET KHÁC

Thường thì trong thực tiễn, người ta ít khi sử dụng VLOOKUP để tìm kiếm thông tin trong cùng một sheet. Thay vào đó, họ thường hay phải truy xuất và thu thập dữ liệu từ một sheet khác.

Để sử dụng hàm VLOOKUP với sheet khác trong Excel, bạn cần ghi rõ tên của sheet đó và thêm dấu chấm than (!) vào trước dải ô cụ thể trong câu lệnh table_array, ví dụ như =VLOOKUP(40, Sheet2!A2:B15, 2). Công thức này báo rằng dải ô tìm kiếm A2:B15 nằm ở Sheet2.

Không nhất thiết phải điền tên sheet một cách thủ công. Bạn chỉ cần nhập công thức và khi bạn đến phần câu lệnh table_array, chỉ cần chuyển tới sheet bạn muốn rồi sử dụng chuột để lựa chọn dải ô cần thiết.

Công thức được minh họa trong ảnh dưới đây sẽ tìm từ “Product 1” (“Sản phẩm 1”) trong cột A (cột đầu tiên trong dải ô được tìm A2:B9) thuộc bảng tính có tên là Prices:

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

Hãy nhớ rằng giá trị chuỗi bạn cần tìm phải được bọc trong dấu ngoặc kép (“”) theo cách thông thường khi bạn làm việc với các công thức trong Excel.

Đây là một mẹo nhỏ: Sử dụng liên tục cấu trúc tham chiếu tuyệt đối cho phần tham số table_array trong công thức VLOOKUP là một lựa chọn thông minh. Nhờ đó, vùng dữ liệu được tham chiếu sẽ không thay đổi khi công thức được copy sang ô mới.

CÁCH THỰC HIỆN HÀM VLOOKUP GIỮA CÁC WORKBOOK KHÁC NHAU

Để dùng hàm VLOOKUP giữa các workbook khác nhau trong Excel, hãy đặt tên của workbook vào trong dấu ngoặc vuông trước tên sheet.

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

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

2)

Đây là phương pháp đơn giản nhất để bạn có thể tạo ra công thức VLOOKUP trong Excel tham chiếu tới một sổ làm việc khác:

Hãy mở cả hai tài liệu làm việc trên Excel. Mặc dù không hoàn toàn cần thiết, nhưng bước này giúp bạn dễ dàng hơn trong việc thiết lập công thức do không cần phải nhập tên sổ tay thủ công. Nó cũng giúp tránh các lỗi do sai lầm trong việc ghi lại tên.

Khi đang nhập công thức VLOOKUP, bạn chuyển qua sổ làm việc cần tham chiếu, sau đó chọn khoảng dữ liệu mà bạn muốn tìm kiếm ngay trong đó.

Tại ảnh chụp màn hình bên dưới, bạn có thể thấy rằng sổ làm việc để thực hiện tìm kiếm là PriceList.xlsx và worksheet cần tìm là Prices.

Khi sổ làm việc chứa dữ liệu tìm kiếm được đóng lại, công thức VLOOKUP sẽ tiếp tục hoạt động và hiển thị đường dẫn đầy đủ đến sổ làm việc tham chiếu, như mô tả trong ảnh dưới đây:

Lưu ý. Trong trường hợp tên sổ làm việc hoặc tên worksheet có chứa khoảng trắng, hãy đặt chúng trong dấu VLOOKUP(40, ‘[Numbers.xlsx]Sheet2’!A2:B15,2)

CÁCH SỬ DỤNG TÊN CHO KHOẢNG DỮ LIỆU HOẶC BẢNG TRONG CÔNG THỨC VLOOKUP

Nếu bạn cần áp dụng cùng một khoảng dữ liệu tìm kiếm cho nhiều công thức VLOOKUP khác nhau, việc tạo tên cho khoảng dữ liệu đó rồi nhập tên đó ngay vào công thức sẽ thay vì nhập phạm vi cần tìm kiếm có thể hữu ích.

Để tạo tên cho một khoảng dữ liệu, bạn chỉ cần chọn nó và nhập tên mong muốn vào ô Name box (Hộp tên) nằm ở bên trái thanh công cụ Formula.

Bây giờ, bạn có thể sử dụng công thức VLOOKUP dưới đây để tìm giá của Sản phẩm 1:

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

Do phần lớn tên cung cấp bởi Excel áp dụng cho toàn bộ sổ làm việc, không cần thiết phải chỉ rõ tên của worksheet trong phần nhập dữ liệu table_array, kể cả khi phạm vi dữ liệu bạn cần nằm trên một sheet khác. Nếu dữ liệu nằm trên một sổ làm việc khác, tên sổ làm việc cần đặt trước tên phạm vi dữ liệu, như ví dụ dưới đây:

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

Công thức như vậy trở nên dễ hiểu hơn nhiều, phải không? Hơn nữa, việc sử dụng tên cho khoảng dữ liệu cũng thuận lợi không kém so với việc sử dụng tham chiếu tuyệt đối. Nhờ vào tính năng tên dữ liệu không thay đổi khi công thức được sao chép sang các ô khác, bạn có thể yên tâm rằng sẽ luôn tìm kiếm đúng phạm vi dữ liệu.

Nếu bạn chuyển khoảng dữ liệu thành bảng Excel với các tính năng đầy đủ (Insert tab > Table), chỉ cần chọn khoảng dữ liệu đó, và Microsoft Excel sẽ tự động đặt tên cho các cột hoặc cả bảng (trong trường hợp bạn đã chọn toàn bộ bảng) cho công thức của bạn:

Công thức hoàn chỉnh có thể có dạng như sau:

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

hoặc thậm chí rất có thể trở thành: =VLOOKUP(“Product 1”, Table46,2)

KHI SỬ DỤNG KÝ TỰ ĐẶC BIỆT TRONG CÔNG THỨC VLOOKUP

Trong Excel, bạn có khả năng sử dụng ký tự đại diện cho hàm VLOOKUP:

Sử dụng dấu hỏi (?) thay thế cho một ký tự đơn lẻ bất kỳ, và

Dấu sao (*) để thay thế cho một chuỗi ký tự bất kỳ.

Ký tự đại diện có thể rất hữu dụng trong VLOOKUP trong nhiều tình huống:

Khi bạn quên mất chính xác cụm từ mình đang tìm kiếm.

Khi bạn muốn tìm một từ cụ thể nằm bên trong nội dung của một ô. Lưu ý rằng VLOOKUP tìm kiếm nội dung đầy đủ của một ô, như

Có thể bạn đã lựa chọn tùy chọn “Khớp toàn bộ nội dung ô” khi sử dụng chức năng Tìm kiếm chuẩn trong Excel.

Nếu trong cột bạn đang tìm kiếm mà có dấu cách ở đầu hoặc cuối dòng. Trong tình huống đó, có thể bạn phải đau đầu tìm hiểu nguyên nhân tại sao công thức thông dụng của mình không thực hiện được.

VÍ DỤ 1: TÌM KIẾM CHUỖI KÝ TỰ CÓ BẮT ĐẦU HOẶC KẾT THÚC BỞI KÝ TỰ ĐẶC BIỆT

Xét giả định rằng bạn đang cần tìm một khách hàng cụ thể trong cơ sở dữ liệu sau. Tên của người đó bạn không nhớ rõ, nhưng biết chắc chắn rằng nó bắt đầu với “ack”. Khi đó, công thức VLOOKUP sau sẽ hỗ trợ bạn hiệu quả:

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

Để tìm số tiền mà khách hàng đó thanh toán, bạn cũng có thể áp dụng công thức VLOOKUP giống trên nhưng thay đổi tham số thứ ba thành cột bạn muốn tìm, cụ thể ở đây là cột C (3):

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

Bên dưới là vài ví dụ khác về cách dùng hàm VLOOKUP với ký tự đại diện:

=VLOOKUP(“*man”,$A$2:$C$11,1,FALSE) – để tìm tên có phần cuối là “man”.

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

=VLOOKUP(“?????”,$A$2:$C$11,1,FALSE) – để tìm tên có độ dài là năm ký tự.

Lưu ý rằng để VLOOKUP với ký tự đại diện hoạt động chính xác, bạn cần luôn thêm FALSE làm tham số cuối. Nếu dải ô tìm kiếm của bạn có nhiều hơn một giá trị trùng khớp, kết quả đầu tiên phù hợp sẽ được trả về.

VÍ DỤ 2: CÔNG THỨC VLOOKUP VỚI KÝ TỰ ĐẠI DIỆN ĐỂ TÌM KIẾM GIÁ TRỊ BẤT KỲ TRONG Ô

Giờ chúng ta cùng xét tới một ví dụ phức tạp hơn – việc tìm giá trị từ một ô cụ thể. Giả sử bạn có mã bản quyền ở cột A, tên bản quyền ở cột B. Bạn có một phần của mã bản quyền trong ô C1 và bạn muốn tìm tên bản quyền liên quan.

Cách giải quyết này có thể tận dụng công thức VLOOKUP như sau:

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

Công thức này sẽ tìm giá trị trong ô C1 bằng cách quét qua dải ô đã xác định, sau đó trả về giá trị ở cột B tương ứng. Chú ý, chúng ta sử dụng ký hiệu nối chuỗi (&) trước và sau biến tham chiếu ô trong thông số đầu tiên.

Bức ảnh chụp màn hình dưới đây cho thấy hàm VLOOKUP của chúng ta trả về “Jeremy Hill” vì mã bản quyền của anh ấy chứa chuỗi ký tự giống như trong ô C1:

Ngoài ra, cần chú ý đến đoạn tự table_array trong bức ảnh. Nó sử dụng tên bảng (“Bảng 7”) thay vì dải ô như đã nêu trong ví dụ trước.

SỨ MỆNH CỦA HÀM VLOOKUP VỚI GIÁ TRỊ TÌM KIẾM CỤ THỂ VÀ GIÁ TRỊ TÌM KIẾM XẤP XỈ

Cuối cùng, hãy xem xét kỹ lưỡng lại tham số cuối cùng bạn nhập vào hàm VLOOKUP của Excel – range_lookup. Như đã đề cập đầu bài, tham số này rất quan trọng vì tùy vào việc nhập TRUE hay FALSE, kết quả trả về có thể khác biệt.

Trước tiên, chúng ta hãy hiểu rõ “giá trị tìm kiếm cụ thể” và “giá trị tìm kiếm xấp xỉ” có nghĩa là gì trong Microsoft Excel.

Nếu tham số range_lookup được thiết lập là FALSE, thì công thức sẽ tìm đúng giá trị cần tìm một cách cụ thể, chẳng hạn nó sẽ tìm chỉ giá trị nào mà bạn nhập rõ ràng làm thông số đầu tiên (lookup_value).

Khi có hơn một giá trị khớp với thông số tìm kiếm trong cột đầu tiên của table_array, kết quả phù hợp đầu tiên sẽ được lấy. Trong trường hợp không tìm thấy giá trị chính xác nào, lỗi #N/A sẽ được trả lại.

Ví dụ, nếu bạn dùng công thứcTrong trường hợp bạn sử dụng công thức =VLOOKUP(4, A2:B15,2,FALSE) và trong khoảng từ ô A2 tới A15 không có giá trị 4, kết quả sẽ là lỗi #N/A.

Trường hợp range_lookup được thiết lập thành TRUE hoặc bị bỏ qua, hàm sẽ tìm kiếm giá trị phù hợp một cách gần đúng. Điều này nghĩa là nếu không tìm thấy giá trị chính xác, VLOOKUP sẽ tìm giá trị phù hợp gần đúng nhất và lớn hơn giá trị cần tìm.

Điều cần ghi nhớ! Khi sử dụng TRUE hoặc không sử dụng range_lookup, các giá trị trong cột đầu tiên của vùng ô cần được sắp xếp theo thứ tự tăng dần từ thấp đến cao. Nếu không, hàm VLOOKUP có thể sẽ không hoạt động chính xác.

Thử thêm một số công thức VLOOKUP khác để xem sự khác biệt khi sử dụng TRUEFALSE.

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

Lưu ý rằng, để tìm giá trị chính xác, bạn cần đặt FALSE ở cuối công thức VLOOKUP trong Excel.

Chúng ta sẽ lấy bảng “Animal speed” làm ví dụ và tìm loài vật có tốc độ 80,5 km một giờ. Công thức =VLOOKUP(50, $A$2:$B$15, 2, FALSE) là dễ dàng để sử dụng:

Cần chú ý là trong cột A chúng ta có hai giá trị 50 tại A5 và A6, nhưng kết quả sẽ trả về giá trị thấy đầu tiên tại A5. Điều này xảy ra vì hàm VLOOKUP với giá trị chính xác sẽ trả về giá trị khớp đầu tiên.

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

Khi dùng VLOOKUP với giá trị tìm kiếm tương đối, tức là range_lookup được thiết lập thành TRUE hay bị loại bỏ, yêu cầu đầu tiên là bạn phải sắp xếp cột đầu tiên theo thứ tự tăng dần.

Điều này quyết định việc VLOOKUP sẽ trả về giá trị phù hợp lớn hơn và chấm dứt tìm kiếm. Nếu không sắp xếp đúng, bạn có thể nhận được kết quả là một số lạ hoặc lỗi #N/A.

Và giờ, hai công thức có thể sử dụng là:

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

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

Nhằm mục đích tìm loài vật có tốc độ gần với 111 km một giờ nhất và đây là kết quả:

Kết quả cho “Antelope” với tốc độ 98 km/giờ, trong khi có loài báo chạy với tốc độ 113 km/giờ. Lý do là VLOOKUP tìm giá trị gần đúng lớn nhất nhưng nhỏ hơn giá trị cần tìm.

Hy vọng những ví dụ trên đã giúp bạn hiểu rõ hơn về cách dùng Excel và hàm VLOOKUP.

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

VLOOKUP không thể tìm các giá trị bên trái, chỉ tìm trong cột liền kề bên trái.

Các giá trị trong công thức VLOOKUP không quan trọng về chữ hoa hay thường.

Nếu giá trị tìm kiếm nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên

Khi sử dụng hàm VLOOKUP mà không thấy giá trị mong muốn trong phạm vi được quy định, hàm này sẽ hiển thị lỗi #N/A.

Lỗi #VALUE! sẽ xuất hiện nếu giá trị của tham số thứ ba (col_index_num) trong hàm VLOOKUP là nhỏ hơn 1. Nếu tham số này lại lớn hơn số lượng cột có trong phạm vi dữ liệu cần tra cứu (table_array), hàm sẽ cho ra lỗi #REF!.

Khi sử dụng tham chiếu ô tuyệt đối cho tham số table_array trong hàm VLOOKUP, bạn sẽ đảm bảo phạm vi cần tìm kiếm luôn được duy trì chính xác qua các lần xử lý công thức. Cách khác, đặt tên cho phạm vi cần tra cứu hoặc bảng trong Excel có thể là một lựa chọn thay thế tốt.

Đảm bảo rằng bạn đã sắp xếp cột đầu tiên của dải ô cần tìm kiếm theo thứ tự từ thấp đến cao nếu bạn chọn tìm kiếm phù hợp tương đối (range_lookup được thiết lập thành TRUE hoặc bỏ qua).

Đừng quên tầm quan trọng của thông số cuối cùng trong hàm VLOOKUP. Chọn TRUE hoặc FALSE một cách phù hợp để tránh nhức đầu.

Việc nắm vững các hàm và biết cách sử dụng hiệu quả các công cụ của Excel sẽ giúp chúng ta ứng dụng tốt chương trình này vào công việc. Hãy học cách sử dụng các hàm cao cấp như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… và các công cụ như Data validation, Conditional formatting, Pivot table… để tăng hiệu quả công việc.

Bạn có thể học hỏi tất cả những kiến thức này thông qua các khóa học.

Xem thêm: Hướng dẫn cách áp dụng hàm vlookup với nhiều điều kiện – kèm theo ví dụ minh họa cụ thể

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop