Hàm Vlookup nhiều điều kiện trong Excel là hàm tìm kiếm kiếm trá trị vào chuỗi với đầy đủ yêu cầu cao cấp mà hàm vlookup thông thường không xử trí được. Nó đỡ đần ta dò tìm dữ liệu theo cột với điều kiện dò kiếm tìm được phối kết hợp từ những cột. Trong nội dung bài viết này, tmec.edu.vncoban.com sẽ gợi ý bạn sử dụng hàm Vlookup nhiều điều kiện qua các ví dụ nắm thể, thực tiễn và dễ hiểu.
Bạn đang xem: Hàm vlookup với nhiều điều kiện
Hàm vlookup các điều kiện thực chất là hàm Vlookup thông thường, nhưng để thực hiện được thì ta cần kết hợp một số hàm khác nhằm biến hóa điều kiện dò tìm sao cho hàm Vlookup hiểu và trả về tác dụng đúng.
1. Tác dụng của hàm Vlookup vào Excel.
Trong Excel chúng ta sử dụng hàm Vlookup để search kiếm tài liệu trong bảng hoặc một phạm vi theo cột vào một bảng dò search đã quan niệm trước. Như vậy, công dụng chính của hàm Vlookup là dùng để tìm kiếm quý giá trong một bảng báo giá trị mang đến trước.
2. Cú pháp hàm Vlookup vào excel.
VLOOKUP(LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,
3. Hàm Vlookup những điều kiện.
Từ phương pháp trên, ta hoàn toàn có thể thấy được hàm Vlookup thuông hay chỉ tìm tìm kiếm được với điều kiện dò tìm là 1 ô tham chiếu, một giá bán trị, hoặc một chuỗi văn bản. Vậy nếu đk dò tìm nằm ở vị trí nhiều ô hoặc từ không ít chuỗi văn bạn dạng trở lên thì chắc chắn là ta không thể sử dụng hàm Vlookup thông thường được. Ở đây, tmec.edu.vncoban.com sẽ giới thiệu cho bạn 2 phương pháp để tìm tìm với hàm vlookup những điều kiện.
Cách 1: sử dụng cột phụ.
Cách 2: Dùng cách làm mảng.
Ưu điểm yếu kém của 2 bí quyết dùng hàm Vlookup nhiều điều kiện trong Excel.
Sử dụng cột phụ:
Ưu điểm: Đơn giản, dễ sử dụng và dễ dàng áp dụng.Nhược điểm: làm cho tăng lượng tài liệu và tra cứu kiếm bởi việc tạo thêm cột phụ.Sử dụng cách làm mảng:
Ưu điểm: Không làm tăng lượng dữ liệu, không khiến dư thừa.Nhược điểm: khó dùng, khó nhớ, phương pháp dài, trừu tượng.4. Hàm Vlookup nhiều đk sử dụng cột phụ.
4.1 cần sử dụng hàm Vlookup nhiều điều kiện sử dụng cột phụ.
Cách đơn giản nhất nhằm tìm tìm trong Excel với nhiều điều kiện là sử dụng cột phụ. Từ khá nhiều điều khiếu nại mình tổng phù hợp lại vào 1 cột mới và dùng hàm Vlookup để kiếm tìm kiếm với điều kiện dò search là cột ta vừa tạo.
Tùy vào cụ thể từng yêu cầu rõ ràng mà ta vẫn ghép những điều kiện bằng các cách khác nhau, nhưng thường thì Cột phụ đang được tạo thành bằng những ghép các điều kiện bằng kí hiệu “&”.
VD: A1 cất giá trị là: NV. B1 đựng giá trị là 10.
=>Ta ghép điều kiện bằng cách: A1&B1 kết quả trả về vẫn là: NV10
Để dễ hình dung hơn về cách sử dụng hàm Vloolup với cột phụ họ cùng tò mò một vài lấy ví dụ minh họa.
4.2 Ví dụ sử dụng hàm Vlookup nhiều đk bằng cột phụ.
VD: chúng ta có một list sản lượng cung cấp cho từng sản phẩm, từng ca, từng nhân viên. Làm cố kỉnh nào để biết được sản lượng của 1 mặt hàng nào kia trong từng ca theo từng nhân viên cấp dưới là bao nhiêu?
Sử dụng cột phụ nhằm tìm kiếm.
Hình 1: Hàm Vlookup nhiều điều kiện sử dụng cột phụ.
Với câu hỏi tìm kiếm tài liệu theo sản phẩm ngang vào Excel chắc chắn bạn sẽ nghĩ ngay lập tức đến hàm Vlookup, nhưng tại đây ta phải tìm sản lượng của từng sản phẩm trong từng ca theo từng nhân viên (3 điều kiện) nhưng mà hàm vlookup thông hay lại chỉ sử dụng được với một điều kiện.
Nên ta cần biến hóa điều kiện đầu vào từ 3 đk thành một điểm kiện bằng cách tạo ra một cột phụ mới từ các việc ghép cột Sản phẩm, cột Ca cùng cột Nhân viên.
Các cách thực hiện:
B1: tạo thành cột phụ.
B2: Viết hàm với điều kiện tìm tìm là cột phụ vừa tạo.
Chi tiết những bước:
B1: sản xuất cột phụ:
Ta sản xuất thêm cột mới, cột này đứng ngơi nghỉ trước cột thành phầm và được chế tạo ra bằng cách ghép cột Sản phẩm, cột Ca cùng cột Nhân viên.
Công thức ghép:
Với hàng đầu tiền thì cách làm sẽ là: B5&C5&D5
Sau lúc tạo công thức cho số 1 ta copy bí quyết đó cho các hàng tiếp theo sau để xong xuôi cột.
Sau khi chấm dứt thì cột phụ sẽ có được dạng như hình sau:
Hàm vlookup những điều kiện
Hình 2: Hàm Vlookup sử dụng cột phụ.
Bước 2: Viết cách làm tìm kiếm với cột phụ vừa tạo.
Để kiếm tìm kiếm sản lượng của một sản phẩm theo ca của từng nhân viên tại ô I8 ta nhập công thức: =VLOOKUP(I5&I6&I7,$B$5:$F$9,5,0)
Trong đó:
I5&I6&I7: Là giá bán trị buộc phải đối chiếu.B5:F9: Là bảng dò tìm bao hàm cả cột phụ5: Số đồ vật tự cột dữ liệu trên bảng dò tìm.0: thứ hạng tìm kiếm chính xác.Quan liền kề hình dưới nhằm hiểu hơn về các thành phần của công thức bọn họ vừa tạo.
Hàm vlookup những điều kiện
Hình 3: Hàm Vlookup sử dụng cột phụ.
Nếu mong mỏi tìm sản lượng của sản phẩm khác hoặc ca khác với nhân viên cấp dưới khác bạn chỉ cần nhập sản phẩm, ca và nhân viên cần tra cứu vào bảng kiếm tìm kiếm như trên hình cơ mà không đề xuất tạo lại công thức.
4.3 Những xem xét khi thực hiện hàm Vlookup nhiều điều kiện bằng cột phụ.
Về bản chất: Hàm Vlookup nhiều đk khi sử dụng cột phụ sẽ vươn lên là hàm vlookup 1 đk thông thường.
Cột phụ được tạo nên phải được đặt tại đầu bảng dò tìm.
Khi tạo cột phụ ta yêu cầu ghép những cột lại theo đồ vật tự như là với vật dụng tự của đk dò tìm.
5. Hàm Vlookup nhiều điều kiện sử dụng bí quyết mảng.
Công thức mảng là 1 trong những cách tính nâng cao trong Excel mà chưa phải ai học cũng chăm chú tới nó. Chắc chắn hẳn, bên trên 90% chúng ta đọc nội dung bài viết này sẽ không biết tới việc thực hiện công thức mảng để tìm kiếm với nhiều điều kiện. Bởi phần nhiều mọi fan ít biết tới mảng trong Excel và biện pháp này tính này khá phức hợp với những bạn chưa thạo Excel. đề xuất các bạn có thể tìm kiếm bằng phương pháp tạo cột phụ phía trên.
5.1 phương pháp mảng là gì?
Mảng là một hàng giá trị hay một cột giá trị hoặc là 1 trong kết phù hợp các hàng và cột giá trị. (ví dụ: 1,2,3,4)
Công thức mảng là bí quyết được bao vì cặp vệt ngoặc nhọn vày excel tự động hóa thêm vào sau khi ngừng nhập công thức. Phương pháp mảng là công thức hoàn toàn có thể thực hiện các phép tính so với một hoặc nhiều mục vào mảng. Cách làm mảng có thể trả về nhiều tác dụng hoặc một công dụng duy nhất.
Công thức mảng bao hàm nhiều ô được call là cách làm đa ô và bí quyết mảng trong một ô độc nhất vô nhị được hotline là công thức đơn ô.
Nhập bí quyết mảng:
Chọn ô hoặc vùng ô yêu cầu nhập công thức. Nhập công thức yêu cầu tính toán.Nhấn tổ hợp phím Ctrl + Shift + Enter.5.2 giải pháp dùng hàm Vlookup nhiều đk sử dụng công thức mảng.
Khi sử dụng công thức mảng chúng ta sẽ không hẳn tạo thêm cột, không có tác dụng tăng lượng dữ liệu và search kiếm.
Để tìm kiếm kiếm ta phối hợp sử dụng hàm Vlookup với hàm Choose. Và phương pháp mảng được lồng vào hàm Choose.
5.3 ví dụ như về hàm Vlookup nhiều điều kiện sử dụng phương pháp mảng.
Như ví dụ trên ta liên tiếp tìm tìm sản lượng của một thành phầm theo từng ca.
Để kiếm tìm kiếm sản lượng của một mặt hàng theo ca tại ô I8 ta nhập công thức: =VLOOKUP(I5&I6&I7,CHOOSE(1,2,C5:C9&D5:D9&E5:E9,F5:F9),2,0) và nhấn Ctrl + Shift + Enter.
Trong đó:
I5&I6&I7: Là kết hợp 3 đk cần tìm.CHOOSE(1,2,C5:C9&D5:D9&E5:E9,F5:F9): Là bảng dò tìm, bảng này được tạo từ một mảng 2d có 2 cột.Cột 1: Cột tham chiều.Cột 2: Cột quý hiếm trả về.2: Số đồ vật tự cột dữ liệu trên bảng dò tìm.0: dạng hình tìm kiếm bao gồm xác.
Hàm vlookup những điều kiện.
Hình 4: tìm kiếm bằng công thức mảng.
Đọc đến đây chắc hẳn nhiều bạn vẫn chưa chắc chắn được về hàm Choose nhưng mà ta đã sử dụng.
Các bạn cùng quan gần kề hình bên dưới để hiểu hơn về hàm Choose.
Kết trái trả về của hàm CHOOSE vào Excel
Hình 5: quý giá trả về của hàm Choose vào Excel.
Giải mê thích công thức: = CHOOSE(1,2,C5:C9&D5:D9&E5:E9,F5:F9)
Trong đó:
1,2: bí quyết này đang trả về 1 mảng 2d gồm 2 cột. Cột 1 được rước ở tức thì sau vết phẩy lắp thêm nhất, cột 2 được đem ở sau lốt phẩy sản phẩm 2.C5:C9&D5:D9&E5:E9: quý hiếm trả về là 1 trong cột, các giá trị trong cột này được ghép từ bỏ cột Ca, cột thành phầm và cột Nhân viên.F5:F9: Là cột sản lượng.Chú ý: Hình 5 chỉ để các bạn hiểu rộng về cách hoạt động vui chơi của hàm CHOOSE trong Excel, khi sử dụng với hàm Vlookup các bạn chỉ việc viết hàm CHOOSE lồng trong hàm Vlookup như vào hình 4.
5.4 chú ý khi thực hiện hàm Vlookup nhiều đk bằng phương pháp mảng.
Khi nhập ngừng công thức các bạn nhớ nhấn tổ hợp phím Ctrl + Shift + Enter.
Dấu ngoặc nhọn bao lại công thức mảng là do excel auto thêm vào sau cùng khi dứt nhập công thức.
Hàm Vlookup nhiều đk dùng phương pháp mảng tương đối khó cần sử dụng nhưng nếu như bạn thành thuần thục nó sẽ hỗ trợ bạn tương đối nhiều trong câu hỏi dò tìm và thống kê.
Nếu chúng ta không đích thực thành thạo thì nên cần bỏ qua biện pháp này để tránh viết sai bí quyết dẫn đế tính toán sai.
Tổng kết.
Hàm Vlookup các điều kiện thực chất khởi hành từ hàm Vlookup thường thì và phối hợp 1 số hàm hoặc một số phương pháp để xử lý biến hóa từ nhiều đk về thành một điểm kiện. Trên phía trên tmec.edu.vncoban.com đã giữ hộ tới các bạn 2 bí quyết sử dụng hàm Vlookup những điều kiện trong Excel. ước ao rằng nội dung bài viết có thể giúp cho bạn. Chúc chúng ta thành công!
Gợi ý học tập mở rộng.
Trọn bộ khoá học tập Excel cơ phiên bản miễn phí: Học Excel cơ bản
Hàm VLOOKUP cơ bản trong Excel.Hàm VLOOKUP nâng cấp trong Excel.Hàm VLOOKUP 2 đk trong Excel.Hàm VLOOKUP kết hợp hàm IF vào Excel.Hàm VLOOKUP ngược, giải pháp dùng hàm VLOOKUP ngược vào Excel.Những lỗi thường gặp trong hàm Vlookup.
Sau khi đã biết cách sử dụng hàm Vlookup, bạn sẽ tương đối tự tin đi dò tìm kiếm bảng tính. Phần đa bước sau đây để giúp bạn còn tự tin không dừng lại ở đó khi thạo hàm vlookup bao gồm điều kiện. Trường hợp bạn chưa chắc chắn được VLOOKUP cơ bạn dạng thế nào, bạn cũng có thể theo dõi video clip này trước lúc đọc tiếp
Dưới đấy là bảng tính Excel minh hoạ đến ví dụ này. Bạn cũng có thể thực hành trực tiếp ở bảng tính Excel này. Như thế nào cùng bước đầu tìm hiểu hàm vlookup gồm điều kiện thôi!
Hãy bước đầu bằng ví dụ sau đây (Các chúng ta cũng có thể tìm thấy VD này vào Sheet1 làm việc bảng tính Excel phía trên)
Ở đây, nhằm tra cứu được giá trị sinh hoạt Cột C mà đk là Bb thì bọn họ cần thêm 1 Cột phụ như ở C9:C15. Tuy thế khi bọn họ không được phép thay đổi cấu trúc của bảng tính, thì chúng ta không thể áp dụng cách này. Làm thế nào để hoàn toàn có thể không biến đổi cấu trúc của bảng tính mà vẫn hoàn toàn có thể thực hiện nay được vấn đề này? bạn cũng có thể chuyển sang Sheet2.
Sử dụng VLOOKUP cùng rất CHOOSE và bí quyết mảng để tra cứu vãn dữ liệu dựa vào nhiều cột:
Trong Sheet2, chúng ta sử dụng công thức mảng tiếp sau đây tại ô F2 để có thể tra cứu giúp được dữ liệu. Để nhập bí quyết này, các bạn nhớ dùng tổ hợp phím CTRL + SHIFT + ENTER
=VLOOKUP(E2,CHOOSE(1,2,$A$2:$A$7&$B$2:$B$7,$C$2:$C$7),2,0)
Ở vào công thức, những phần được tô màu sắc là phần các chúng ta cũng có thể thay thay đổi tuỳ theo yêu cầu sử dụng. Để đọc được công thức, bạn có thể theo dõi ảnh sau thuộc với mỗi bước của hàm CHOOSE.
Hàm CHOOSE thực hiện trong bài bác này của chúng ta là hàm mảng phải nếu bạn muốn thử tác dụng của hàm CHOOSE trong bảng tính Excel thì các chúng ta cũng có thể sử dụng cách làm sau sống trong thanh công thức trên Excel ở máy tính xách tay của bạn, nhớ đánh dấu công thức trên thanh cách làm và dấn phím F9, nếu như bạn dùng laptop, rất có thể bạn yêu cầu dùng tổ hợp phím FN + F9:
=CHOOSE(1,2,"Học","Excel","Online","Miễn","Phí")
Kết quả bạn nhận được làm việc ngay trên thanh công thức là ="Học","Excel"
Thêm 2 ví dụ với hàm vlookup có đk và CHOOSE:
Tra cứu tài liệu dựa trên điều kiện từ 3 cột
Ví dụ này được lưu lại trong Sheet3 của bảng tính làm việc phía trên. Trong lấy ví dụ như này, chúng ta cũng có thể dùng kinh nghiệm vừa được mô tả để tra cứu tài liệu từ cột D dựa vào dữ liệu ngơi nghỉ cột A, cột B cùng cột C. Đến đây, hi vọng các chúng ta có thể xử lý thạo dù dữ liệu có tới từ bao nhiêu cột đi nữa.
Xem thêm: Những tiểu thuyết hay nhất của trung quốc phải đọc một lần trong đời
Tra cứu tài liệu đặc biệt
Ví dụ này rất có thể được tra cứu thấy vào Sheet4 của bảng tính phía trên. Họ có 2 cột Họ và Tên, dựa vào 2 cột này bọn họ muốn tìm ra số báo danh của một người. Ở lấy ví dụ như này, giải pháp nối Họ với Tên được áp dụng để làm cho tên đầy đủ, có thể nối Họ và Tên vì dấu cách, vết nối, hoặc có thể dấu phẩy thêm vào đó dấu cách, các công thức được sử dụng cho từng trường hợp sẽ là (lưu ý, phương pháp mảng, thừa nhận CTRL + SHIFT + ENTERđể nhập):
=VLOOKUP(A7,CHOOSE(1,2,$A$2:$A$4&" "&$B$2:$B$4,$C$2:$C$4),2,0)=VLOOKUP(A8,CHOOSE(1,2,$A$2:$A$4&"-"&$B$2:$B$4,$C$2:$C$4),2,0)=VLOOKUP(A9,CHOOSE(1,2,$A$2:$A$4&", "&$B$2:$B$4,$C$2:$C$4),2,0)Kết luận:Như vậy, mong muốn qua bài bác này chúng ta đã biết thêm một cách thực hiện hàm vlookup tất cả điều kiện để có thể ứng dụng linh hoạt hơn. Hãy hãy nhớ là download tư liệu kèm theo đoạn clip và bài viết để thực hành thực tế một cách thuận lợi nhất!
Những kiến thức bạn vẫn xem tất cả trong khóa học EX101 – Excel từ bỏ cơ bản tới chuyên gia của học Excel Online. Trong khóa đào tạo này bạn sẽ được mày mò cách sử dụng các công cụ: conditional formatting, data validation, pivot table… để vận dụng vào trong công việc. Bên cạnh đó bạn còn biết phương pháp sử dụng những hàm vào excel, từ các hàm cơ bạn dạng cho tới những hàm nâng cao, phức tạp. Hiện nay hệ thống đang sẵn có ưu đãi siêu lớn cho chính mình khi đk tham gia khóa học.