Hàm VLOOKUP là một trong những hàm phổ biến nhất trong Excel. Hướng dẫn này sẽ giới thiệu cách sử dụng hàm VLOOKUP trong Excel với hàng chục ví dụ cơ bản và nâng cao theo từng bước.

Giới thiệu hàm VLOOKUP - Cú pháp và Đối số

Trong Excel, hàm VLOOKUP là một hàm mạnh mẽ đối với hầu hết người dùng Excel, nó cho phép bạn tìm kiếm một giá trị ở ngoài cùng bên trái của phạm vi dữ liệu và trả về một giá trị phù hợp trong cùng một hàng từ một cột mà bạn đã chỉ định như ảnh chụp màn hình sau đây được hiển thị .

Cú pháp của hàm VLOOKUP:

đối số:

Ghi chú chức năng:

Các ví dụ cơ bản về VLOOKUP

Trong phần này, chúng ta sẽ nói về một số công thức Vlookup mà bạn thường sử dụng.

2.1 Đối sánh chính xác và đối sánh gần đúng VLOOKUP

 2.1.1 So khớp chính xác VLOOKUP

Thông thường, nếu bạn đang tìm kiếm một đối sánh chính xác với hàm VLOOKUP, bạn chỉ cần sử dụng FALSE làm đối số cuối cùng.

Ví dụ, để nhận được điểm Toán tương ứng dựa trên số ID cụ thể, vui lòng thực hiện như sau:

Hãy sao chép và dán công thức dưới đây vào một ô trống (ở đây tôi chọn G2) và nhấn đăng ký hạng mục thi phím để nhận kết quả:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Công thức VLOOKUP hoạt động như thế nào?

Đầu tiên, nó tìm ID - C1005 ở cột ngoài cùng bên trái của bảng. Nó đi từ trên xuống dưới và tìm giá trị trong ô A6.

Ngay khi tìm thấy giá trị, nó sẽ chuyển sang bên phải trong cột thứ ba và trích xuất giá trị trong đó.

Vì vậy, bạn sẽ nhận được kết quả như ảnh chụp màn hình bên dưới:

 2.1.2 So khớp gần đúng VLOOKUP

Đối sánh gần đúng hữu ích cho việc tìm kiếm các giá trị giữa các phạm vi dữ liệu. Nếu không tìm thấy kết quả khớp chính xác, hàm VLOOKUP gần đúng sẽ trả về giá trị lớn nhất nhỏ hơn giá trị tra cứu.

Ví dụ: nếu bạn có dải dữ liệu sau và các đơn đặt hàng được chỉ định không có trong cột Đơn hàng, làm cách nào để có được Giảm giá gần nhất trong cột B?

Bước 1: Áp dụng công thức VLOOKUP và điền vào các ô khác

Sao chép và dán công thức sau vào một ô mà bạn muốn đặt kết quả, sau đó kéo bộ điều khiển điền xuống để áp dụng công thức này cho các ô khác.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Kết quả:

Bây giờ, bạn sẽ nhận được kết quả khớp gần đúng dựa trên các giá trị đã cho, xem ảnh chụp màn hình:

2.2 Thực hiện VLOOKUP phân biệt chữ hoa chữ thường trong Excel

Theo mặc định, hàm VLOOKUP thực hiện tra cứu không phân biệt chữ hoa chữ thường, nghĩa là nó coi các ký tự chữ thường và chữ hoa giống nhau. Đôi khi, bạn có thể cần thực hiện tra cứu phân biệt chữ hoa chữ thường trong Excel, hàm VLOOKUP bình thường có thể không giải quyết được. Trong trường hợp này, bạn có thể sử dụng các hàm thay thế như INDEX và MATCH với hàm EXACT hoặc hàm LOOKUP và EXACT.

Ví dụ: tôi có dải dữ liệu sau mà cột ID chứa chuỗi văn bản với chữ hoa hoặc chữ thường, bây giờ, tôi muốn trả về điểm Toán tương ứng của số ID đã cho.

Bước 1: Áp dụng bất kỳ một công thức nào và điền nó vào các ô khác

Vui lòng sao chép và dán bất kỳ một trong các công thức dưới đây vào ô trống nơi bạn muốn nhận kết quả. Sau đó, chọn ô công thức, kéo núm điều khiển điền xuống các ô mà bạn muốn điền công thức này.

Công thức 1: Sau khi dán công thức, vui lòng nhấn Ctrl + Shift + Enter phím.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Công thức 2: Sau khi dán công thức, vui lòng nhấn đăng ký hạng mục thi Chìa khóa.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Kết quả:

Sau đó, bạn sẽ nhận được kết quả chính xác mà bạn cần. Xem ảnh chụp màn hình:

2.3 Giá trị VLOOKUP từ phải sang trái trong Excel

Hàm VLOOKUP luôn tìm kiếm một giá trị trong cột ngoài cùng bên trái của một dải dữ liệu và trả về giá trị tương ứng từ cột bên phải. Nếu bạn muốn thực hiện một hàm VLOOKUP đảo ngược, nghĩa là tra cứu một giá trị cụ thể ở cột bên phải và trả về giá trị tương ứng ở cột bên trái như ảnh chụp màn hình bên dưới:

Nhấp để biết chi tiết từng bước về nhiệm vụ này…

2.4 VLOOKUP giá trị khớp thứ hai, thứ n hoặc cuối cùng trong Excel

Thông thường, nếu tìm thấy nhiều giá trị trùng khớp khi sử dụng hàm Vlookup, thì chỉ bản ghi khớp đầu tiên sẽ được trả về. Trong phần này, tôi sẽ nói về cách lấy giá trị khớp thứ hai, thứ n hoặc cuối cùng trong một dải dữ liệu.

 2.4.1 VLOOKUP và trả về giá trị khớp thứ hai hoặc thứ n

Giả sử bạn có một danh sách tên ở cột A, khóa đào tạo họ đã mua ở cột B. Bây giờ, bạn đang muốn tìm khóa đào tạo thứ 2 hoặc thứ n được mua bởi khách hàng nhất định. Xem ảnh chụp màn hình:

Ở đây, hàm VLOOKUP có thể không trực tiếp giải quyết tác vụ này. Tuy nhiên, bạn có thể sử dụng hàm INDEX để thay thế.

Bước 1: Áp dụng và điền công thức cho các ô khác

Chẳng hạn, để lấy giá trị khớp thứ hai dựa trên tiêu chí đã cho, vui lòng áp dụng công thức sau vào một ô trống và nhấn Ctrl + Shift + Enter keys với nhau để có được kết quả đầu tiên. Sau đó, chọn ô công thức, kéo núm điều khiển điền xuống các ô mà bạn muốn điền công thức này.

=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))

Kết quả:

Bây giờ, tất cả các giá trị khớp thứ hai dựa trên các tên đã cho đã được hiển thị cùng một lúc.

 2.4.2 VLOOKUP và trả về giá trị khớp cuối cùng

Nếu bạn muốn vlookup và trả về giá trị khớp cuối cùng như hình ảnh chụp màn hình bên dưới, điều này VLOOKUP và trả về giá trị khớp cuối cùng hướng dẫn có thể giúp bạn nhận được giá trị phù hợp cuối cùng một cách chi tiết.

2.5 Các giá trị so khớp VLOOKUP giữa hai giá trị hoặc ngày đã cho

Đôi khi, bạn có thể muốn tra cứu các giá trị giữa hai giá trị hoặc ngày và trả về kết quả tương ứng như trong ảnh chụp màn hình bên dưới. Trong trường hợp này, bạn có thể sử dụng hàm LOOKUP thay vì hàm VLOOKUP với một bảng đã được sắp xếp.

 2.5.1 VLOOKUP so khớp các giá trị giữa hai giá trị hoặc ngày đã cho với công thức

Bước 1: Sắp xếp dữ liệu và áp dụng công thức sau

Bảng ban đầu của bạn phải là một phạm vi dữ liệu được sắp xếp. Sau đó, sao chép hoặc nhập công thức sau vào một ô trống. Sau đó, kéo núm điều khiển điền để điền công thức này vào các ô khác mà bạn cần.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Kết quả:

Và bây giờ, bạn sẽ nhận được tất cả các bản ghi phù hợp dựa trên giá trị đã cho, xem ảnh chụp màn hình:

 2.5.2 VLOOKUP khớp các giá trị giữa hai giá trị hoặc ngày đã cho với một tính năng tiện dụng

Nếu bạn cảm thấy khó nhớ và khó hiểu công thức trên, thì sau đây, tôi sẽ giới thiệu một công cụ dễ dàng – Kutools cho Excel, Với khả NHÌN giữa hai giá trị tính năng, bạn có thể trả lại mục tương ứng dựa trên giá trị hoặc ngày cụ thể giữa hai giá trị hoặc ngày một cách dễ dàng.

1. Nhấp chuột Kutools > SIÊU NHÌN > NHÌN giữa hai giá trị để kích hoạt tính năng này.
2. Sau đó chỉ định các hoạt động từ hộp thoại dựa trên dữ liệu của bạn.

2.6 Sử dụng ký tự đại diện để khớp từng phần trong hàm VLOOKUP

Trong Excel, các ký tự đại diện có thể được sử dụng trong hàm VLOOKUP, cho phép bạn thực hiện khớp một phần giá trị tra cứu. Chẳng hạn, bạn có thể sử dụng VLOOKUP để trả về giá trị phù hợp từ một bảng dựa trên một phần của giá trị tra cứu.

Giả sử, tôi có một loạt dữ liệu như ảnh chụp màn hình bên dưới, bây giờ, tôi muốn trích xuất điểm số dựa trên tên (không phải tên đầy đủ). Làm cách nào để giải quyết công việc này trong Excel?

Bước 1: Áp dụng và điền công thức cho các ô khác

Vui lòng sao chép hoặc nhập công thức sau vào một ô trống, sau đó, kéo núm điều khiển điền để điền công thức này vào các ô khác mà bạn cần:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Kết quả:

Và tất cả các điểm phù hợp đã được trả lại như ảnh chụp màn hình bên dưới:

2.7 Các giá trị VLOOKUP từ một trang tính khác

Thông thường, bạn có thể phải làm việc với nhiều trang tính, hàm VLOOKUP có thể được sử dụng để tra cứu dữ liệu từ một trang tính khác giống như trên một trang tính.

Ví dụ: bạn có hai trang tính như hình minh họa bên dưới, để tra cứu và trả về dữ liệu tương ứng từ trang tính bạn đã chỉ định, vui lòng thực hiện theo các bước sau:

Bước 1: Áp dụng và điền công thức cho các ô khác

Vui lòng nhập hoặc sao chép công thức bên dưới vào ô trống nơi bạn muốn lấy các mục phù hợp. Sau đó, kéo núm điều khiển điền xuống các ô mà bạn muốn áp dụng công thức này.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Kết quả:

Bạn sẽ nhận được kết quả tương ứng khi cần, xem ảnh chụp màn hình:

2.8 Giá trị VLOOKUP từ sổ làm việc khác

Phần này sẽ nói về tra cứu và trả về các giá trị phù hợp từ một sổ làm việc khác bằng cách sử dụng hàm VLOOKUP.

Ví dụ: giả sử bạn có hai sổ làm việc. Sổ làm việc đầu tiên chứa danh sách các sản phẩm và chi phí tương ứng của chúng. Trong sổ làm việc thứ hai, bạn muốn trích xuất chi phí tương ứng cho từng mặt hàng sản phẩm như ảnh chụp màn hình bên dưới.

Bước 1: Áp dụng và điền công thức

Mở cả hai sổ làm việc bạn muốn sử dụng, sau đó áp dụng công thức sau vào một ô mà bạn muốn đưa kết quả vào sổ làm việc thứ hai. Sau đó, kéo và sao chép công thức này sang các ô khác mà bạn cần

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Kết quả:

2.9 Trả về văn bản trống hoặc cụ thể thay vì 0 hoặc lỗi #N/A

Thông thường, khi bạn sử dụng hàm VLOOKUP để trả về một giá trị tương ứng, nếu ô khớp trống sẽ trả về 0. Và nếu không tìm thấy giá trị khớp, bạn sẽ nhận được giá trị lỗi #N/A như trong hình ảnh chụp màn hình bên dưới. Nếu bạn muốn hiển thị một ô trống hoặc một giá trị cụ thể thay vì 0 hoặc #N/A, điều này VLOOKUP để trả về giá trị trống hoặc cụ thể thay vì 0 hoặc N/A hướng dẫn có thể giúp bạn.

Ví dụ về VLOOKUP nâng cao

3.1 Tra cứu hai chiều (Vlookup theo hàng và theo cột)

Đôi khi, bạn có thể cần thực hiện tra cứu 2 chiều, nghĩa là tìm kiếm một giá trị trong cả hàng và cột cùng một lúc. Ví dụ: nếu bạn có phạm vi dữ liệu sau và bạn có thể cần lấy giá trị cho một sản phẩm cụ thể trong một quý cụ thể. Phần này sẽ giới thiệu một công thức để giải quyết công việc này trong Excel.

Trong Excel, bạn có thể sử dụng kết hợp các hàm VLOOKUP và MATCH để thực hiện tra cứu hai chiều.

Vui lòng áp dụng công thức sau vào một ô trống, rồi nhấn đăng ký hạng mục thi phím để nhận kết quả.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 VLOOKUP đối sánh giá trị dựa trên hai tiêu chí trở lên

Bạn có thể dễ dàng tra cứu giá trị phù hợp dựa trên một tiêu chí, nhưng nếu bạn có hai tiêu chí trở lên, bạn có thể làm gì?

 3.2.1 Giá trị đối sánh VLOOKUP dựa trên hai hoặc nhiều tiêu chí có công thức

Trong trường hợp này, các hàm LOOKUP hay MATCH và INDEX trong Excel có thể giúp bạn giải quyết công việc này một cách nhanh chóng và dễ dàng.

Ví dụ, tôi có bảng dữ liệu bên dưới, để trả lại giá phù hợp dựa trên sản phẩm và kích thước cụ thể, các công thức sau có thể giúp bạn.

Bước 1: Áp dụng bất kỳ một công thức nào

Công thức 1: Sau khi dán công thức, vui lòng nhấn đăng ký hạng mục thi Chìa khóa.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Công thức 2: Sau khi dán công thức, vui lòng nhấn Ctrl + Shift + Enter phím.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Kết quả:

 3.2.2 VLOOKUP so khớp giá trị dựa trên hai tiêu chí trở lên với tính năng thông minh

Việc ghi nhớ các công thức phức tạp trên cần được áp dụng lặp đi lặp lại có thể gây khó khăn cho bạn, điều này có thể làm giảm hiệu quả công việc của bạn. Tuy nhiên, Kutools cho Excel Cung cấp một Tra cứu đa điều kiện tính năng cho phép bạn trả về kết quả tương ứng dựa trên một hoặc nhiều điều kiện chỉ với vài cú nhấp chuột.

1. Nhấp chuột Kutools > SIÊU NHÌN > Tra cứu đa điều kiện để kích hoạt tính năng này.
2. Sau đó chỉ định các hoạt động từ hộp thoại dựa trên dữ liệu của bạn.

3.3 VLOOKUP để trả về nhiều giá trị với một hoặc nhiều tiêu chí

Trong Excel, hàm VLOOKUP tìm kiếm một giá trị và chỉ trả về giá trị khớp đầu tiên nếu tìm thấy nhiều giá trị tương ứng. Đôi khi, bạn có thể muốn trả về tất cả các giá trị tương ứng trong một hàng, trong một cột hoặc trong một ô. Phần này sẽ nói về cách trả về nhiều giá trị phù hợp với một hoặc nhiều điều kiện trong sổ làm việc.

 3.3.1 VLOOKUP tất cả các giá trị phù hợp dựa trên một hoặc nhiều điều kiện theo chiều ngang

Giả sử rằng bạn có một bảng dữ liệu chứa quốc gia, thành phố và tên trong phạm vi A1:C14, và bây giờ, bạn muốn trả về tất cả các tên theo chiều ngang từ "US" như ảnh chụp màn hình bên dưới. Để giải quyết công việc này, hãy bấm vào đây để có được kết quả từng bước.

 3.3.2 VLOOKUP tất cả các giá trị phù hợp dựa trên một hoặc nhiều điều kiện theo chiều dọc

Nếu bạn cần Vlookup và trả về tất cả các giá trị phù hợp theo chiều dọc dựa trên các tiêu chí cụ thể như ảnh chụp màn hình bên dưới, xin vui lòng bấm vào đây để có được giải pháp chi tiết.

 3.3.3 VLOOKUP tất cả các giá trị phù hợp dựa trên một hoặc nhiều điều kiện vào một ô

Nếu bạn muốn Vlookup và trả về nhiều giá trị phù hợp vào một ô duy nhất với dấu phân cách được chỉ định, chức năng mới của TEXTJOIN có thể giúp bạn giải quyết công việc này một cách nhanh chóng và dễ dàng.

3.4 VLOOKUP để trả về toàn bộ hàng của ô khớp

Trong phần này, tôi sẽ nói về cách truy xuất toàn bộ hàng của một giá trị khớp bằng cách sử dụng hàm VLOOKUP.

Bước 1: Áp dụng và điền vào công thức sau

Vui lòng sao chép hoặc nhập công thức dưới đây vào ô trống nơi bạn muốn xuất kết quả và nhấn đăng ký hạng mục thi key để lấy giá trị đầu tiên. Sau đó, kéo ô công thức sang bên phải cho đến khi dữ liệu của toàn bộ hàng được hiển thị.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Kết quả:

Bây giờ, bạn có thể thấy toàn bộ dữ liệu hàng được trả về. Xem ảnh chụp màn hình:

3.5 VLOOKUP lồng nhau trong Excel

Đôi khi, bạn có thể cần tra cứu các giá trị được liên kết với nhau trên nhiều bảng. Trong trường hợp này, bạn có thể lồng nhiều hàm VLOOKUP vào với nhau để lấy giá trị cuối cùng.

Ví dụ: tôi có một trang tính chứa hai bảng riêng biệt. Bảng đầu tiên liệt kê tất cả các tên sản phẩm cùng với nhân viên bán hàng tương ứng của chúng. Bảng thứ hai liệt kê tổng doanh thu của từng nhân viên bán hàng. Bây giờ, nếu bạn muốn tìm doanh số của từng sản phẩm, như minh họa trong ảnh chụp màn hình sau, bạn có thể lồng hàm VLOOKUP để hoàn thành nhiệm vụ này.

Công thức chung cho hàm VLOOKUP lồng nhau là:

Bước 1: Áp dụng và điền vào công thức sau

Vui lòng áp dụng công thức sau vào một ô trống, rồi kéo núm điều khiển điền xuống các ô mà bạn muốn áp dụng công thức này.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Kết quả:

Bây giờ, bạn sẽ nhận được kết quả như trong ảnh chụp màn hình sau:

3.6 Kiểm tra xem giá trị có tồn tại dựa trên dữ liệu danh sách trong một cột khác

Hàm VLOOKUP cũng có thể giúp bạn kiểm tra xem các giá trị có tồn tại hay không dựa trên danh sách dữ liệu trong một cột khác. Ví dụ: nếu bạn muốn tìm tên trong cột C và chỉ cần trả về Có hoặc Không nếu tên được tìm thấy hoặc không trong cột A như ảnh chụp màn hình bên dưới.

Bước 1: Áp dụng và điền vào công thức sau

Vui lòng áp dụng công thức sau vào một ô trống, sau đó, kéo núm điều khiển điền xuống các ô mà bạn muốn điền công thức này.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Kết quả:

Và bạn sẽ nhận được kết quả như bạn cần, xem ảnh chụp màn hình:

3.7 VLOOKUP và tính tổng tất cả các giá trị khớp trong hàng hoặc cột

Khi làm việc với dữ liệu số, bạn có thể cần trích xuất các giá trị phù hợp từ một bảng và tính tổng các số trong một số cột hoặc hàng. Phần này sẽ giới thiệu một số công thức có thể giúp bạn hoàn thành nhiệm vụ này.

 3.7.1 VLOOKUP và tính tổng tất cả các giá trị khớp trong một hàng hoặc nhiều hàng

Giả sử bạn có danh sách sản phẩm có doanh số trong vài tháng, như trong ảnh chụp màn hình sau. Bây giờ, bạn cần tính tổng tất cả các đơn đặt hàng trong tất cả các tháng dựa trên các sản phẩm đã cho.

Bước 1: Áp dụng và điền vào công thức sau

Vui lòng sao chép hoặc nhập công thức sau vào ô trống, rồi nhấn Ctrl + Shift + Enter keys với nhau để có được kết quả đầu tiên. Sau đó, kéo núm điều khiển điền xuống để sao chép công thức này sang các ô khác mà bạn cần.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Kết quả:

Tất cả các giá trị trong một hàng của giá trị khớp đầu tiên đã được cộng lại với nhau, xem ảnh chụp màn hình:

 3.7.2 VLOOKUP và tính tổng tất cả các giá trị khớp trong một cột hoặc nhiều cột

Nếu bạn muốn tính tổng giá trị cho các tháng cụ thể như trong ảnh chụp màn hình bên dưới. Hàm VLOOKUP bình thường có thể không giúp ích gì cho bạn, ở đây, bạn nên áp dụng kết hợp các hàm SUM, INDEX và MATCH để tạo công thức.

Bước 1: Áp dụng công thức sau

Áp dụng công thức dưới đây vào một ô trống, sau đó kéo núm điều khiển điền xuống để sao chép công thức này sang các ô khác.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Kết quả:

Giờ đây, các giá trị khớp đầu tiên dựa trên tháng cụ thể trong một cột đã được cộng lại với nhau, hãy xem ảnh chụp màn hình:

 3.7.3 VLOOKUP và tính tổng các giá trị khớp đầu tiên hoặc tất cả các giá trị khớp bằng một tính năng mạnh mẽ

Có thể các công thức trên khiến bạn khó nhớ, trong trường hợp này, tôi sẽ đề xuất một tính năng mạnh mẽ - Tra cứu và tính tổng of Kutools cho Excel, với tính năng này, bạn có thể Vlookup và tính tổng các giá trị khớp đầu tiên hoặc tất cả các giá trị khớp trong các hàng hoặc cột một cách dễ dàng nhất có thể.

1. Nhấp chuột Kutools > SIÊU NHÌN > LOOKUP và Sum để kích hoạt tính năng này.
2. Sau đó chỉ định các hoạt động từ hộp thoại dựa trên nhu cầu của bạn.

 3.7.4 VLOOKUP và tính tổng tất cả các giá trị khớp cả trong hàng và cột

Ví dụ, nếu bạn muốn tính tổng các giá trị khi bạn cần khớp cả cột và hàng để có được tổng giá trị của sản phẩm Áo len trong tháng XNUMX như ảnh chụp màn hình bên dưới.

Tại đây, bạn có thể sử dụng hàm SUMPRODCT để hoàn thành tác vụ này.

Vui lòng áp dụng công thức sau vào một ô, rồi nhấn đăng ký hạng mục thi để lấy kết quả, xem ảnh chụp màn hình:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VLOOKUP để hợp nhất hai bảng dựa trên các cột chính

Trong công việc hàng ngày, khi phân tích dữ liệu, bạn có thể cần tập hợp tất cả thông tin cần thiết vào một bảng duy nhất dựa trên một hoặc nhiều cột chính. Để hoàn thành tác vụ này, bạn có thể sử dụng hàm INDEX và MATCH thay vì hàm VLOOKUP.

 3.8.1 VLOOKUP để hợp nhất hai bảng dựa trên một cột chính

Ví dụ: bạn có hai bảng, bảng đầu tiên chứa dữ liệu sản phẩm và tên và bảng thứ hai chứa dữ liệu sản phẩm và đơn hàng, bây giờ, bạn muốn kết hợp hai bảng này bằng cách khớp cột sản phẩm chung thành một bảng.

Bước 1: Áp dụng và điền vào công thức sau

Hãy áp dụng công thức sau vào một ô trống. Sau đó, kéo núm điều khiển điền xuống các ô mà bạn muốn áp dụng công thức này

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Kết quả:

Bây giờ, bạn sẽ nhận được một bảng đã hợp nhất với cột thứ tự nối với bảng đầu tiên dựa trên dữ liệu cột chính.

 3.8.2 VLOOKUP để hợp nhất hai bảng dựa trên nhiều cột chính

Nếu hai bảng bạn muốn nối có nhiều cột chính, để hợp nhất các bảng dựa trên các cột chung này, vui lòng thực hiện theo các bước bên dưới.

Công thức chung là:

Bước 1: Áp dụng công thức sau

Vui lòng áp dụng công thức bên dưới vào một ô trống nơi bạn muốn đặt kết quả, rồi nhấn Ctrl + Shift + Enter các phím với nhau để nhận giá trị phù hợp đầu tiên, xem ảnh chụp màn hình:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Bước 2: Điền công thức vào các ô khác

Sau đó, chọn ô công thức đầu tiên và kéo núm điều khiển điền để sao chép công thức này sang các ô khác khi bạn cần:

3.9 VLOOKUP khớp các giá trị trên nhiều trang tính

Bạn đã bao giờ cần thực hiện VLOOKUP trên nhiều trang tính trong Excel chưa? Ví dụ: nếu bạn có ba trang tính có phạm vi dữ liệu và bạn muốn truy xuất các giá trị cụ thể dựa trên tiêu chí từ các trang tính này, bạn có thể làm theo hướng dẫn từng bước Giá trị VLOOKUP trên nhiều trang tính để hoàn thành nhiệm vụ này.

Các giá trị phù hợp với hàm VLOOKUP giữ nguyên định dạng ô

Khi tra cứu các giá trị trùng khớp, định dạng ô ban đầu như màu chữ, màu nền, định dạng dữ liệu, v.v. sẽ không được giữ nguyên. Để giữ nguyên định dạng ô hoặc dữ liệu, phần này sẽ giới thiệu một số thủ thuật để giải quyết công việc.

4.1 VLOOKUP khớp giá trị và giữ nguyên màu ô, định dạng font chữ

Như chúng ta đã biết, hàm VLOOKUP bình thường chỉ có thể truy xuất giá trị khớp từ một dải dữ liệu khác. Tuy nhiên, có thể có những trường hợp bạn muốn nhận giá trị tương ứng cùng với định dạng ô, chẳng hạn như màu tô, màu phông chữ và kiểu phông chữ. Trong phần này, chúng ta sẽ thảo luận cách truy xuất các giá trị phù hợp trong khi vẫn giữ nguyên định dạng nguồn trong Excel.

Vui lòng thực hiện theo các bước sau để tra cứu và trả về giá trị tương ứng của nó cùng với định dạng ô:

Bước 1: Copy đoạn code 1 vào Sheet Code Module

1. Trong bảng tính chứa dữ liệu muốn VLOOKUP, nhấp chuột phải vào tab trang tính và chọn Mã Chế độ xem từ menu ngữ cảnh. Xem ảnh chụp màn hình:
   
2. Trong mở Microsoft Visual Basic cho các ứng dụng , vui lòng sao chép mã VBA bên dưới vào cửa sổ Mã.
3. Mã VBA 1: VLOOKUP để nhận định dạng ô cùng với giá trị tra cứu

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Bước 2: Copy đoạn code 2 vào cửa sổ Module

1. Vẫn còn trong Microsoft Visual Basic cho các ứng dụng cửa sổ, nhấp Chèn > Mô-đun, và sau đó sao chép mã VBA 2 bên dưới vào cửa sổ Mô-đun.
2. Mã VBA 2: VLOOKUP để nhận định dạng ô cùng với giá trị tra cứu

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Bước 3: Chọn tùy chọn cho VBAproject

1. Sau khi chèn các mã trên, sau đó nhấp vào CÔNG CỤ > dự án trong Microsoft Visual Basic cho các ứng dụng cửa sổ. Sau đó kiểm tra Thời gian chạy tập lệnh của Microsoft trong hộp kiểm Tài liệu tham khảo - VBAProject hộp thoại. Xem ảnh chụp màn hình:
2. Sau đó nhấn vào OK để đóng hộp thoại, sau đó lưu và đóng cửa sổ mã.

Bước 4: Nhập công thức để nhận kết quả

1. Bây giờ, quay trở lại bảng tính, áp dụng công thức sau. Sau đó, kéo núm điều khiển điền xuống để nhận tất cả kết quả cùng với định dạng của chúng. Xem ảnh chụp màn hình:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 Giữ nguyên định dạng ngày từ giá trị trả về VLOOKUP

Khi sử dụng hàm VLOOKUP để tra cứu và trả về một giá trị có định dạng ngày tháng, kết quả trả về có thể hiển thị dưới dạng số. Để giữ định dạng ngày trong kết quả trả về, bạn nên đặt hàm VLOOKUP trong hàm TEXT.

Bước 1: Áp dụng và điền vào công thức sau

Vui lòng áp dụng công thức dưới đây vào một ô trống. Sau đó, kéo núm điều khiển điền để sao chép công thức này sang các ô khác.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Kết quả:

Tất cả các ngày phù hợp đã được trả về như ảnh chụp màn hình bên dưới:

4.3 Trả về nhận xét ô từ VLOOKUP

Bạn đã bao giờ cần truy xuất cả dữ liệu ô phù hợp và nhận xét được liên kết của nó bằng VLOOKUP trong Excel, như minh họa trong ảnh chụp màn hình sau chưa? Nếu vậy, Hàm do Người dùng Xác định được cung cấp bên dưới có thể giúp bạn hoàn thành nhiệm vụ này.

Bước 1: Sao chép mã vào Mô-đun

1. Giữ phím tắt ALT + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.
2. Nhấp chuột Chèn > Mô-đun, sau đó sao chép và dán mã sau vào Cửa sổ mô-đun.
   Mã VBA: Vlookup và trả về giá trị khớp với nhận xét ô:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Sau đó lưu và đóng cửa sổ mã.

Bước 2: Nhập công thức để lấy kết quả

1. Bây giờ, hãy nhập công thức sau và kéo núm điều khiển điền để sao chép công thức này sang các ô khác. Nó sẽ trả về đồng thời cả giá trị phù hợp và nhận xét, xem ảnh chụp màn hình:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 Các số VLOOKUP được lưu dưới dạng văn bản

Chẳng hạn, tôi có một dải dữ liệu trong đó số ID trong bảng gốc ở định dạng số và số ID trong các ô tra cứu được lưu dưới dạng văn bản, bạn có thể gặp lỗi #N/A khi sử dụng hàm VLOOKUP thông thường. Trong trường hợp này, để truy xuất thông tin chính xác, bạn có thể bọc các hàm TEXT và VALUE trong hàm VLOOKUP. Dưới đây là công thức để đạt được điều này:

Bước 1: Áp dụng và điền vào công thức sau

Vui lòng áp dụng công thức sau vào một ô trống, sau đó kéo núm điều khiển điền xuống để sao chép công thức này.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Kết quả:

Bây giờ, bạn sẽ nhận được kết quả chính xác như ảnh chụp màn hình bên dưới: