Lực td
Lực td
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 706 lượt xem

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí

Jan 06 2021

Trong bài viết này, Gitiho sẽ cùng các bạn 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 tiêu chí, điều kiện. Cuối cùng là sử dụng hàm VLOOKUP để tra cứu hai chiều nhé. 

Đăng ký ngay khoá Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

Sử dụng hàm VLOOKUP có điều kiện với nhiều tiêu chí khác nhau

Về tổng quan, hàm VLOOKUP được sinh ra để hỗ trợ việc tìm kiếm một giá trị nhất định trên một cơ sở dữ liệu.. Nhưng bất kì hàm nào cũng có điểm yếu và điểm mạnh, đối với VLOOKUP thì chỉ cho phép ta tìm kiếm một giá trị. Vậy chẳng hạn trong trường hợp muốn tìm nhiều điều kiện, chúng ta phải làm như thế nào?

Câu trả lời chính là sử dụng hàm VLOOKUP có điều kiện. Chúng ta sẽ tìm hiểu cách sử dụng chúng thông qua các ví dụ sau đây:

Ví dụ 1: Dùng VLOOKUP để tìm kiếm 2 tiêu chí khác nhau

Ở ví dụ sau này chúng ta sẽ có một bảng tính Excel về các đơn hàng, nhiệm vụ của bạn là tìm số lượng hàng (Quantity - Qty) dựa trên 2 tiêu chí cho sẵn là "Name" (tên) và Product (sản phẩm). Điều chúng ta cần lưu ý ở đây là có trường hợp một khách hàng lại đặt nhiều sản phẩm.

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

Chỉ một hàm VLOOKUP thông thường sẽ không thể nào áp dụng được trong trường hợp này bởi nó sẽ trả về giá trị mà nó tìm thấy đầu tiên và phù hợp theo chỉ định của người sử dụng. Trong trường hợp này chúng ta muốn biến số lượng sản phẩm trong danh mục "Sweets" được sắp xếp bởi khách hàng "Jeremy Smith" và sử dụng lệnh "=VLOOKUP(B1,$A$5:$C$14,3,FALSE)" thì kết quả đưa ra là "15" tương ứng với sản phẩm "Apples".

Để đơn giản hoá công việc này, Gitiho.com sẽ đưa ra cho bạn cách thức sau đây: Bạn sẽ cần thêm một cột phụ liên kết với tất cả các tiêu chí mà bạn yêu cầu (trong ví dụ này là hai cột Product và Customer). Điều quan trọng ở đây là bạn phải nhớ rằng cột liên kết luôn luôn phải là cột ngoài cùng trong phạm vi tìm kiếm của bạn vì đây là vị trí mà VLOOKUP luôn tìm kiếm giá trị tra cứu.

Sau khi cho thêm một cột liên kết phụ vào bàng, chúng ta dùng công thức =B2&C2 vào cột này. Hoặc bạn có thể sử dụng công thức =B2&” “&C2 trong trường hợp bạn cần tách các giá trị được nối bằng khoảng trắng để giúp cho dữ liệu dễ đọc hơn khi in ra bản cứng.

Tiếp đến ta sẽ sử dụng công thức VLOOKUP sau đây.

=VLOOKUP(“Jeremy Hill Sweets”,$A$5:$C$14,3,FALSE)

hoặc:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

Trong đó cột B1 chứa giá trị tra cứu (Lookup_value) và 3 là số cột chứa dữ liệu cần tìm kiếm (col_index_num).

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

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

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

Với trường hợp bạn cần phải cập nhật bảng tính chính với dữ liệu từ bản tính, trang tính khác thì một lần nữa việc kết nối các giá trị tra cứu trực tiếp vào bảng tính sẽ giúp đỡ bạn.

Tương tự như ví dụ trên, ta tiếp tục  thêm một cột phụ vào bảng tra cứu kết nối vào bên trái ngoài cùng của dãy tìm kiếm. Sau đó chúng ta sử dụng công thức VLOOKUP sau:

=VLOOKUP(B2&” “&C2,Orders!$A&$2:$B$2,4,FALSE).

Tại đây, cột B và C chứa "Customer" "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.

Để đơn giản hoá công thức này, các bạn sử dụng hàm VLOOKUP để tạo nên một vùng được đặt tên cho bảng tra cứu và để dễ đọc hơn.

 =VLOOKUP(B2&” “&C2,Orders,4,FALSE) 

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

Lưu ý:

Để sử dụng công thức này chính xác, cột bên trái ngoài cùng phải chứa đúng các giá trị ghép nối chính xác như trong tiêu chí tra cứu mà bạn đưa ra. Như trong ví dụ trên, chúng ta phải ghép các giá trị một khoảng trắng nhất định trong bảng tra cứu. Vậy nên 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)

Bạn cũng cần chú ý rằng hàm VLOOKUP chỉ cho phép giới hạn trong 255 ký tự, vậy bất kỳ giá trị nào có nhiều hơn 255 ký tự sẽ không thể tìm kiếm được. Vì vậy hãy nhớ điều kiện này và đưa ra tiêu chí tìm kiếm phù hợp nhé.

Xem thêm: Cách viết công thức VLOOKUP đơn giản, dễ hiểu qua các bước ví dụ

Cách sử dụng hàm VLOOKUP để lấy 2;3;4;... giá trị phù hợp

Hàm VLOOKUP chỉ có thể tìm được một giá trị phù hợp và giá trị đó phải là giá trị đầu tiên tìm được. Thế còn những giá trị xuất hiện với tần suất nhiều hơn và bạn muốn kéo tất cả các giá trị phù hợp thì cần phải làm gì? Thật ra cũng không quá phức tạp đâu. Và sau đây Gitiho sẽ hướng dẫn bạn một cách chi tiết.

Ví dụ, một bảng khách hàng nằm ở một cột riêng biệt và họ mua sản phẩm từ cột khác. Nhiệm vụ của bạn là tìm sản phẩm 2, 3, 4 do cùng một khách hàng chọn mua.

Lần này chúng ta sử dụng tiếp một cột phụ trước cột "Customer Name", tiếp theo là điền tên và điền số thứ tự. Như ví dụ dưới đây là "John Doe1" và "John Doe2". Hình ảnh dưới đây sử dụng hàm COUNTIF để xử lý tác vụ này (giả sử tên khách hàng nằm trong cột B). 

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

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

Sau đó bạn có thể dùng VLOOKUP để  tìm thứ tự tương ứng. Ví dụ như các trường hợp tra cứu sau đây.

- Sản phẩm thứ 2 mà khách hàng tên "Dan Brown" mua: 

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

- Sản phẩm thứ 3 mà khách hàng Dan Brown mua:

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

Ngoài ra bạn có thể điền một giá trị tham chiếu ô thay vì văn bản trong giá trị tra cứu như dưới đây.

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

Nếu như bạn lại muốn tìm kiếm thêm lần thứ 2 thì lần này chúng ta sẽ không cần phải tạo cột phụ nữa. Thay vào đó chúng ta sẽ dùng thêm một hàm VLOOKUP

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

Chúng ta hãy cùng phân tích kỹ hàm này.

- $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 Name" (tên khách hàng).

- Table 4 [Customer Name] - tra cứu cột trong bảng hoặc một phạm vi tra cứu nhất định.

- $C16 - ô dưới cùng bên trái trong bảng tra cứu của bạn.

Lưu ý: công thức này chỉ dùng được khi tìm thấy giá trị phù hợp thứ 2. Nếu như bạn cần tìm thêm giá trị khác thì chúng ta phải sử dụng những giải pháp trước đó.

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

Hàm VLOOKUP không thể đáp ứng được nhiệm vụ lấy được danh sách tất cả giá trị phù hợp bởi nó chỉ có thể trả một giá trị tại một thời điểm. Tới đây chúng ta sẽ phải sử dụng hàm INDEX để sử dụng trong nhiệm vụ này. Sau đây Gitiho.com sẽ hướng dẫn rõ hơn cho các bạn.

Cách lấy được tất cả giá trị trùng lăp trong phạm vi tra cứu

Như đã đề cập phía trên, tác vụ này không thể sử dụng hàm VLOOKUP. Thay vào đó chúng ta sẽ phải sử dụng công thức tương đối phức tạp hơn gồm các  hàm như INDEXSMALL và ROW.

Như công thức dưới đây sẽ tìm được tất cả các trường hợp thuộc ô F2 trong phạm vi tra cứu B2:B16 và trả về giá trị cho cột C:

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

Bạn có thể copy công thức này vào ô lân cận (như hình ảnh dưới đây là ô F4:F8). Hãy lưu ý rằng số ô mà bạn sao chép phải lớn hơn hoặc bằng số mục trùng lặp tối đa. Ngoài ra, hãy dùng tổ hợp Ctrl + Shift + Enter để nhập công thức một cách chính xác. 

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

Sau đây Gitiho sẽ phân tích công thức này cho bạn đọc nhằm có thể dễ hiểu hơn.

Phần 1: IF($F$2=B2:B16,ROW(C2:C16)-1,””)

Đầu tiên, $F$2=B2:B16 để so sánh giá trị ô F2 với mỗi giá trị trong dải B2:B16. Nếu như đã tìm thấy giá trị phù hợp thì tiếp theo ROW(C2:C16)-1 sẽ cho về số của hàng tương ứng (-1 để khẩu trừ dòng tiêu đề). Chẳng hạn giá trị so sánh không hợp thì hàm IF sẽ trả về một chuỗi rỗng.

Kết quả mà hàm IF cho ra sẽ là: {1, “”, 3, “”, 5, “”, “”, “”, “”, “”, “”,12, “”, “”, “”}

Phần 2: ROW()-3

ROW trong trường hợp này sẽ được dùng như một bộ đếm đệm thêm bởi công thức được copy vào ô F4:F9 Thêm -3 cho hàm trả 1 cho ô F4 (hàng 4 trừ 3), 2 cho ô F5 (hàng 5 trừ 3).

Phần 3: SMALL(IF($F$2=$B$2:$B$16,ROW($C$2:$C$16)-1,””),ROW()-3))

Giống như cái tên, hàm SMALL sẽ trả về giá trị nhỏ nhất thứ k trong bộ dữ liệu. Trong ví dụ này thì vị trí (từ nhỏ nhất) trở về được xác định bởi hàm ROW ở phần 2. Cho nên với ô F4, hàm SMALL ({array}, 1) trả về phần tử đầu tiên (nhỏ nhất) của mảng, tức là số 1. Còn ô F5 sẽ trả về phần tử nhỏ thứ 2 của mảng là 3.

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 này có tác dụng trả về giá trị một ô xác định trong mảng C2:C16. Đối với ô F4, để trả về Apples chúng ta sẽ dùng INDEX($C$2:$C$16,1). Tương tự như ô F5 sẽ trả về "Sweets" với INDEX($C$2:$C$16,3).

Phần 5IFERROR()

Sau cùng sẽ là hàm IFERROR để tránh việc ra kết quả N/A trong bảng tính của bạn khi số ô bạn sao chép công thức lớn hơn số xuất hiện trùng lặp của giá trị tra cứu.

Sử dụng VLOOKUP để tra cứu hai chiều trong Excel

Tìm kiếm hai chiều hay tra cứu ma trận hoặc tra cứu hai chiều được dùng để tìm kiếm trên cả hàng và cột. Hay cụ thể hơn là tìm kiếm một giá trị tại giao điểm của một hàng hoặc cột nhất định.

Sau đây là một bảng tính lượng hàng bán theo từng tháng (Monthly Sales) và nhiệm vụ của chúng ta là sử dụng VLOOKUP để tìm xem bao nhiêu sản phẩm "Lemons" (quả chanh) đã được đến tay khách hàng trong tháng 3.

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

Dưới đây là những lựa chọn để thực hiện tra cứu hai chiều. Bạn có thể chọn bất kỳ cách thức nào để phù hợp với tính chất công việc.

Cách 1: Sử dụng kết hợp hàm VLOOKUP & hàm MATCH

Hai hàm này có thể sử dụng để liên kết để tham chiếu chéo hai trường trong bảng tính cũng như cơ sở dữ liệu. Như ví dụ đây là Product (hàng) và Month (cột).

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

Hàm VLOOKUP này để tìm kiếm kết hợp chính xác ô "Lemons"  từ các ô A2 đến I9. Nhưng cái khó  nằm ở chỗ ta không biết chính xác cột bán hàng của tháng Ba (March) khiến cho không thể  cung cấp số cột trong tham số thứ ba trong công thức VLOOKUP. Lúc này chúng ta sẽ sử dụng chức năng MATCH để tìm cột đó.

Trong đó MATCH(“Mar”,$A$1:$I$1,0) sẽ gồm những phần như sau:

- Tra cứu "Mar" (tham số tìm kiếm).

-  Tra cứu trong ô A1 đến I1  (mảng tham số cần tìm kiếm).

- Hàm MATCH sẽ tìm giá trị đầu tiên chính xác bằng với giá trị tra cứu bằng cách sử dụng "0" trong tham số thứ 3 nhằm trả lại kết hợp chính xác loại tham số tìm kiếm mảng. Điều này khá tương tự tham số FALSE trong VLOOKUP.

Cách 2: Sử dụng hàm SUMPRODUCT.

Hàm SUMPRODUCT sẽ có chức năng nhân các 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)

Với hàm INDEX và MATCH sẽ có công thức là: 

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

Tự động kéo các dữ liệu từ nhiều trang tính khác nhau chỉ bằng VLOOKUP và INDIRECT.

Cách này thường được sử dụng khi có dữ liệu ở cùng một định dạng trên nhiều bảng tính khác nhau khiến cho bạn tốn thời gian khi phải kéo dữ liệu phù hợp từ một trang tính cụ thể tuỳ thuộc vào giá trị được nhập vào ô. Tức có nghĩa là "kéo các dữ liệu từ nhiều trang tính khác nhau" là tất cả những trang tính sẽ hiện trên một bảng tính.

Ví dụ minh hoạ dưới đây sẽ giúp chúng ta dễ hình dung hơn

Bạn có một vài bản báo cáo bán hàng cho cùng một sản phẩm ở cùng một định dạng và yêu cầu bạn phải tìm số bán cho một khu vực nhất định.

Nếu như số khu vực tương đối ít, chúng ta sẽ sử dụng một công thức VLOOKUP cùng hàm IF để chọn trang cho VLOOKUP.

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

Chúng ta hãy cùng phân tích công thức này:

- $D$2 là ô chứa  nội dung "Product Name"  (tên sản phẩm). Ở đây ta cần phải lưu ý khi sử dụng ô tham chiếu ô tuyệt đối trong trường hợp này để ngăn cho giá trị thay đổi khi sao chép công thức sang ô khác.

- $D3 là một ô chứa tên trạng thái (sử dụng tham chiếu hàng tương đối và cột tuyệt đối nhằm 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 trước cho bản báo cáo bán hàng tương ứng. Thật ra việc sử dụng tên bảng tính và dải ô thông thường như ‘FL Sheet’! $A$3:$B$10 cũng không có gì sai, nhưng sử dụng các phạm vi được đặt tên sẽ tiết kiệm thời gian hơn.

Tổng hợp hàm VLOOKUP để tra cứu hai chiều VLOOKUP nâng cao và lồng VLOOKUP theo nhiều tiêu chí.

Thế nhưng hàm IF sẽ không phải lựa chọn thích hợp nếu như có nhiều bảng tính cùng lúc. Lúc này hàm INDIRECT sẽ phát huy hiệu quả để trả về phạm vi tra cứu theo yêu cầu.

Mục đích của sử dụng INDIRECT là để gián tiếp tham khảo một ô. Việc tiếp theo chúng ta cần làm là thay thế câu lệnh IF bằng tham chiếu INDIRECT như trên. Còn dưới đây là công thức để kết hợp VLOOKUP và INDIRECT.

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

Công thức này bao gồm:

$D$2 là ô chứa tên sản phẩm và nó sẽ không bảo giờ thay đổi bởi các tham chiếu cột và cột này là tuyệt đối.

- $D3 là ô chứa tên trạng thái đầu tiên 

“_Sales” là phần chung của tên bảng hoặc tên phạm vi. Liên kết với giá trị trong ô D3 sẽ tạo tên đầy đủ của dãy yêu cầu.

Nếu như bạn chưa nắm rõ về INDIRECT của Excel, sau đây Gitiho.com sẽ hướng dẫn sơ qua cho bạn đọc.

Cách thức hoạt động của INDIRECT và hàm VLOOKUP.

Đầu tiên, cú pháp của INDIRECT là: =INDIRECT (ref_text, [a1])

Thông số đầu tiên có thể là tham chiếu ô A1 hoặc R1C1 hoặc tên dải ô hay một chuỗi văn bản. Tiếp đến là xác định loại tham chiếu được chứa trong ref_text - theo kiểu A1  (TRUE hoặc bỏ qua) hoặc kiểu R1C1 (FALSE). Đây là ví dụ A1 trong trường hợp này. Vì vậy ta hoàn toàn có thể bỏ qua tham số thứ 2 và chỉ tập trung cho thông số đầu.

Từ đó chúng ta sẽ quay trở lại với ví dụ bảng tính trước đó của chúng ta. Vấn đề vẫn nằm ở việc mỗi bản báo cáo là một bảng riêng biệt. Để cho công thức có thể sử dụng trơn tru ta sẽ phải đặt tên cho các ô, bảng hoặc dãy của bạn. Để dễ phân biệt thì chúng ta nên để có một ký tự chung bất kỳ. Ví dụ ta đặt tên FL_Sales, CA_Sales, TX_Sales,...Tất cả đều có phần Sales.

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

Qua bài viết trên, chúng ta đã có thể hiểu được về chức năng của hàm VLOOKUP để tra cứu hai chiều, cũng như nắm được cụ thể về cú pháp của hàm và cách và lồng VLOOKUP theo nhiều tiêu chí. Hi vọng các bạn đã hiểu rõ hơn về hàm VLOOKUP và có thể ứng dụng nó tốt nhất trong công việc của mình. 

Để nắm được những kiến thức cơ bản nhất về Excel cũng như nâng cao kỹ năng Excel của mình hơn nữa, các bạn hãy tham gia khoá học Excel của Gitiho để nắm được tất cả các công cụ của Excel và ứng dụng chúng một cách hiệu quả trong công việc nhé. 

Tham khảo các bài viết khác tại đây:

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế






Tài liệu kèm theo bài viết

Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản

Thảo luận 0 câu trả lời
Lượt xem 706 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội