Bài viết sau đây, Học Excel Online sẽ hướng dẫn bạn cách sử dụng Vlookup để tra cứu kết hợp nhiều điều kiện và trả về một kết quả cụ thể hoặc tất cả các kết quả phù hợp. Đồng thời, bạn cũng sẽ được hướng dẫn cách dùng hàm Vlookup với nhiều trang tính trong Excel và nhiều hơn thế nữa. 

Đây là phần thứ 2 trong loạt bài giúp bạn khai thác sức mạnh của hàm Vlookup trong Excel. Các ví dụ sẽ cho chúng ta biết chức năng Vlookup hoạt động như thế nào. Bây giờ hãy bắt đầu xem xét lại cách sử dụng cơ bản của hàm Vlookup trong Excel.

Nhắc lại một chút về cú pháp của hàm Vlookup:

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

Làm thế nào để Vlookup nhiều tiêu chí

Hàm Vlookup trong Excel thực sự hữu ích khi tìm kiếm một giá trị nhất định dựa trên cơ sở dữ liệu. Tuy nhiên, nó thiếu một tính năng quan trọng – cú pháp của nó chỉ cho phép một giá trị tra cứu. Nhưng nếu bạn muốn tra cứu với một số điều kiện? Có một số giải pháp khác nhau cho bạn lựa chọn.

Công thức 1. VLOOKUP dựa trên 2 tiêu chí

Giả sử bạn có danh sách các đơn hàng và muốn tìm số lượng dựa trên 2 tiêu chí Customer name và Product. Một yếu tố phức tạp là mỗi khách hàng đặt hàng nhiều sản phẩm, như trong bảng dưới đây:

Một công thức VLOOKUP thông thường sẽ không giải quyết được trong trường hợp này, bởi nó trả về giá trị tìm thấy đầu tiên phù hợp với giá trị tra cứu mà bạn chỉ định

Giải pháp đơn giản là tạo một cột bổ sung nối tất cả các tiêu chí mà bạn muốn, trong ví dụ này là các cột Customer và Product. Điều quan trọng là cột trợ giúp phải là cột ngoài cùng bên trái trong mảng bảng vì đó là nơi hàm VLOOKUP của Excel luôn tìm kiếm giá trị tra cứu.

Vì vậy, hãy thêm một cột vào bên trái bảng của bạn và sao chép công thức dưới đây qua cột đó. Điều này sẽ điền vào cột trợ giúp với các giá trị từ cột B và C (ký tự khoảng trắng được nối giữa để dễ đọc hơn):

=B2&” “&C2

Và sau đó, sử dụng công thức VLOOKUP chuẩn và đặt cả hai tiêu chí vào đối số lookup_value:

=VLOOKUP(“Jeremy Sweets”, A2:D11, 4, FALSE)

Hoặc, nhập tiêu chí vào các ô riêng biệt (trong trường hợp trên  là G1 và G2) và nối các ô đó:

=VLOOKUP(G1&” “&G2, A2:D11, 4, FALSE)

Khi chúng ta muốn trả về một giá trị từ cột D, là giá trị thứ tư trong mảng bảng, chúng ta sử dụng 4 cho col_index_num . Đối số range_lookup được đặt thành FALSE để Vlookup đối sánh chính xác. Ảnh chụp màn hình bên dưới cho thấy kết quả:

Trong trường hợp Lookup Table của bạn nằm trong một trang tính khá , hãy đưa tên của trang tính vào công thức VLOOKUP của bạn. Ví dụ:

=VLOOKUP(G1&” “&G2, Orders!A2:D11, 4, FALSE)

Ngoài ra, hãy tạo một phạm vi đã đặt tên cho Lookup Table (giả sử, Orders) để làm cho công thức dễ đọc hơn:

=VLOOKUP(G1&” “&G2, Orders, 4, FALSE)

Ghi chú. Để công thức hoạt động chính xác, các giá trị trong cột phụ phải được nối chính xác theo giống như trong đối số lookup_value . Ví dụ: chúng ta sử dụng một ký tự khoảng trắng để phân tách các tiêu chí trong cả cột trợ giúp (B2 & “” & C2) và công thức VLOOKUP (G1 & “” & G2).

Công thức 2. Hàm VLOOKUP trong Excel với nhiều điều kiện

Về lý thuyết Vlookup vẫn sử dụng cho những nhiệm vụ nhiều hơn hai tiêu chí. Tuy nhiên, có một số lưu ý. Thứ nhất, giá trị tra cứu được giới hạn trong 255 ký tự và thứ hai, thiết kế của trang tính có thể không cho phép thêm cột trợ giúp.

Microsoft Excel thường cung cấp nhiều cách để làm điều tương tự. Để Vlookup nhiều tiêu chí, bạn có thể sử dụng kết hợp INDEX MATCH hoặc hàm XLOOKUP được giới thiệu gần đây trong Office 365.

Ví dụ: để tra cứu dựa trên 3 giá trị khác nhau ( Date , Customer name và Product ), chúng ta có thể sử dụng một trong các công thức sau:

=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))

=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)

• G1 là tiêu chí 1 (Date)
• G2 là tiêu chí 2 (Customer name)
• G3 là tiêu chí 3 (Product)
• A2: A11 là phạm vi tra cứu 1 (Date)
• B2: B11 là phạm vi tra cứu 2 (product name)
• C2: C11 là phạm vi tra cứu 3 (product)
• D2: D11 là phạm vi trả về (Qty)

Ghi chú. Trong tất cả các phiên bản ngoại trừ Excel 365, INDEX MATCH phải được nhập dưới dạng công thức mảng CSE bằng cách nhấn Ctrl + Shift + Enter. Trong Excel 365 hỗ trợ mảng động, nó cũng hoạt động như một công thức thông thường.

Làm thế nào để sử dụng VLOOKUP và trả về hơn 1 giá trị

Hàm Vlookup trong Excel chỉ tìm nạp một giá trị phù hợp và nó chỉ trả về giá trị phù hợp đầu tiên. Nhưng nếu có nhiều kết quả phù hợp với mảng tra cứu và bạn muốn nó trả về tất cả các giá trị thì phải làm thế nào? Nghe có phức tạp nhưng dưới đây sẽ hướng dẫn bạn một số cách xử lý.

Công thức 1: Sử dụng Vlookup lấy giá trị n phù hợp

Giả sử bạn có một cột là Customer name, một cột là sản phẩm họ đã mua Product và bạn đang tìm sản phẩm thứ 2 hoặc thứ 3 được mua bởi một khách hàng nhất định.

Cách đơn giản nhất là thêm một cột phụ vào bên trái của bảng như đã làm trong ví dụ đầu tiên. Nhưng lần này, chúng ta sẽ điền tên khách hàng và số lần xuất hiện như ” John Doe1 “, ” John Doe 2 “, v.v.

Sử dụng hàm COUNTIF với tham chiếu phạm vi hỗn hợp (tham chiếu đầu tiên là tuyệt đối và tham chiếu thứ hai là tương đối như $ B $ 2: B2). Vì tham chiếu tương đối thay đổi dựa trên vị trí của ô nơi công thức được sao chép, trong hàng 3, nó sẽ trở thành $ B $ 2: B3, ở hàng 4 – $ B $ 2: B4, v.v.

Được nối với tên khách hàng (B2), công thức có dạng sau:

=B2&COUNTIF($B$2:B2, B2)

Công thức trên chuyển đến A2, sau đó bạn sao chép nó xuống bao nhiêu ô nếu cần.

Sau đó, nhập tên đối tượng và số lần xuất hiện trong các ô riêng biệt (F1 và F2) và sử dụng công thức dưới đây để Vlookup:

=VLOOKUP(F1&F2, A2:C11, 3, FALSE)

Công thứ 2: Vlookup lấy giá trí xuất hiện lần thứ 2

Nếu bạn đang tìm kiếm lần xuất hiện thứ 2 , bạn có thể tiến hành mà không cần tạo cột phụ bằng cách tạo ra một công thức VLOOKUP phức tạp hơn với INDIRECT và MATCH:

=VLOOKUP(E1, INDIRECT(“A”&(MATCH(E1, A2:A11, 0)+2)&”:B11″), 2, FALSE)

• E1 là giá trị tra cứu
• A2: A11 là phạm vi tra cứu
• B11 là ô cuối cùng (dưới cùng bên phải) của Lookup Table

Chú ý. Công thức này chỉ tìm thấy giá trị phù hợp thứ hai. Nếu bạn cần có sự xuất hiện vị trí khác thì quay lại với giải pháp trước. Lưu ý rằng công thức trên được viết cho một trường hợp cụ thể trong đó các ô dữ liệu trong Lookup Table bắt đầu ở hàng 2. Nếu bảng của bạn ở đâu đó ở giữa trang tính, hãy sử dụng công thức chung dưới đây, trong đó A1 là ô trên cùng bên trái của Lookup Table có chứa tiêu đề cột:

=VLOOKUP(E1, INDIRECT(“A”&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&”:B11″), 2, FALSE)

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

Đây là phần quan trọng của công thức tạo phạm vi Vlookup động :

INDIRECT(“A”&(MATCH(E1, A2:A11, 0)+2)&”:B11″)

Hàm MATCH được cấu hình cho đối sánh chính xác (0 trong đối số cuối cùng) so sánh tên đích (E1) với danh sách các tên (A2: A11) và trả về vị trí của đối sánh được tìm thấy đầu tiên. Số này sẽ được sử dụng làm tọa độ hàng bắt đầu cho phạm vi vlookup, vì vậy chúng tôi thêm 2 vào nó (+1 để loại trừ trường hợp đầu tiên và +1 để loại trừ hàng 1 với tiêu đề cột). Ngoài ra, bạn có thể sử dụng 1 + ROW (A1) để tự động tính toán điều chỉnh cần thiết dựa trên vị trí của hàng tiêu đề (A1 trong trường hợp của chúng tôi).

Kết quả là nhận được chuỗi văn bản sau đây và  INDIRECT chuyển đổi thành tham chiếu phạm vi:

INDIRECT(“A”&5&”:B11″) -> A5:B11

Phạm vi này đi đến đối số table_array của VLOOKUP buộc nó phải bắt đầu tìm kiếm ở hàng 5, bỏ qua phiên bản đầu tiên của giá trị tra cứu:

VLOOKUP(E1, A5:B11, 2, FALSE)

Vlookup trả về nhiều giá trị trong Excel

Hàm Vlookup trong Excel được thiết kế và chỉ trả về một kết quả phù hợp. Vậy có cách nào để hàm Vlookup trả về nhiều giá trị hay không? Để làm được điều này phải yêu cầu sử dụng kết hợp một số hàm khác như Index, Small và Row.

Ví dụ dưới đây có thể tìm thấy tất cả các lần xuất hiện của giá trị tra cứu F2 trong phạm vi tra cứu B2: B16 và trả về nhiều kết quả phù hợp từ cột C:

{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,””), ROW()-3)),””)}

Có 2 cách để nhập công thức vào trang tính của bạn:

Nhập công thức vào ô đầu tiên, nhấn Ctrl + Shift + Enter, rồi kéo nó xuống một vài ô nữa.

Chọn một số ô liền kề trong một cột duy nhất (F1: F11 trong ảnh chụp màn hình bên dưới), nhập công thức và nhấn Ctrl + Shift + Enter để hoàn thành nó.

Dù bằng cách nào, số ô mà bạn nhập công thức phải bằng hoặc lớn hơn số kết quả phù hợp tối đa có thể.

Cách Vlookup theo hàng và cột (tra cứu hai chiều)

Tìm kiếm 2 chiều trong Excel (còn gọi là tra cứu ma trận hoặc tra cứu hai chiều) tìm kiếm trên cả hàng và cột . Nói cách khác, bạn tìm kiếm một giá trị tại giao điểm của một hàng và cột nhất định.

Đối với ví dụ này, chúng ta sẽ lấy bảng sau đây với doanh số hàng tháng và tính ra công thức VLOOKUP để truy xuất số liệu bán hàng cho một mặt hàng cụ thể trong một tháng nhất định.

Với tên mục trong A2: A9, tên tháng trong B1: F1, mục mục tiêu trong I1 và tháng mục tiêu trong I2, công thức như sau:

=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)

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

Cốt lõi của công thức là hàm VLOOKUP tiêu chuẩn tìm kiếm kết quả khớp chính xác với giá trị tra cứu trong I1. Nhưng vì không biết chính xác doanh số bán hàng cho một tháng cụ thể nằm trong cột nào, nên không thể cung cấp số cột trực tiếp cho đối số col_index_num. Để tìm cột đó sử dụng hàm MATCH sau :

MATCH(I2, A1:F1, 0)

Ý nghĩa công thức: tra cứu giá trị I2 trong A1: F1 và trả về vị trí tương đối của nó trong mảng. Bằng cách cung cấp 0 cho đối số thứ 3, bạn hướng dẫn MATCH tìm giá trị chính xác bằng giá trị tra cứu (giống như sử dụng FALSE cho đối số range_lookup của hàm VLOOKUP).

Vì Mar ở cột thứ 4 trong mảng tra cứu, nên hàm MATCH trả về giá trị 4, đi trực tiếp đến đối số col_index_num của hàm VLOOKUP:

VLOOKUP(I1, A2:F9, 4, FALSE)

Mặc dù tên tháng bắt đầu ở cột B nhưng ví dụ trên sử dụng A1: I1 cho mảng tra cứu. Điều này được thực hiện để số được MATCH trả về tương ứng với vị trí của cột trong table_array của VLOOKUP.

Cách thực hiện nhiều Vlookup trong Excel (Vlookup lồng nhau)

Đôi khi Main Table và Lookup Table của bạn không có một cột chung nào, điều này không cho bạn thực hiện một Vlookup bình thường. Tuy nhiên, có một bảng khác, không chứa thông tin bạn đang tìm kiếm nhưng có một cột chung với Main Table của bạn và một chung với Lookup Table của bạn.

Hình ảnh minh họa dưới đây:

Mục đích là sao chép giá vào bảng chính dựa trên ID mặt hàng . Vấn đề là bảng chứa giá không có ID mặt hàng , có nghĩa là chúng ta sẽ phải thực hiện hai Vlookup trong một công thức.

Để thuận tiện, trước tiên hãy tạo một vài phạm vi được đặt tên:

Lookup Table 1 có tên là Sản phẩm (D3: E3).

Lookup Table 2 có tên là Giá ( G3: H3 ).

Các bảng có thể nằm trong các trang tính giống nhau hoặc khác nhau.

Và bây giờ, chúng ta sẽ thực hiện cái gọi là Vlookup kép , hay còn gọi là Vlookup lồng nhau .

Đầu tiên, tạo công thức VLOOKUP để tìm tên sản phẩm trong Lookup Table 1 (có tên Product ) dựa trên ID ITEM (A3):

=VLOOKUP(A3, Products, 2, FALSE)

Tiếp theo, đặt công thức trên vào đối số lookup_value của một hàm VLOOKUP khác để kéo giá từ Lookup Table 2 (có tên là Price ) dựa trên tên sản phẩm được trả về bởi hàm VLOOKUP lồng nhau:

=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)

Ảnh chụp màn hình bên dưới cho thấy công thức Vlookup lồng nhau của chúng tôi đang hoạt động:

Sử dụng Vlookup từ nhiều trang tính khác nhau

Trong một số trường hợp, bạn có thể có dữ liệu ở cùng một định dạng được chia trên nhiều bảng tính và mỗi lần bạn muốn kéo dữ liệu phù hợp từ một trang tính cụ thể tùy thuộc vào giá trị được nhập vào một ô nào. Một ví dụ sẽ giúp ta dễ hiểu hơn. Giả sử bạn có một vài báo cáo bán hàng khu vực ở cùng một định dạng và bạn đang tìm kiếm số liệu bán hàng cho một sản phẩm cụ thể ở một số khu vực nhất định:

Giống như trong ví dụ trước, bắt đầu với việc xác định một vài tên:

Phạm vi A2: B5 trong trang tính CA được đặt tên là CA_Sales .

Phạm vi A2: B5 trong trang tính FL được đặt tên là FL_Sales .

Phạm vi A2: B5 trong trang tính KS được đặt tên là KS_Sales .

Như bạn có thể thấy, tất cả các phạm vi được đặt tên đều có phần chung ( Sales ) và phần duy nhất ( CA , FL , KS ). Hãy đảm bảo đặt tên cho các phạm vi theo cách tương tự vì nó rất cần thiết cho công thức mà chúng ta sắp xây dựng.

Công thức 1. INDIRECT VLOOKUP để kéo dữ liệu động từ các trang tính khác nhau

Nếu nhiệm vụ của bạn là truy xuất dữ liệu từ nhiều trang tính, công thức VLOOKUP INDIRECT là giải pháp tốt nhất – nhỏ gọn và dễ hiểu.

Đối ví dụ này:

Nhập các sản phẩm quan tâm trong A2 và A3. Đó là những giá trị tra cứu của chúng tôi.

Nhập các phần duy nhất của các dải ô được đặt tên trong B1, C1 và D1.

Tiếp theo nối ô chứa phần duy nhất (B1) với phần chung (“_Sales”) và cấp chuỗi kết quả thành INDIRECT:

INDIRECT(B$1&”_Sales”)

Hàm INDIRECT biến đổi chuỗi thành tên mà Excel có thể hiểu được và bạn đặt nó vào đối số table_array của hàm VLOOKUP:

=VLOOKUP($A2, INDIRECT(B$1&”_Sales”), 2, FALSE)

Công thức trên chuyển đến B2, sau đó bạn sao chép nó xuống bên phải.

Lưu ý: trong giá trị tra cứu ($ A2), chúng tôi đã khóa tọa độ cột với tham chiếu ô tuyệt đối để cột vẫn cố định khi công thức được sao chép sang bên phải nên đã khóa hàng. Trong tham chiếu B$1, vì muốn tọa độ cột thay đổi và cung cấp một phần tên thích hợp thành INDIRECT tùy thuộc vào cột mà công thức được sao chép vào đó:

Nếu bảng chính của bạn được tổ chức khác nhau, các giá trị tra cứu trong một hàng và các phần duy nhất của tên phạm vi trong một cột, thì bạn nên khóa tọa độ hàng trong giá trị tra cứu (B $ 1) và tọa độ cột trong phần tên ($ A2):

=VLOOKUP(B$1, INDIRECT($A2&”_Sales”), 2, FALSE)

Công thức 2. VLOOKUP và IF lồng nhau để tra cứu nhiều trang tính

Trong trường hợp bạn chỉ có hai hoặc ba trang tính tra cứu, bạn có thể sử dụng công thức VLOOKUP khá đơn giản với các hàm IF lồng nhau để chọn trang tính chính xác dựa trên giá trị khóa trong một ô cụ thể:

=VLOOKUP($A2, IF(B$1=”CA”, CA_Sales, IF(B$1=”FL”, FL_Sales, IF(B$1=”KS”, KS_Sales,””))), 2, FALSE)

Trong đó $ A2 là giá trị tra cứu (tên mặt hàng) và B $ 1 là giá trị khóa (trạng thái):

Trong trường hợp này, bạn không nhất thiết phải xác định tên và có thể sử dụng tham chiếu bên ngoài để tham chiếu đến một trang tính hoặc sổ làm việc khác.

Trên đây là cách sử dụng hàm VLOOKUP trong Excel. Ngoài ra còn có rất nhiều hàm trong Excel khác hỗ trợ tối ưu cho công việc. Chúng tôi hy vọng có thể hỗ trợ bạn xử lý tốt Excel và hẹn gặp bạn những bài hướng dẫn tiếp theo.