Table of Contents
Là một người dùng Excel thì không thể nào không biết được VLOOKUP là hàm gì. Đây là một hàm vô cùng tiện dụng, hữu ích và được sử dụng nhiều nhất trong Excel. Trong bài viết dưới đây, GhienCongNghe sẽ hướng dẫn thật chi tiết cách sử dụng hàm VLOOKUP một cách dễ dàng và hiệu quả nhất cũng như cách áp dụng nó trong tất cả các trường hợp khác nhau.
VLOOKUP là hàm gì?
VLOOKUP là hàm gì? – Đó là hàm dùng để tìm kiếm giá trị bạn chỉ định và trả về một giá trị phù hợp từ một cột khác. Chi tiết hơn, hàm VLOOKUP tìm kiếm một giá trị trong cột đầu tiên của một phạm vi nhất định và trả về một giá trị trong cùng một hàng từ một cột khác.
Trong cách sử dụng phổ biến, hàm VLOOKUP trong Excel tìm kiếm thông qua tập dữ liệu của bạn dựa trên số nhận dạng duy nhất và mang đến cho bạn một phần thông tin được liên kết với số nhận dạng duy nhất đó.
Chữ cái “V” là viết tắt của “vertical” và được sử dụng để phân biệt hàm VLOOKUP với hàm HLOOKUP tìm kiếm một giá trị trong một hàng chứ không phải cột (H là viết tắt của “horizontal”).
Hàm có sẵn trong tất cả các phiên bản Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 và các phiên bản thấp hơn.
Cú pháp hàm VLOOKUP
Cú pháp của hàm VLOOKUP như sau:
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Như bạn thấy, hàm có 4 đối số – ba đối số đầu tiên được yêu cầu và đối số cuối cùng là tùy chọn.
- Lookup_value là giá trị cần tìm kiếm.Đây có thể là một giá trị (số, ngày tháng hoặc văn bản), tham chiếu ô (tham chiếu đến ô chứa giá trị tra cứu) hoặc giá trị được trả về bởi một số hàm khác. Không giống như số và tham chiếu ô, giá trị văn bản phải luôn được đặt trong “dấu ngoặc kép”.
Xem ví dụ sau:
=VLOOKUP(“lion”, A2:B6, 2, FALSE)
Lúc này, công thức dùng để tra cứu từ “lion” - Table_array là hai hoặc nhiều cột dữ liệu.
Hàm VLOOKUP tìm kiếm giá trị tra cứu trong cột đầu tiên của mảng bảng, có thể chứa nhiều giá trị văn bản, số, ngày tháng và giá trị logic khác nhau. Vì vậy, bây giờ bạn có thể đọc thêm một chút công thức ở trên: tìm kiếm “lion” trong phạm vi A2: A6 (vì A là cột đầu tiên trong mảng bảng của chúng tôi). - Col_index_num là số cột mà từ đó giá trị sẽ được trả về.
Việc đếm bắt đầu từ cột ngoài cùng bên trái trong mảng bảng, là 1.
Có nghĩa là, công thức mẫu sẽ trả về một giá trị khớp từ cột B, giá trị này đứng thứ 2 trong mảng bảng. - Range_lookup (tùy chọn) – xác định xem nên tìm kiếm đối sánh gần đúng hay đối sánh chính xác:
TRUE hoặc bị bỏ qua (mặc định) – đối sánh gần đúng. Nếu không tìm thấy kết quả phù hợp chính xác, công thức sẽ tìm kiếm kết quả phù hợp gần nhất, tức là giá trị lớn nhất nhỏ hơn giá trị tra cứu. Yêu cầu sắp xếp cột tra cứu theo thứ tự tăng dần.
FALSE – kết hợp chính xác. Công thức tìm kiếm một giá trị chính xác bằng giá trị tra cứu. Nếu không tìm thấy kết quả khớp chính xác, giá trị #N/A sẽ được trả về.
Ở trên đã giới thiệu xong các đối số, giờ đây bạn sẽ không gặp vấn đề gì khi đọc toàn bộ công thức: tìm kiếm “lion” trong A2: A6, tìm một kết quả khớp chính xác và trả về một giá trị từ cột B trong cùng một hàng:
=VLOOKUP(“lion”, A2:B6, 2, FALSE)
Để thuận tiện, bạn có thể nhập giá trị quan tâm vào một số ô, chẳng hạn E1, thay thế văn bản “mã cứng” bằng tham chiếu ô và nhận công thức để tra cứu bất kỳ giá trị nào bạn nhập vào E1:
=VLOOKUP(E1, A2:B6, 2, FALSE)
Nếu bạn vẫn chưa rõ ràng lắm thì thử xem cách dưới đây:
Cách thực hiện VLOOKUP trong Excel
Khi sử dụng công thức VLOOKUP trong trang tính đời thực, nguyên tắc chung là: khóa mảng bảng với các tham chiếu ô tuyệt đối (như $A$2:$C$11) để ngăn nó thay đổi khi sao chép công thức sang các ô khác.
Các giá trị tra cứu trong nhiều trường hợp phải là một tài liệu tham khảo liên quan (như E2) hoặc bạn có thể khóa phối hợp chỉ cột ($E2). Khi công thức được sao chép xuống cột, tham chiếu sẽ tự động điều chỉnh cho mỗi hàng.
Để cho bạn dễ hình dung, theo dõi vidu sau với bảng mẫu được bổ sung thêm một cột đứng động vật bằng tốc độ (cột A) để tìm hạng 1, hạng 5 và hạng 10 động vật chạy nước rút nhanh nhất trên thế giới. Đối với điều này, hãy nhập thứ hạng tra cứu vào một số ô (E2: E4 trong ảnh chụp màn hình bên dưới) và sử dụng các công thức sau:
Để kéo tên động vật từ cột B:
=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)
Để trích xuất tốc độ từ cột C:
=VLOOKUP($E2, $A$2:$C$11, 3, FALSE)
Nhập các công thức trên vào các ô F2 và G2, chọn các ô đó và kéo các công thức vào các hàng bên dưới:
Nếu bạn điều tra công thức ở hàng dưới, bạn sẽ nhận thấy rằng tham chiếu giá trị tra cứu đã được điều chỉnh cho hàng cụ thể đó, trong khi mảng bảng bị khóa:
Dưới đây, bạn sẽ có thêm một số mẹo hữu ích, giúp bạn tiết kiệm rất nhiều thời gian đau đầu và khắc phục sự cố.
Hàm VLOOKUP trong Excel – 5 điều cần nhớ!
- Hàm VLOOKUP không thể nhìn sang bên trái của nó. Nó luôn tìm kiếm ở cột ngoài cùng bên trái của mảng bảng và trả về một giá trị từ cột bên phải. Nếu bạn cần kéo các giá trị từ bên trái, hãy sử dụng kết hợp INDEX MATCH có thể không quan tâm đến vị trí của các cột tra cứu và trả về.
- Hàm VLOOKUP không phân biệt chữ hoa chữ thường, nghĩa là các ký tự viết hoa và viết thường được coi là tương đương nhau. Để phân biệt chữ cái, hãy sử dụng công thức VLOOKUP phân biệt chữ hoa chữ thường.
- Hãy nhớ về tầm quan trọng của tham số cuối cùng. Sử dụng TRUE cho đối sánh gần đúng và FALSE cho đối sánh chính xác. Để biết chi tiết đầy đủ, vui lòng xem hàm VLOOKUP TRUE so với FALSE.
- Khi tìm kiếm đối sánh gần đúng, hãy đảm bảo dữ liệu trong cột tra cứu được sắp xếp theo thứ tự tăng dần.
- Nếu không tìm thấy giá trị tra cứu, sẽ trả về lỗi #N/A. Để biết thông tin về các lỗi khác, hãy tìm hiểu xem vì sao Excel VLOOKUP không hoạt động.
Cách VLOOKUP từ một trang tính khác trong Excel
Trong thực tế, hàm VLOOKUP trong Excel hiếm khi được sử dụng với dữ liệu trong cùng một trang tính. Thông thường, bạn sẽ phải lấy dữ liệu phù hợp từ một trang tính khác.
Để VLOOKUP từ một trang tính Excel khác, hãy đặt tên của trang tính theo sau là dấu chấm than trong đối số table_array trước tham chiếu phạm vi. Ví dụ: để tìm kiếm trong phạm vi A2: B10 trên Sheet2, hãy sử dụng công thức sau:
=VLOOKUP(“Product1”, Sheet2!A2:B10, 2)
Tất nhiên, bạn không phải nhập tên trang tính theo cách thủ công. Chỉ cần bạn nhập công thức và khi nói đến đối số table_array, hãy chuyển sang trang tính tra cứu và chọn phạm vi bằng chuột.
Ví dụ: đây là cách bạn có thể tra cứu giá trị A2 trong phạm vi A2: A9 trên trang tính Prices và trả về giá trị phù hợp từ cột C:
=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE)
Ghi chú:
- Nếu tên bảng tính chứa khoảng trắng hoặc các ký tự không phải chữ cái, nó phải được đặt trong dấu ngoặc kép, ví dụ: ‘Price list’!$A$2:$C$9.
- Trong trường hợp bạn sử dụng công thức VLOOKUP cho nhiều ô, hãy nhớ khóa table_array bằng dấu $, như $A$2:$C$9.
Cách VLOOKUP từ một sổ làm việc khác trong Excel
Để VLOOKUP từ một sổ làm việc Excel khác, hãy đặt tên cửa sổ làm việc trong dấu ngoặc vuông trước tên của trang tính.
Ví dụ: đây là công thức để tra cứu giá trị A2 trên trang tính có tên Prices trong sổ làm việc Price_List.xlsx :
=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)
Nếu tên sổ làm việc hoặc tên trang tính chứa khoảng trắng hoặc các ký tự không phải chữ cái, bạn nên đặt chúng trong dấu ngoặc kép như sau:
=VLOOKUP(A2, ‘[Price List.xlsx]Prices’!$A$2:$C$9, 3, FALSE)
Cách dễ nhất để tạo công thức VLOOKUP tham chiếu đến một sổ làm việc khác là:
- Mở cả hai tệp.
- Bắt đầu nhập công thức của bạn, chuyển sang sổ làm việc khác và chọn mảng bảng bằng chuột.
- Nhập các đối số còn lại và nhấn phím Enter để hoàn thành công thức của bạn.
Kết quả sẽ giống như ảnh chụp màn hình bên dưới:
Khi bạn đóng tệp bằng bảng tra cứu của mình, công thức VLOOKUP sẽ tiếp tục hoạt động, nhưng bây giờ nó sẽ hiển thị đường dẫn đầy đủ cho sổ làm việc đã đóng:
Cách VLOOKUP từ một dải ô đã đặt tên trong một trang tính khác
Trong trường hợp bạn định sử dụng cùng một dải ô tra cứu trong nhiều công thức, bạn có thể tạo một dải ô đã đặt tên cho nó và nhập tên trực tiếp vào đối số table_array.
Để tạo một phạm vi đã đặt tên, chỉ cần chọn các ô và nhập tên bạn muốn vào hộp Tên ở bên trái của thanh Công thức.
Đối với ví dụ này, chúng tôi đã đặt tên Prices_2020 cho các ô dữ liệu (A2: C9) trong bảng tra cứu và nhận được công thức nhỏ gọn này:
=VLOOKUP(A2, Prices_2020, 3, FALSE)
Hầu hết các tên trong Excel áp dụng cho toàn bộ sổ làm việc, vì vậy bạn không cần chỉ định tên của trang tính khi sử dụng phạm vi đã đặt tên.
Nếu phạm vi được đặt tên nằm trong một sổ làm việc khác, hãy đặt tên của sổ làm việc trước tên phạm vi, ví dụ:
=VLOOKUP(A2, ‘Price List.xlsx’!Prices_2020, 3, FALSE)
Bên cạnh đó, sử dụng các phạm vi đã đặt tên có thể là một thay thế tốt cho các tham chiếu tuyệt đối. Vì một phạm vi đã đặt tên không thay đổi, bạn có thể chắc chắn rằng mảng bảng của bạn sẽ vẫn bị khóa cho dù công thức được di chuyển hoặc sao chép ở đâu.
Nếu bạn đã chuyển đổi phạm vi tra cứu của mình thành một bảng Excel đầy đủ chức năng, thì bạn có thể thực hiện VLOOKUP dựa trên tên bảng, ví dụ: Price_table trong công thức dưới đây:
=VLOOKUP(A2, Price_table, 3, FALSE)
Tham chiếu bảng, còn được gọi là tham chiếu có cấu trúc , có khả năng phục hồi và miễn nhiễm với nhiều thao tác dữ liệu. Ví dụ: bạn có thể xóa hoặc thêm hàng mới vào bảng tra cứu của mình mà không cần lo lắng về việc cập nhật các tham chiếu.
Sử dụng ký tự đại diện trong công thức VLOOKUP
Giống như nhiều công thức khác, hàm VLOOKUP trong Excel chấp nhận các ký tự đại diện sau:
- Dấu hỏi (?) Để phù hợp với bất kỳ ký tự đơn nào.
- Dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự nào.
Các ký tự đại diện thực sự hữu ích trong nhiều trường hợp:
- Khi bạn không nhớ chính xác văn bản bạn đang tìm kiếm.
- Khi bạn đang tìm kiếm một chuỗi văn bản là một phần của nội dung ô.
- Khi một cột tra cứu chứa khoảng trống ở đầu hoặc cuối . Trong trường hợp đó, bạn có thể cố gắng tìm ra lý do tại sao một công thức bình thường không hoạt động.
Các ví dụ minh họa
Ví dụ 1. Tra cứu văn bản bắt đầu hoặc 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 nào đó trong cơ sở dữ liệu bên dưới. Bạn không nhớ họ, nhưng bạn tin chắc rằng nó bắt đầu bằng “ack”.
Để trả lại họ từ cột A, hãy sử dụng công thức ký tự đại diện VLOOKUP sau:
=VLOOKUP(“ack*”, $A$2:$B$10, 1, FALSE)
Để truy xuất khóa cấp phép từ cột B, hãy sử dụng khóa này (sự khác biệt chỉ nằm ở số chỉ mục của cột):
=VLOOKUP(“ack*”, $A$2:$B$10, 2, FALSE)
Bạn cũng có thể nhập phần đã biết của tên vào một số ô, chẳng hạn như E1, và kết hợp ký tự đại diện với tham chiếu ô:
=VLOOKUP(E1&”*”, $A$2:$B$10, 1, FALSE)
Ảnh chụp màn hình dưới đây cho thấy kết quả:
Dưới đây là một số công thức VLOOKUP khác với các ký tự đại diện.
Tìm họ kết thúc bằng “son”:
=VLOOKUP(“*son”, $A$2:$B$10, 1, FALSE)
Lấy tên bắt đầu bằng “joh” và kết thúc bằng “son”:
=VLOOKUP(“joh*son”, $A$2:$B$10, 1, FALSE)
Kéo một họ gồm 5 ký tự:
=VLOOKUP(“?????”, $A$2:$B$10, 1, FALSE)
Ví dụ 2. Ký tự đại diện VLOOKUP dựa trên giá trị ô
Từ ví dụ trước, bạn đã biết rằng có thể nối ký hiệu “và” (&) và tham chiếu ô để tạo chuỗi tra cứu. Để tìm một giá trị chứa (các) ký tự đã cho ở bất kỳ vị trí nào, hãy đặt dấu “và” trước và sau tham chiếu ô.
Giả sử, bạn muốn có một tên tương ứng với một khóa cấp phép nhất định, nhưng bạn không biết toàn bộ khóa, chỉ có một vài ký tự. Với các khóa trong cột A, tên trong cột B và một phần của khóa đích trong E1, bạn có thể thực hiện một VLOOKUP ký tự đại diện theo cách này:
Giải nén khóa:
=VLOOKUP(“*”&E1&”*”, $A$2:$B$10, 1, FALSE)
Trích xuất tên:
=VLOOKUP(“*”&E1&”*”, $A$2:$B$10, 2, FALSE)
Ghi chú:
- Để công thức VLOOKUP ký tự đại diện hoạt động chính xác, hãy sử dụng kết hợp chính xác (FALSE là đối số cuối cùng).
- Nếu tìm thấy nhiều hơn một kết quả phù hợp, kết quả đầu tiên sẽ được trả về.
VLOOKUP TRUE so với FALSE
Và bây giờ, đã đến lúc xem xét kỹ hơn đối số cuối cùng của hàm VLOOKUP trong Excel. Mặc dù là tùy chọn, nhưng tham số range_lookup rất quan trọng. Tùy thuộc vào việc bạn chọn TRUE hoặc FALSE, công thức của bạn có thể mang lại các kết quả khác nhau.
Đối sánh chính xác hàm VLOOKUP trong Excel (FALSE)
Nếu range_lookup được đặt thành FALSE, công thức VLOOKUP sẽ tìm kiếm một giá trị chính xác bằng giá trị tra cứu. Nếu tìm thấy hai hoặc nhiều kết quả phù hợp, kết quả đầu tiên được trả về. Nếu không tìm thấy kết quả khớp chính xác, lỗi #N/A sẽ xảy ra.
Đối sánh gần đúng hàm VLOOKUP trong Excel (TRUE)
Nếu range_lookup được đặt thành TRUE hoặc bị bỏ qua (mặc định), công thức sẽ tìm kiếm kết quả phù hợp nhất. Chính xác hơn, nó sẽ tìm kiếm một kết hợp chính xác trước tiên và nếu không tìm thấy một kết hợp chính xác, nó sẽ tìm kiếm giá trị lớn nhất tiếp theo nhỏ hơn giá trị tra cứu.
Đối sánh gần đúng VLOOKUP hoạt động với những lưu ý sau:
- Cột tra cứu phải được sắp xếp theo thứ tự tăng dần , từ nhỏ nhất đến lớn nhất, nếu không có thể không tìm thấy giá trị chính xác.
- Nếu giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng tra cứu, thì sẽ trả về lỗi #N/A.
Các ví dụ sau đây sẽ giúp bạn hiểu rõ hơn về sự khác biệt giữa đối sánh chính xác và đối sánh gần đúng trong VLOOKUP và khi nào mỗi công thức được sử dụng tốt nhất.
Ví dụ 1. Cách thực hiện đối sánh chính xác VLOOKUP
Để tra cứu kết quả khớp chính xác, chỉ cần đặt FALSE trong đối số cuối cùng.
Trong ví dụ này, chúng ta hãy bảng tốc độ động vật, trao đổi các cột, và cố gắng tìm những con vật có thể chạy 80, 50 và 30 dặm một giờ. Với các giá trị tra cứu trong D2, D3 và D4, hãy nhập công thức dưới đây vào E2, sau đó sao chép nó xuống hai ô khác:
=VLOOKUP(D2, $A$2:$B$12, 2, FALSE)
Như bạn có thể thấy, công thức trả về “Lion” trong E3 vì chúng chạy chính xác 50 mỗi giờ. Đối với hai giá trị tra cứu khác, không tìm thấy kết quả khớp chính xác và lỗi #N/A xuất hiện.
Ví dụ 2. Làm thế nào để VLOOKUP đối sánh gần đúng
Để tra cứu kết quả so khớp gần đúng, có hai điều cần thiết bạn cần làm:
- Sắp xếp cột đầu tiên của table_array từ nhỏ nhất đến lớn nhất.
- Sử dụng TRUE cho đối số range_lookup hoặc bỏ qua nó.
Việc sắp xếp cột tra cứu là rất quan trọng vì hàm VLOOKUP ngừng tìm kiếm ngay khi tìm thấy kết quả gần khớp nhỏ hơn giá trị tra cứu. Nếu dữ liệu không được sắp xếp đúng cách, bạn có thể nhận được kết quả lạ hoặc một loạt lỗi #N/A.
Đối với dữ liệu mẫu của chúng tôi, công thức VLOOKUP đối sánh gần đúng như sau:
=VLOOKUP(D2, $A$2:$B$12, 2, TRUE)
Và trả về các kết quả sau:
- Đối với giá trị tra cứu là “80”, “Cheetah” được trả về vì tốc độ của nó (70) là giá trị khớp gần nhất nhỏ hơn giá trị tra cứu.
- Đối với giá trị tra cứu là “50”, một kết quả khớp chính xác được trả về (Sư tử).
- Đối với giá trị tra cứu là “30”, lỗi #N/A được trả về vì giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong cột tra cứu.
Các công cụ đặc biệt để VLOOKUP trong Excel
Hàm VLOOKUP là một trong những hàm Excel thông dụng và hữu ích nhất, nhưng nó cũng là một trong những hàm khó hiểu nhất. Học cách sử dụng nó dễ dàng và trải nghiệm thú vị hơn, thử sử dụng một số công cụ tiết kiệm thời gian trong Ultimate Suite dành cho Excel.
VLOOKUP Wizard – cách dễ dàng để viết các công thức phức tạp
Trình VLOOKUP Wizard tương tác sẽ hướng dẫn bạn qua các tùy chọn cấu hình để xây dựng một công thức hoàn hảo cho các tiêu chí bạn chỉ định. Tùy thuộc vào cấu trúc dữ liệu của bạn, nó sẽ sử dụng hàm VLOOKUP tiêu chuẩn hoặc công thức INDEX MATCH có thể kéo các giá trị từ trái sang.
Để có được công thức tùy chỉnh của bạn, đây là những gì bạn cần làm:
- Chạy trình VLOOKUP Wizard.
- Chọn bảng chính và bảng tra cứu của bạn.
- Chỉ định các cột sau (trong nhiều trường hợp, chúng được chọn tự động):
- Key column – cột trong bảng chính của bạn chứa các giá trị cần tra cứu.
- Lookup column – cột cần tra cứu.
- Return column – cột mà từ đó lấy các giá trị.
- Nhấp vào nút Insert.
Các ví dụ sau đây cho thấy trình hướng dẫn đang hoạt động.
VLOOKUP tiêu chuẩn
Khi cột tra cứu (Animal) là cột ngoài cùng bên trái trong bảng tra cứu, một công thức VLOOKUP thông thường cho đối sánh chính xác sẽ được chèn:
VLOOKUP bên trái
Khi cột tra cứu (Animal) ở bên phải của cột trả về (Speed), trình hướng dẫn sẽ chèn một công thức INDEX MATCH vào VLOOKUP từ phải sang trái:
Bổ sung thêm: Do việc sử dụng thông minh các tham chiếu ô , các công thức có thể được sao chép hoặc di chuyển đến bất kỳ cột nào mà bạn không cần phải cập nhật các tham chiếu.
Merge Two Tables – thay thế dạng không cần công thức cho hàm VLOOKUP trong Excel
Nếu các tệp Excel của bạn quá lớn và phức tạp, thời hạn của dự án sắp xảy ra và bạn đang tìm người có thể giúp bạn một tay, hãy dùng thử Merge Two Tables.
Công cụ này là sự thay thế trực quan và không gây căng thẳng của chúng tôi cho hàm VLOOKUP của Excel, hoạt động theo cách này:
- Chọn bảng chính của bạn.
- Chọn bảng tra cứu.
- Chọn một hoặc một số cột chung làm (các) số nhận dạng duy nhất.
- Chỉ định những cột nào cần cập nhật.
- Theo tùy chọn, chọn các cột để thêm.
- Cho phép Merge Two Tables vài giây để xử lý… và tận hưởng kết quả 🙂
Xem thêm:
Vậy là GhienCongNghe đã giới thiệu cho bạn VLOOKUP là hàm gì một cách rất chi tiết phải không nào. Nếu còn có bất kỳ thắc mắc nào cần được giải đáp thì hãy Comment bên dưới nhé. Đừng quên Like và Share nếu bạn thấy bài viết này có ích và đó cũng là động lực cho GhienCongNghe đem đến những nội dung bổ ích hơn trong tương lai.
Tham khảo Ablebits
