Hướng Dẫn Sử Dụng Hàm VLOOKUP Nâng Cao Trong Excel

VLOOKUP là hàm mạnh mẽ trong Excel giúp tra cứu dữ liệu. Bài viết này sẽ hướng dẫn bạn cách sử dụng VLOOKUP nâng cao, kết hợp với các hàm khác để tra cứu theo nhiều điều kiện, tra cứu hai chiều, và nhiều thủ thuật hữu ích khác.

VLOOKUP Với Nhiều Tiêu Chí

Hàm VLOOKUP hữu ích khi tìm kiếm giá trị trong cơ sở dữ liệu, nhưng chỉ giới hạn tra cứu một giá trị. Vậy nếu muốn tìm kiếm theo nhiều điều kiện?

Tra Cứu 2 Tiêu Chí Khác Nhau

Ví dụ, bạn muốn tìm số lượng (Qty) dựa trên tên (Name) và sản phẩm (Product) trong danh sách đơn hàng. Mỗi khách hàng có thể đặt nhiều sản phẩm.

Dữ liệu đơn hàng với tên khách hàng và sản phẩm

VLOOKUP thông thường không hiệu quả vì nó trả về giá trị đầu tiên tìm thấy. Giải pháp là tạo cột phụ kết hợp các tiêu chí. Cột này phải nằm ngoài cùng bên trái trong phạm vi tra cứu.

Bạn có thể dùng công thức =B2&C2 hoặc =B2&" "&C2 (thêm khoảng trắng để dễ đọc).

Sau đó, dùng công thức VLOOKUP: =VLOOKUP("Jeremy HillSweets",$A$5:$C$14,3,FALSE) hoặc =VLOOKUP(B1,$A$5:$C$14,3,FALSE) (B1 chứa giá trị tra cứu, 3 là số cột chứa dữ liệu cần tìm).

Công thức VLOOKUP tra cứu theo 2 tiêu chí

VLOOKUP Với 2 Tiêu Chí Từ Bảng Tính Khác

Nếu cần cập nhật dữ liệu từ bảng tính khác, bạn có thể nối giá trị tra cứu trực tiếp vào công thức. Tạo cột phụ kết hợp các tiêu chí ở ngoài cùng bên trái trong bảng tra cứu.

Công thức VLOOKUP: =VLOOKUP(B2&" "&C2,Orders!$A$2:$B$2,4,FALSE) (B và C chứa tên khách hàng và sản phẩm, Orders!$A$2:$B$2 là bảng tra cứu).

Đặt tên cho bảng tra cứu để công thức dễ đọc hơn: =VLOOKUP(B2&" "&C2,Orders,4,FALSE).

Lưu ý: Giá trị tra cứu trong công thức phải khớp với giá trị trong cột tra cứu. VLOOKUP giới hạn 255 ký tự.

Lấy Nhiều Giá Trị Phù Hợp Với VLOOKUP

VLOOKUP chỉ trả về giá trị phù hợp đầu tiên. Để lấy nhiều giá trị, bạn có thể thêm cột phụ với số thứ tự. Ví dụ, =B2&COUNTIF($B$2:B2,B2) (tên khách hàng ở cột B).

Thêm số thứ tự vào tên khách hàng

Sau đó, dùng VLOOKUP: =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE) (tìm sản phẩm thứ hai của Dan Brown).

VLOOKUP tìm sản phẩm thứ hai

Công thức phức tạp hơn cho giá trị phù hợp thứ hai (không cần cột phụ): =IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"").

Để lấy tất cả giá trị phù hợp, dùng hàm INDEX (xem phần sau).

Lấy Tất Cả Giá Trị Trùng Lặp

Để lấy tất cả giá trị trùng lặp, dùng công thức mảng: {=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}.

Nhập công thức vào nhiều ô, nhấn Ctrl + Shift + Enter.

Lấy tất cả giá trị trùng lặp

Tra Cứu Hai Chiều Trong Excel

Tra cứu hai chiều tìm kiếm giá trị tại giao điểm của hàng và cột.

VLOOKUP & MATCH

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE) (tìm số lemons bán trong tháng 3).

SUMPRODUCT

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

INDEX & MATCH

=INDEX($A$2:$I$9, MATCH("Lemons",$A$2:$A$9,0), MATCH("Mar",$A$1:$I$1,0))

Dải Được Đặt Tên & SPACE (Toán Tử Giao Nhau)

1. Chọn bảng, vào Formulas > Create from Selection.
2. Nhập =Lemons Mar hoặc =Mar Lemons (dùng khoảng trắng làm toán tử giao nhau).

Kết quả tra cứu hai chiều:

VLOOKUP Lồng Nhau

Khi bảng chính và bảng tra cứu không có cột chung, bạn có thể dùng VLOOKUP lồng nhau.

VLOOKUP lồng nhau

1. =VLOOKUP(A2,New_SKU,2,FALSE) (tìm tên sản phẩm trong “Lookup table 1” dựa trên New SKU).
2. =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE) (tìm giá trong “Lookup table 2” dựa trên tên sản phẩm).

Kết quả VLOOKUP lồng nhau

VLOOKUP Nâng Cao, Trả Về Nhiều Giá Trị

Có thể kết hợp các hàm để tạo hàm tổng hợp trả về nhiều giá trị. Sử dụng tổ hợp phím CTRL + SHIFT + ENTER để nhập công thức mảng.

VLOOKUP và INDIRECT Kéo Dữ Liệu Từ Nhiều Trang Tính

Khi dữ liệu cùng định dạng nằm trên nhiều trang tính, bạn có thể dùng VLOOKUP và INDIRECT.

VLOOKUP và INDIRECT

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE) ($D$2 chứa tên sản phẩm, $D3 chứa tên khu vực, “_Sales” là phần chung của tên bảng).

INDIRECT & VLOOKUP – Cách Hoạt Động

Hàm INDIRECT tham chiếu gián tiếp đến ô. Đặt tên cho các bảng với phần chung (ví dụ: “_Sales”).

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE) (nếu dữ liệu ở bảng tính khác).

Kết quả VLOOKUP và INDIRECT

Lưu ý: Bảng tính chứa dữ liệu phải được mở khi sử dụng INDIRECT.