Hàm VLOOKUP nâng cao, lồng vlookup với nhiều tiêu chí, tra cứu hai chiều
Trong bài viết này chúng ta sẽ cùng tìm hiểu về các công thức Vlookup nâng cao, cách sử dụng hàm VLookup lồng các hàm khác để tìm kiếm theo nhiều điều kiện, cách tra cứu hai chiều với hàm VLOOKUP. Hãy cùng tìm hiểu nào:
Xem nhanh
Excel 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? Ta có 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 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. Vì vậy, nếu bạn muốn biết số lượng “Sweets” được sắp xếp bởi “Jeremy Hill” và viết công thức =VLOOKUP(B1,$A$5:$C$14,3,FALSE), 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 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 . Hãy nhớ rằng cột liên kết luôn luôn là cột ngoài cùng bên trái trong phạm vi tra cứu của bạn vì đây là nơi mà VLOOKUP luôn tìm kiếm giá trị tra cứu.
Vì vậy, bạn thêm một cột phụ vào bảng của bạn và sao chép một công thức như thế này =B2&C2 qua cột đó (hoặc =B2&” “&C2 nếu bạn muốn tách các giá trị được nối bằng khoảng trắng để làm cho dữ liệu dễ đọc hơn).
Và sau đó, bạn có thể sử dụng một công thức VLOOKUP đơn giản như sau:
=VLOOKUP(“Jeremy Hill Sweets”,$A$5:$C$14,3,FALSE)
hoặc là
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
Trong đó B1 chứa giá trị tra cứu (lookup_value) và 3 là số cột chứa dữ liệu bạn muốn tìm (col_index_num).
Ví dụ 2. 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.
Giống ví dụ trên, bạn cần thêm một cột phụ vào bảng tra cứu với các giá trị tra cứu kết nối và nên tạo ở vị trí ngoài cùng bên trái của dãy tìm kiếm.
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$2,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$2 là bảng tra cứu của bạn trong một bảng tính khác.
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, 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. Như bạn thấy trong hình trên, chúng ta ghép các giá trị bằng một khoảng trắng trong bảng tra cứu, vì vậy chúng ta phải làm tương tự trong các tiêu chí tra cứu của công thức VLOOKUP (B2 & “” & C2).
Ngoài ra, hãy nhớ VLOOKUP giới hạn trong 255 ký tự . Hàm VLOOKUP không thể tìm kiếm giá trị tra cứu có chứa hơn 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 VLOOKUP để lấy 2, 3, 4,.. vv giá trị phù hợp:
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? Hoặc nhiều hơn nữa, nếu bạn muốn kéo tất cả các giá trị phù hợp? Nghe có vẻ phức tạp, nhưng vẫn có cách thực hiện.
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 đơ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 Công thức COUNTIF dưới đây là mánh nhỏ để làm việc này (giả sử tên khách hàng Nằm trong cột B):
=B2&COUNTIF($B$2:B2,B2)
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)
- Tìm sản phẩm thứ ba Dan Brown mua:
=VLOOKUP(“Dan Brown3”,$A$2:$C$16,3,FALSE)
Đương nhiên, bạn có thể nhập một tham chiếu ô 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:
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:
- $F$2 – ô với tên khách hàng (là hằng số, lưu ý các tham chiếu ô tuyệt đối);
- $B$ – cột “Customer Names”;
- Table 4 [Customer Name] – tra cứu cột trong bảng hoặc một phạm vi tra cứu;
- $C16 – ô cuối cùng (dưới cùng bên trái) trong bảng tra cứu của bạn.
Tham khảo: Phát hiện và xử lý lỗi công thức trong Excel
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.
Làm thế nào để có được tất cả các giá trị trùng lặp trong phạm vi tra cứu
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.
Tham khảo:
Hướng dẫn sử dụng hàm Large và hàm Small trong Excel 2007, 2010, 2013, 2016, 2019
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 dưới đây vào một số ô lân cận, ví dụ: các ô F4:F8 như thể hiện trong ảnh chụp màn hình dưới đây. 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:
Phần 1 .IF($F$2=B2:B16,ROW(C2:C16)-1,””)
$F$2=B2:B16– so sánh giá trị trong ô F2 với mỗi giá trị trong dải B2: B16. Nếu tìm thấy giá trị phù hợp, ROW(C2:C16)-1 trả lại số của hàng tương ứng (-1 được sử dụng để khấu trừ dòng tiêu đề). Nếu các giá trị so sánh không khớp, hàm IF trả về một chuỗi rỗng.
Kết quả của hàm IF là mảng sau đây: {1, “”, 3, “”, 5, “”, “”, “”, “”, “”, “”,12, “”, “”, “”}
Phần 2 .ROW()-3
Trong trường hợp này, hàm ROW hoạt động như một bộ đếm thêm. Vì công thức được sao chép vào ô F4: F9, ta thêm -3 cho hàm trả 1 cho ô F4 (hàng 4 trừ 3), 2 cho ô F5 (hàng 5 trừ 3), vv
Phần 3 .SMALL(IF($F$2=$B$2:$B$16,ROW($C$2:$C$16)-1,””),ROW()-3))
Hàm SMALL trả về giá trị nhỏ nhất thứ k trong một bộ dữ liệu. Trong trường hợp của này, vị trí (từ nhỏ nhất) trở về được xác định bởi hàm ROW (Phần 2). Vì vậy, đối với ô F4, hàm SMALL ({array}, 1) trả về phần tử trước tiên (nhỏ nhất) của mảng, tức là 1. Đối với ô F5, nó trả về phần tử nhỏ thứ 2 của mảng, là 3, trên.
Phần 4 .INDEX($C$2:$C$16, SMALL(IF($F$2=$B$2:$B$16, ROW($C$2:$C$16)-1,””), ROW()-3))
Hàm INDEX chỉ đơn giản trả về giá trị của một ô xác định trong mảng C2: C16. Đối với ô F4, INDEX($C$2:$C$16,1) trả về “Apples”; Cho tế bào F5, INDEX($C$2:$C$16,3) trả về “Sweets”, v.v.
Phần 5 .IFERROR()
Cuối cùng, chúng ta lồng công thức trong hàm IFERROR bởi vì chắc bạn không muốn thấy thông báo N / A trong bảng tính của bạn, khi số ô mà bạn đã sao chép công thức lớn hơn số lần xuất hiện trùng lặp của giá trị tra cứu.
Làm thế nào để làm tra cứu hai chiều trong Excel
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)
Công thức ở trên là một hàm Vlookup thông thường tìm kiếm kết hợp chính xác của “Lemons” trong các ô từ A2 đến I9. Tuy nhiên, vì bạn không biết chính xác cột bán hàng của tháng ba (Mar), bạn không thể cung cấp số cột trong tham số thứ ba của công thức VLOOKUP của bạn. Thay vào đó, bạn sử dụng chức năng MATCH để tìm cột đó.
Xem thêm: Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel
Hàm MATCH(“Mar”,$A$1:$I$1,0) có nghĩa là:
- Tra cứu “Mar” (tham số tìm kiếm).
- Tìm kiếm trong ô A1 đến I1 (mảng tham số tìm kiếm).
- Trả lại kết hợp chính xác (loại tham số tìm kiếm mảng). Bằng cách sử dụng “0” trong tham số thứ 3, hàm MATCH tìm giá trị đầu tiên chính xác bằng với giá trị tra cứu. Bạn có thể coi nó như một tham số False trong VLOOKUP.
Đâ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.
Hàm SUMPRODUCT
Hàm SUMPRODUCT nhân các thành phần trong các mảng nhất định và trả về tổng của chúng:
=SUMPRODUCT(($A$2:$A$9=”Lemons”)*($A$1:$I$1=”Mar”),$A$2:$I$9)
Hàm INDEX & MATCH
Công thức là:
=INDEX($A$2:$I$9, MATCH(“Lemons”,$A$2:$A$9,0), MATCH(“Mar”,$A$1:$I$1,0))
Xem thêm: Hàm SUMPRODUCT trong Excel và một số ví vụ công 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:
1. Chọn bảng của bạn, chuyển sang tab Formulas và nhấp vào Create from Selection.
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 .
3. Trong bất kỳ ô rỗng nào, gõ =row_value column_value, ví dụ =Lemons Mar,hoặc ngược lại = Mar Lemons.
Hãy nhớ tách giá trị hàng và giá trị cột của bạn với khoảng trắng, trong trường hợp này chúng sẽ hoạt động như một toán tử giao nhau.
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.
- Nhấn phím Entervà 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:
Làm thế nào để làm nhiều Vlookups trong một công thức (Vlookup lồng nhau)
Đôi khi bảng chính và bảng tra cứu 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 bảng chính của bạn và một chung với bảng tra cứu của bạn.
Hãy xem xét ví dụ sau đây. Bạn có bảng chính với một cột đơn, New SKU, và bạn cần phải kéo giá tương ứng từ một bảng khác. Ngoài ra, bạn có 2 bảng tra cứu – mã đầu tiên chứa mã số New SKU và tên sản phẩm tương tự, cái thứ hai liệt kê tên sản phẩm, giá cả, nhưng có Mã SKU old.
Để kéo giá từ bảng Lookup 2 đến bảng Chính, bạn phải thực hiện cái gọi là vlookup đôi của Excel, hoặc 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 “ và ” Lookup table 2″ , 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 để kéo giá từ ” Lookup table 2 “, dựa vào tên của sản phẩm bằng cách 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 tra cứu 2, và 3 là cột C 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:
Hàm VLOOKUP nâng cao, tra cứu trả về nhiều giá trị
Dưới đây Blog Học Excel Online 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ị. Video và bài thuyết trình sau đây sẽ giúp các bạn hiểu được công thức mảng tương đối phức tạp này. 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.
Ứng dụng của cách tra cứu kiểu này là tạo ra phiếu xuất kho, đã được trình bày trong video ở bài viết này
Download tài liệu kèm theo video
Sử dụng VLOOKUP và INDIRECT để tự động kéo dữ liệu từ các trang tính khác nhau
Để bắt đầu, hãy xác định chính xác “kéo dữ liệu động từ các trang tính khác nhau” có nghĩa là để đảm bảo rằng chúng nằm trên cùng một trang.
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 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”. Lưu ý rằng chúng ta sử dụng tham chiếu ô tuyệt đối trong trường hợp này để ngăn không cho giá trị tra cứu thay đổi khi sao chép công thức vào các ô khác.
- $D3 là một ô với tên trạng thái (sử dụng tham chiếu cột tuyệt đối và hàng tương đối nếu bạn dự định sao chép công thức vào các ô khác trong cùng một cột).
- FL_Sales và CA_Sales là các tên bảng, hoặc các phạm vi được đặt tên, cho các báo cáo bán hàng tương ứng. Bạn cũng có thể sử dụng tên bảng tính và dải ô thông thường, ví dụ như ‘FL Sheet’! $A$3:$B$10, nhưng sử dụng các phạm vi được đặt tên thuận tiện hơn nhiều.
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.
Hàm INDIRECT được sử dụng để gián tiếp tham khảo một ô, và đây chính xác là điều chúng ta cần lúc này. Vì vậy, hãy tiếp tục và thay thế câu lệnh IF bằng tham chiếu INDIRECT theo công thức trên. Và đây là công thức khi kết kết hợp VLOOKUP và INDIRECT trong ví dụ:
=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. Dưới đây tôi sẽ cung cấp một số chi tiết cụ thể hơn cho những người không có nhiều kinh nghiệm với hàm INDIRECT của Excel.\
INDIRECT & VLOOKUP – cách hoạt động
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])
Tham số đầu tiên có thể là tham chiếu ô theo kiểu A1 hoặc kiểu R1C1, tên dải ô, hoặc một chuỗi văn bản. Thông số thứ hai xác định loại tham chiếu được chứa trong ref_text – kiểu A1 (TRUE hoặc bỏ qua) hoặc kiểu R1C1 (FALSE). Đó là A1 trong trường hợp của chúng ta, vì vậy chúng ta có thể bỏ qua tham số thứ hai và chỉ tập trung vào cái đầu tiên.
Bây giờ, hãy trở lại với báo cáo bán hàng của chúng ta. Như bạn thấy, mỗi báo cáo là một bảng riêng biệt ở trong một trang riêng biệt. Để công thức hoạt động, bạn cần phải đặt tên cho các bảng hoặc dãy của bạn, và tất cả các tên nên có một phần chung. Ví dụ: báo cáo bán hàng của ta có tên: CA_Sales, FL_Sales, TX_Sales vv Như bạn thấy, luôn có phần _Sales.
Vì vậy, Hàm INDIRECT INDIRECT ($D3& “_ Sales”) kết nối giá trị trong cột D với từ _Sales (với một gạch dưới), và cho hàm VLOOKUP biết chính xác bảng để tra cứu. 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 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!.
Để nâng cao kiến thức Excel, bạn hãy tham gia ngay khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Trong khóa học này bạn sẽ được tìm hiểu cách sử dụng các công cụ: conditional formatting, data validation, pivot table… để ứng dụng vào trong công việc. Ngoài ra bạn còn biết cách sử dụng các hàm trong excel, từ các hàm cơ bản cho tới các hàm nâng cao, phức tạp. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online
Khóa học mới xuất bản
