Cách dùng hàm VLOOKUP nhiều giá trị và lồng VLOOKUP với nhiều tiêu chí

Hàm VLOOKUP cơ bản chắc chắn bạn nào làm Excel cũng đã biết sử dụng. Chúng mình sẽ hướng dẫn các bạn kiến thức nâng cao về hàm VLOOKUP nhiều giá trị, lồng VLOOKUP với nhiều tiêu chí và tra cứu hai chiều. Hãy cùng theo dõi trong bài viết dưới đây nhé.

Hàm VLOOKUP với nhiều tiêu chí

Hàm VLOOKUP thực sự hữu ích khi tìm kiếm một giá trị nhất định trên một cơ sở dữ liệu. Tuy nhiên, nó có điểm hạn chế lớn – cú pháp của nó chỉ cho phép tra cứu một giá trị. Nếu bạn muốn tìm kiếm theo nhiều điều kiện thì sao? Liệu có hàm tìm kiếm trả về nhiều giá trị trong Excel không? Xem ngay giải pháp dưới đây:

Ví dụ 1: Tra cứu 2 tiêu chí khác nhau

Giả sử bạn có một danh sách các đơn đặt hàng và bạn muốn tìm Qty (Số lượng) dựa trên 2 tiêu chí – “Name” và “Product”.  Phức tạp ở chỗ là mỗi khách hàng đặt hàng nhiều sản phẩm, như bạn thấy trong bảng dưới đây:

Một công thức VLOOKUP một tiêu chí thông thường sẽ không giải quyết được trong trường hợp này, bởi nó chỉ 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. 

=VLOOKUP(B1,$A$6:$C$17,3,FALSE)

Ví dụ nếu bạn muốn biết số lượng “Sweets” được mua bởi “Jeremy Hill”, kết quả đầu tiên tìm thấy trả về là “15” tương ứng với “Apples”.

Giải pháp đơn giản là tạo thêm một cột mới và kết hợp các tiêu chí mà bạn muốn tìm kiếm. Trong ví dụ này, ta kết hợp các cột Customer và Product. Lưu ý cột kết hợp luôn nằm ở vị trí ngoài cùng bên trái của phạm vi bạn đang tra cứu, bởi vì đây là nơi mà hàm VLOOKUP luôn tìm kiếm giá trị tra cứu.

Vì vậy, bạn chèn thêm một cột phụ vào bảng của bạn bên trái cột Customer. Sau đó, bạn điền dữ liệu cho cột mới (cột A) bằng công thức nối giá trị trong ô A7: 

=B7&" "&C7

Tiếp theo hãy kéo và sao chép công thức của ô A7 xuống các ô còn lại trong cột. Và sau đó, bạn có thể điền hàm VLOOKUP nhiều tiêu chí vào ô B4 như sau:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

hoặc là:

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

Trong đó B1 chứa giá trị tra cứu (lookup_value) và 4 là số cột chứa dữ liệu bạn muốn tìm (cột D).

Xem thêm: Cách kết hợp hàm IF và VLOOKUP trong Excel

Ví dụ 2: Hàm VLOOKUP với 2 tiêu chí từ một bảng tính khác

Nếu bạn cần phải cập nhật bảng chính với dữ liệu từ bảng khác (bảng hoặc trang tính khác), thì bạn có thể nối các giá trị tra cứu trực tiếp vào công thức mà bạn nhập vào bảng chính của bạn.

Vì vậy, công thức VLOOKUP của bạn có thể trông như thế này:

=VLOOKUP(B2&" "&C2,Orders!$A$2:$B$13,4,FALSE)

Trong đó các cột B và C chứa tên customer và product tương ứng và Orders!$A$2: $B$13 là phạm vi tìm kiếm của sheet khác (sheet Orders)

Mẹo: Để làm cho công thức dễ đọc hơn, bạn có thể tạo một vùng được đặt tên cho bảng tra cứu (Lookup table), và công thức của bạn sẽ đơn giản hơn: 

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

Chú ý: Để công thức hoạt động, cột bên trái nhất của bảng tra cứu của bạn phải chứa các giá trị tra cứu ghép nối chính xác như trong tiêu chí tra cứu của bạn: B2&" "&C2

Ngoài ra, hãy nhớ VLOOKUP giới hạn trong 255 ký tự. Vì vậy, hãy nhớ điều này và đảm bảo tổng chiều dài của tiêu chí tra cứu của bạn không vượt quá giới hạn này.

Làm thế nào sử dụng hàm VLOOKUP nhiều giá trị

Hàm Vlookup chỉ có thể lấy một giá trị phù hợp, chính xác hơn, là giá trị đầu tiên được tìm thấy. Thế nếu có nhiều giá trị tra cứu và bạn muốn xuất hiện hai ba lần? Nghe có vẻ phức tạp, nhưng vẫn có cách thực hiện bằng hàm Vlookup nhiều giá trị.

Giả sử bạn có tên khách hàng trong một cột và các sản phẩm họ mua ở một cột khác. Và bây giờ, bạn muốn tìm sản thứ 2 thứ 3 hoặc thứ 4 do cùng một khách hàng mua.

Cách 1: Thêm cột phụ để tra cứu

Bước 1: Cách đơn giản nhất là thêm cột phụ trước cột Customer Names và điền tên và thêm số thứ tự vào, ví dụ như “John Doe1”, “John Doe2” vv. Điền hàm COUNTIF dưới đây vào ô A2 và sao chép công thức xuống các ô còn lại:

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

COUNTIF($B$2:B2,B2): đếm số lần xuất hiện của giá trị từ ô B2 trong phạm vi cụ thể (phạm vi đang mở rộng khi sao chép công thức từ ô B2 xuống các ô còn lại).

Kết quả của toàn bộ công thức này là kết hợp giá trị từ ô B2 với số lần xuất hiện của giá trị đó từ ô B2 đến vị trí hiện tại (như hình)

Bước 2: Sau đó, bạn có thể sử dụng một công thức VLOOKUP thông thường để tìm thứ tự tương ứng. Ví dụ:

• Tìm sản phẩm thứ hai do Dan Brown mua:

=VLOOKUP(“Dan Brown2”,$A$2:$C$16,3,FALSE)

• Đương nhiên, bạn có thể nhập một tham chiếu ô (F2) thay vì văn bản trong giá trị tra cứu, như bạn thấy trong ảnh chụp màn hình bên dưới:

Cách 2: Dùng hàm Vlookup nhiều giá trị mà không cần thêm cột:

Nếu bạn đang tìm kiếm lần xuất hiện thứ 2 , bạn có thể làm 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:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"& (MATCH($F$2,Table4[Customer Name],0)+2) &":$C16"),2,FALSE),"")

Trong công thức:

MATCH($F$2,Table4[Customer Name],0): Tìm vị trí của giá trị tại ô F2 trong cột "Customer Name" của bảng Table4.

(MATCH($F$2,Table4[Customer Name],0)+2): Thêm 2 vào vị trí tìm thấy ở bước trước để chuyển đến cột thứ 3 (cột C).

INDIRECT("$B$"& (MATCH($F$2,Table4[Customer Name],0)+2) &":$C16"): Tạo phạm vi từ cột B đến C, và từ hàng hiện tại đến hàng 16.

VLOOKUP($F$2,INDIRECT(...),2,FALSE): Tìm giá trị từ phạm vi đã chọn bằng cách so sánh với giá trị tại ô F2. Nếu tìm thấy, trả về giá trị từ cột thứ 2.

IFERROR(...,""): Kiểm tra lỗi trong công thức VLOOKUP. Nếu có lỗi, trả về một chuỗi trống.

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 khác, hãy quay lại với giải pháp trước.

Nếu bạn muốn có được danh sách tất cả các giá trị phù hợp, hàm VLOOKUP không thể làm được, bởi vì nó chỉ có thể trả lại một giá trị tại một thời điểm. Nhưng hàm INDEX của Excel có thể xử lý trường hợp này và tôi sẽ cho bạn thấy công thức trong ví dụ tiếp theo.

Cách lấy tất cả giá trị trùng lặp trong VLOOKUP

Như đã đề cập ở trên, hàm VLOOKUP không thể nhận được bản sao trùng lặp của giá trị tra cứu. Để làm điều này, bạn sẽ cần một công thức mảng phức tạp hơn bao gồm nhiều hàm Excel như INDEX, SMALL và ROW.

Ví dụ, công thức dưới đây tìm tất cả các trường hợp của giá trị trong ô F2 trong phạm vi tra cứu B2: B16, và trả về giá trị từ cột C trong các hàng tương tự:

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

Sao chép công thức này vào một số ô lân cận để hàm vlookup trả về nhiều giá trị, ví dụ: các ô F4:F8 (ảnh chụp màn hình). Số lượng ô mà bạn sao chép công thức phải bằng hoặc lớn hơn số mục trùng lặp tối đa. Ngoài ra, nhớ nhấn Ctrl + Shift + Enter để nhập một công thức mảng một cách chính xác.

Nếu bạn tò mò muốn biết logic của công thức, chúng ta hãy đi tìm hiểu sâu một chút:

$F$2: Đây là giá trị bạn đang tìm kiếm, được đặt tại ô F2.

B2:B16: Đây là phạm vi kiểm tra, nơi mà bạn đang so sánh giá trị từ ô F2.

IF($F$2=B2:B16, ROW(C2:C16)-1, ""): Đây là một mảng có kích thước giống như phạm vi kiểm tra. Nếu giá trị tại ô F2 bằng giá trị tại các ô trong phạm vi kiểm tra, thì nó sẽ trả về số hàng hiện tại (ROW(C2:C16)) trừ 1, ngược lại sẽ trả về một chuỗi trống.

INDEX($C$2:$C$16, SMALL(IF(...), ROW()-3)): Đây sử dụng hàm INDEX để truy xuất giá trị từ phạm vi C2:C16. Hàm SMALL kết hợp với mảng IF trước đó để lấy các giá trị thỏa mãn điều kiện và trả về giá trị thứ ROW()-3 trong danh sách này.

IFERROR(..., ""): Kiểm tra nếu có lỗi trong công thức INDEX. Nếu có lỗi, nó sẽ trả về một chuỗi trống.

{= ...}: Dấu ngoặc nhọn này chỉ ra rằng đây là một công thức mảng, được sử dụng để xử lý nhiều giá trị cùng một lúc.

Xem thêm: Hướng dẫn cách phân biệt lệnh VLOOKUP và lệnh HLOOKUP trong Excel

Làm thế nào để tra cứu hai chiều trong Excel bằng VLOOKUP

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.

Vì vậy, hãy sử dụng bảng “Monthly Sales” một lần nữa và viết công thức VLOOKUP tìm xem bao nhiêu lemons được bán vào tháng 3.

Bạn có thể thực hiện tra cứu hai chiều bằng một vài cách khác nhau. Vì vậy, hãy xem qua các lựa chọn thay thế dưới đây.

Hàm VLOOKUP & MATCH

Bạn có thể sử dụng liên kết của các hàm VLOOKUP và MATCH để tham chiếu chéo hai trường trong cơ sở dữ liệu, trong ví dụ này là Product (hàng) và Month (cột):

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

Trong đó:

• Công thức VLOOKUP sử dụng giá trị "Lemons" để tìm kiếm trong phạm vi từ $A$2 đến $I$9.
• MATCH("Mar",$A$1:$I$1,0): Hàm MATCH được sử dụng để tìm vị trí của giá trị "Mar" trong hàng đầu tiên ($A$1 đến $I$1). Tham số 0 đại diện cho tìm kiếm chính xác.
• Tham số cuối cùng là FALSE để tìm kiếm chính xác.

Đây là cách bạn tạo ra một công thức tra cứu có hai tiêu chí trong Excel, nó còn được gọi là tra cứu hai chiều.

Ngoài ra, để nâng cao kiến thức Excel, bạn hãy tham gia ngay khóa học Tuyệt đỉnh Excel của Gitiho:

Khóa học được xây dựng chương trình đi từ kiến thức nền tảng đển các phần chuyên sâu về Excel. Các bài giảng chi tiết, kèm theo bài tập và tài liệu tham khảo trong khóa học sẽ giúp bạn nhanh chóng nắm bắt kiến thức.

Các dải được đặt tên & SPACE (toán tử giao nhau)

Nếu bạn không thích những công thức Excel phức tạp, bạn có thể dùng cách dễ nhớ này:

Bước 1. Chọn bảng của bạn, chuyển sang tab Formulas và nhấp vào Create from Selection.

Bước 2. Microsoft Excel sẽ tạo ra các tên từ các giá trị trong hàng trên cùng và cột bên trái của lựa chọn của bạn, và bạn sẽ có thể tìm kiếm các tên đó trực tiếp thay vì tạo thành một công thức .

Bước 3. Chọn một ô trống bất kỳ, gõ =row_value column_value, ví dụ =Lemons Mar, hoặc ngược lại =Mar Lemons.

Khi bạn gõ, Microsoft Excel sẽ hiển thị một danh sách các tên phù hợp, chính xác như khi bạn bắt đầu gõ một công thức.

 Bước 4. Nhấn phím Enter và xem kết quả.

Tất cả các cách, tùy theo phương pháp bạn chọn, kết quả tra cứu hai chiều cũng sẽ giống nhau:

Cách dùng hàm Vlookup lồng nhau

Đôi khi, khi bảng chính và bảng tra cứu không có cột chung, bạn không thể sử dụng VLOOKUP theo cách thông thường. Tuy nhiên, có một bảng trung gian không chứa thông tin bạn đang tìm kiếm nhưng lại có cột chung với cả bảng chính và bảng tra cứu.

Ví dụ: Bạn có một bảng chính Main table chứa cột "SKU (new)" và bạn muốn trích xuất giá từ một bảng khác. Bạn có hai bảng tra cứu - bảng Lookup table 1 chứa mã "SKU (new)" và tên sản phẩm tương ứng, bảng Lookup table 2 chứa tên sản phẩm và giá cả, nhưng có mã "Old SKU".

 

Để kéo giá từ bảng Lookup table 2 đến bảng Main table, bạn phải sử dụng hàm VLOOKUP lồng nhau.

• Tạo công thức VLOOKUP tìm tên sản phẩm trong ”Lookup table 1“, sử dụng SKU New làm giá trị tra cứu:

=VLOOKUP(A2,New_SKU,2,FALSE)

Trường hợp ‘New_SKU’ là phạm vi được đặt tên cho $A:$B trong “Lookup table 1”, thì cột B có chứa tên sản phẩm (xin xem hình trên).

• Viết công thức để lấy giá từ "Lookup table 2", dựa vào tên sản phẩm. Để làm điều này, bạn sẽ kết hợp hai hàm VLOOKUP ở trên trong tiêu chí tra cứu:

=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

Trong đó “Price” là một phạm vi được đặt tên $A:$C trong bảng Lookup table 2 chứa giá.

Hình dưới đây cho thấy kết quả trả về bởi công thức vlookup lồng nhau của chúng ta:

Xem thêm: Hướng dẫn hàm VLOOKUP trong Excel cho người mới bắt đầu: Học với các ví dụ

Hàm VLOOKUP nâng cao - hàm tìm kiếm trả về nhiều giá trị trong Excel

Dưới đây chúng mình sẽ kết hợp một số hàm trong Excel để tạo ra một hàm tổng hợp có chức năng dò tìm và trả về nhiều giá trị. Lưu ý, sau khi nhập công thức, các bạn phải sử dụng tổ hợp phím CTRL + SHIFT + ENTER để nhập công thức mảng này.

Sử dụng VLOOKUP và INDIRECT để tự động kéo dữ liệu từ các trang tính khác nhau

Giả sử bạn có một vài báo cáo bán hàng khu vực cho cùng một sản phẩm ở cùng một định dạng và bạn muốn tìm số bán cho một khu vực nhất định:

Nếu bạn chỉ có một vài khu vực, bạn có thể sử dụng một công thức VLOOKUP khá đơn giản với một hàm IF để chọn trang cho vlookup:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

Trong đó:

• $D$2 là một ô có chứa ” Product Name” 
• IF($D3="FL", FL_Sales, CA_Sales): Nếu giá trị trong ô D3 là "FL", thì nó sẽ trả về FL_Sales. Nếu không, nó sẽ trả về CA_Sales
• Công thức VLOOKUP được áp dụng vào phạm vi dữ liệu tùy thuộc vào kết quả của hàm IF. Nếu giá trị trong ô D3 là "FL", nó sẽ sử dụng FL_Sales để tra cứu và trả về giá trị. Nếu giá trị không phải "FL", nó sẽ sử dụng CA_Sales để tra cứu và trả về giá trị tương ứng.

 

Tuy nhiên, nếu bạn có nhiều bảng tra cứu, hàm IF không phải là giải pháp lý tưởng. Thay vào đó, bạn có thể sử dụng hàm INDIRECT để trả về phạm vi tra cứu cần thiết.

Và đây là công thức kết hợp VLOOKUP và INDIRECT trong dò tìm nhiều sheet trong ví dụ trên:

=VLOOKUP($D$2,INDIRECT($D3&”_Sales”),2,FALSE)

Trong đó:

• $D$2 là ô có chứa tên sản phẩm, nó luôn luôn không thay đổi do các tham chiếu cột và cột là tuyệt đối.
• $D3 là ô chứa tên trạng thái đầu tiên, trong trường hợp này là FL.
• “_Sales” là phần chung của tên phạm vi, hoặc tên bảng. Kết nối với giá trị trong ô D3 nó tạo tên đầy đủ của dãy yêu cầu. 

Xem thêm: Hướng dẫn sử dụng hàm INDIRECT trong Excel, ứng dụng của hàm INDIRECT

Cách hoạt động của INDIRECT & VLOOKUP

Trước hết, hãy để tôi nhắc cho bạn cú pháp của hàm INDIRECT: 

=INDIRECT (ref_text, [a1])

Với công thức INDIRECT($D3&"_Sales"), chúng ta kết hợp nội dung ô D3 với "_Sales" để tạo ra tên của bảng cần tra cứu. Khi ô D3 có giá trị như "FL", công thức sẽ tạo tên "FL_Sales". 

Nghĩa là, nếu bạn có FL trong ô D3, công thức sẽ tìm kiếm trong bảng FL_Sales, nếu CA – thì trong bảng CA_Sales, v.v …

Kết quả được tạo ra bởi hàm VLOOKUP và INDIRECT của bạn sẽ giống như sau:

Nếu dữ liệu của bạn nằm trong các bảng tính khác nhau, bạn sẽ phải thêm một tên bảng tính trước vùng được đặt tên (WorkbookName! NamedRange), ví dụ:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

Chú ý: Nếu hàm INDIRECT đề cập đến một workbook khác, bảng tính đó phải được mở. Nếu bảng tính nguồn không mở, công thức INDIRECT của bạn sẽ trả về lỗi #REF!

Xem thêm: Hướng dẫn sử dụng hàm INDIRECT trong Excel và cách khắc phục một số lỗi thường gặp

Kết luận

Như vậy, bài viết này đã giúp các bạn tìm hiểu sâu hơn về hàm VLOOKUP nhiều giá trị để mở rộng kiến thức. Chúng mình còn có nhiều bài chia sẻ khác trong mục Blog của Gitiho về chủ đề Excel từ cơ bản đến nâng cao. Các bạn nhớ tham khảo để giúp quá trình làm việc với Excel trở nên hiệu quả hơn nhé.

Chúc các bạn học tập hiệu quả!