Bạn đang xem bài viết Hàm Vlookup 2 Điều Kiện, Cách Dùng Hàm Vlookup 2 Điều Kiện. được cập nhật mới nhất trên website Utly.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Hàm Vlookup 2 điều kiện trong Excel là hàm tìm kiếm trá trị trong chuỗi với những yêu cầu cao cấp mà hàm vlookup thông thường không xử lý được. Nó giúp ta dò tìm dữ liệu theo cột với điều kiện dò tìm được kết hợp từ 2 hay nhiều cột. Trong bài viết này, chúng tôi sẽ hướng dẫn bạn sử dụng hàm Vlookup 2 điều kiện qua các ví dụ cụ thể, thực tế và dễ hiểu.
Hàm vlookup 2 điều kiện thực chất là hàm Vlookup thông thường, nhưng để sử dụng được thì ta cần kết hợp một số hàm khác nhằm biến đổi điều kiện dò tìm sao cho hàm Vlookup hiểu và trả về kết quả đúng.
1. Chức năng của hàm Vlookup trong Excel.
Trong Excel chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.
2. Cú pháp hàm Vlookup trong excel.
VLOOKUP( LOOKUP_VALUE,TABLE_ARRAY,COL_INDEX_NUM,[RANGE_LOOKUP] )
Trong đó:
Lookup_value (bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.
Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.
Row_index_num (bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.
Range_lookup (tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.
Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A
3. Hàm Vlookup 2 điều kiện.
Từ công thức trên, ta có thể thấy được hàm Vlookup thuông thường chỉ tìm kiếm được với điều kiện dò tìm là 1 ô tham chiếu, một giá trị, hoặc một chuỗi văn bản. Vậy nếu điều kiện dò tìm nằm ở nhiều ô, nhiều giá trị khác hoặc từ 2 chuỗi văn bản trở lên thì chắc chắn ta không thể sử dụng hàm Vlookup thông thường được. Ở đây, chúng tôi sẽ giới thiệu cho bạn 2 cách để tìm kiếm với hàm vlookup 2 điều kiện, áp dụng được cho cả nhiều điều kiện.
Cách 1: Dùng cột phụ.
Cách 2: Dùng công thức mảng.
Ưu nhược điểm của 2 cách dùng hàm Vlookup 2 điều kiện trong Excel.
Sử dụng cột phụ:
Ưu điểm: Đơn giản, dễ dùng và dễ áp dụng. Dùng được cho cả hàm Vlookup nhiều điều kiện.
Nhược điểm: Làm tăng lượng dữ liệu và tìm kiếm bởi việc tạo thêm cột phụ.
Sử dụng công thức mảng:
Ưu điểm: Không làm tăng lượng dữ liệu, không gây dư thừa, áp dụng được với cả hàm Vlookup nhiều điều kiện.
Nhược điểm: Khó dùng, khó nhớ, công thức dài, trừu tượng.
4. Hàm Vlookup 2 điều kiện sử dụng cột phụ.
4.1 Cách dùng hàm Vlookup 2 điều kiện sử dụng cột phụ.
Cách đơn giản nhất để tìm kiếm trong Excel với 2 điều kiện là sử dụng cột phụ. Từ 2 hay nhiều điều kiện, mình tổng hợp lại vào 1 cột mới và dùng hàm Vlookup để tìm kiếm với điều kiện dò tìm là cột ta vừa tạo.
Tùy vào từng yêu cầu cụ thể mà ta sẽ ghép các điều kiện bằng các cách khác nhau, nhưng thông thường Cột phụ sẽ được tạo ra bằng các ghép các điều kiện bằng kí hiệu “&”.
VD: A1 chứa giá trị là: NV. B1 chứa giá trị là 10.
Để dễ hình dung hơn về cách sử dụng hàm Vloolup với cột phụ chúng ta cùng tìm hiểu một vài ví dụ minh họa.
4.2 Ví dụ sử dụng hàm Vlookup 2 điều kiện bằng cột phụ.
VD: Bạn có một danh sách sản lượng sản xuất cho từng sản phẩm, từng ca. Làm thế nào để biết được sản lượng của 1 sản phẩm nào đó trong từng ca là bao nhiêu?
Hình 1: Hàm Vlookup sử dụng cột phụ để tìm kiếm.
Với bài toán tìm kiến dữ liệu theo hàng ngang trong Excel chắc chắn bạn sẽ nghĩ ngay đến hàm Vlookup, nhưng ở đây ta cần tìm sản lượng của từng Sản phẩm trong từng Ca (2 điều kiện) mà hàm vlookup thông thường lại chỉ dùng được với 1 điều kiện.
Nên ta cần biến đổi điều kiện đầu vào từ 2 điều kiện thành 1 điều kiện bằng cách tạo ra một cột phụ mới từ việc ghép Sản phẩm và Ca.
Các bước thực hiện:
B1: Tạo cột phụ.
B2: Viết hàm với điều kiện tìm kiếm là cột phụ vừa tạo.
Chi tiết các bước:
B1: Tạo cột phụ:
Ta tạo thêm cột mới, cột này đứng ở trước cột Sản phẩm và được tạo ra bằng cách ghép cột Sản phẩm và cột Ca.
Công thức ghép: [Sản phẩm]&[Ca]
Với hàng đâu tiền thì công thức sẽ là: C5&D5
Sau khi tạo công thức cho hàng đầu ta copy công thức đó cho các hàng tiếp theo để hoàn thành cột.
Sau khi hoàn thành thì cột phụ sẽ có dạng như hình sau:
Hình 2: Hàm Vlookup sử dụng cột phụ.
Bước 2: Viết công thức tìm kiếm với cột phụ vừa tạo.
Để tìm kiến sản lượng của 1 sản phẩm theo ca tại ô H8 ta nhập công thức: =VLOOKUP(H6&H7,$B$5:$E$9,4,0)
Trong đó:
H6&H7: Là giá trị cần đối chiếu.
$B$5:$E$9: Là bảng dò tìm bao gồm cả cột phụ
4: Số thứ tự cột dữ liệu trên bảng dò tìm.
0: Kiểu tìm kiếm chính xác.
Quan sát hình dưới để hiểu hơn về các thành phần của công thức chúng ta vừa tạo.
Hình 3: Hàm Vlookup sử dụng cột phụ.
Nếu muốn tìm sản lượng của sản phẩm khác hoặc ca khác bạn chỉ cần nhập sản phẩm và ca cần tìm vào bảng tìm kiếm như trên hình mà không cần tạo lại công thức.
4.3 Những lưu ý khi sử dụng hàm Vlookup 2 điều kiện bằng cột phụ.
Về bản chất: Hàm Vlookup 2 điều kiện khi dùng cột phụ sẽ trở thành hàm vlookup 1 điều kiện thông thường.
Bằng cách này bạn cũng có thể áp dụng cho nhiều điều kiện một cách dễ dàng. Cột phụ sẽ được tạo bằng cách ghép các điều kiện lại sao cho phù hợp.
Cột phụ được tạo ra phải được đặt ở đầu bảng dò tìm.
Khi tạo cột phụ ta phải ghép các cột lại theo thứ tự giống với thứ tự của điều kiện dò tìm.
5. Hàm Vlookup 2 điều kiện sử dụng công thức mảng.
Công thức mảng là một cách tính nâng cao trong Excel mà không phải ai học cũng chú ý tới nó. Chắc hẳn, trên 90% các bạn đọc bài viết này sẽ không biết tới việc sử dụng công thức mảng để tìm kiếm với nhiều điều kiện. Bởi hầu hết mọi người ít biết tới mảng trong Excel và cách này tính này khá phức tạp với những bạn chưa thành thạo Excel. Nên các bạn có thể sử dụng hàm vlookup 2 điều kiện bằng cách tạo cột phụ.
5.1 Công thức mảng là gì?
Mảng là một hàng giá trị, một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. (ví dụ: {1,2,3,4})
Công thức mảng là công thức được bao bởi cặp dấu ngoặc nhọn {} do excel tự động thêm vào sau khi kết thúc nhập công thức. Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất.
Công thức mảng bao gồm nhiều ô được gọi là công thức đa ô và công thức mảng trong một ô duy nhất được gọi là công thức đơn ô.
Nhập công thức mảng:
Chọn ô hoặc vùng ô cần nhập công thức. Nhập công thức cần tính toán
Nhấn tổ hợp phím Ctrl + Shift + Enter.
5.2 Cách dùng hàm Vlookup 2 điều kiện 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 phải tạo thêm cột, không làm tăng lượng dữ liệu và tìm kiếm.
Để tìm kiếm ta kết hợp sử dụng hàm Vlookup với hàm Choose. Và công thức mảng được lồng trong hàm Choose.
5.3 Ví dụ về hàm Vlookup 2 điều kiện sử dụng công thức mảng.
Như ví dụ bên trên ta tiếp tục tìm kiếm sản lượng của một sản phẩm theo từng ca.
Để tìm kiến sản lượng của 1 sản phẩm theo ca tại ô G7 ta nhập công thức: =VLOOKUP(G5&G6,CHOOSE({1,2},B5:B9&C5:C9,D5:D9),2,0) và nhấn Ctrl + Shift + Enter.
Trong đó:
G5&G6: Là kết hợp 2 điều kiện cần tìm.
CHOOSE({1,2},B5:B9&C5:C9,D5:D9): Là bảng dò tìm, bảng này được tạo từ 1 mảng 2 chiều có 2 cột.
Cột 1: Cột tham chiều.
Cột 2: Cột giá trị trả về.
2: Số thứ tự cột dữ liệu trên bảng dò tìm.
0: Kiểu tìm kiếm chính xác.
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 hiểu được về hàm Choose mà ta đã sử dụng.
Hình 5: Hình họa giá trị trả về của hàm Choose trong Excel.
Giải thích công thức: =CHOOSE({1,2},B5:B9&C5:C9,D5:D9)
Trong đó:
{1,2}: Công thức này sẽ trả về 1 mảng 2 chiều gồm 2 cột. Cột 1 được lấy ở ngay sau dấu phẩy thứ nhất, cột 2 được lấy ở sau dấu phẩy thứ 2.
B5:B9&C5:C9: Giá trị trả về là 1 cột, các giá trị trong cột này được ghép từ cột Ca và cột Sản phẩm.
D5:D9: Là cột sản phẩm.
Chú ý: Hình 5 chỉ để các bạn hiểu hơn về cách hoạt động của hàm CHOOSE trong Excel, khi dùng với hàm Vlookup các bạn chỉ cần viết hàm CHOOSE lồng trong hàm Vlookup như trong hình 4.
5.4 Lưu ý khi sử dụng hàm Vlookup 2 điều kiện bằng công thức mảng.
Khi nhập xong công thứ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 tự động thêm vào sau khi kết thúc nhập công thức.
Hàm Vlookup 2 điều kiện dùng công thức mảng khá khó dùng nhưng nếu bạn thành thạo nó sẽ hỗ trợ bạn rất nhiều, kể cả với nhiều điều kiện.
Nếu bạn không thực sự thành thạo thì nên bỏ qua cách này để tránh viết sai công thức dẫn đế tính toán sai.
Gợi ý học tập mở rộng.
Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản
Kinh Nghiệm Sử Dụng Hàm Vlookup Nhiều Điều Kiện
Có thể thấy Excel là nỗi ám ảnh của nhiều người nếu bạn không phải sử dụng thường xuyên cũng như không biết về các hàm để sử dụng. Trong Excel có rất nhiều hàm. Chúng giúp cho việc thực hiện các trang tính của bạn nhanh chóng và dễ dàng hơn. Tuy nhiên, không phải ai cũng có thể ghi nhớ được tất cả các hàm đó. Chỉ là hàm nào hay dùng nhiều thì nhớ đến. Một trong số những hàm được quan tâm nhiều nhất hiện này đó là hàm VLOOKUP nhiều điều kiện.
Hàm VLOOKUP nhiều điều kiện là gì?
Nếu là người thường xuyên phải làm việc với Excel thì khi hỏi đến hàm VLOOKUP nhiều điều kiện là gì, bạn sẽ có thể trả lời ngay lập tức. Đây là hàm dùng để giúp bạn tìm kiếm các giá trị và trả lại một kết quả theo hàng dọc.
Hàm này được sử dụng rất phổ biến trong việc thống kê cũng như dò tìm các dữ liệu. Công việc này sẽ trở nên nhanh chóng, tiện lợi hơn, không hề mất thời gian, công sức, không có sự sai lệch như khi làm thủ công.
Cách sử dụng hàm VLOOKUP với 2 điều kiện
Sử dụng hàm VLOOKUP trong trường hợp tạo cột phụ
=VLOOKUP(G4&G5;$A$2:$D$6;4;0)
Cuối cùng nhấn OK sẽ được kết quả như hình dưới.
Sử dụng hàm bằng cách tạo công thức mảng
Đây là cách thứ 2 để dùng hàm VLOOKUP nhiều điều kiện giúp bạn tìm kiếm dữ liệu. Cách này khá ít dùng bởi nó hơi phức tạp một chút nhưng nếu muốn tìm với nhiều điều kiện hơn cũng có thể thực hiện được.
Lưu ý là sử dụng hàm bằng cách tạo công thức mảng thì nên dùng VLOOKUP cùng với CHOOSE. Hai hàm này đều dùng để tìm kiếm các dữ liệu và chúng sẽ thực hiện tìm kiếm 1 giá trị trong 1 danh sách giá trị khác nhau.
Chúng ta hãy tiếp tục thực hiện với ví dụ ở trên về tìm kiếm sản phẩm, sản lượng trong cùng 1 ca làm việc. Lúc này, bạn sẽ không cần phải sử dụng cột phụ nữa mà trực tiếp tìm kiếm sản phẩm luôn.
Ví dụ, nếu muốn tìm sản lượng của sản phẩm A trong ca sản xuất số 1 thì công thức áp dụng sẽ là:
=VLOOKUP(G3&G4;CHOOSE({12};(B2:B6)&(C2:C6);D2:D6);2;0)
Lưu ý là các dấu (;) hay (.) được sử dụng trong hàm sẽ tùy thuộc vào các máy tính khác nhau. Bạn cần nhận biết để lên công thức chính xác, không phải lặp đi lặp lại nhiều lần và nhầm lẫn.
Đó là những thông tin cơ bản nhất về việc sử dụng hàm VLOOKUP nhiều điều kiện để tìm kiếm một dữ liệu nào đó mà bất cứ ai cũng có thể áp dụng được. Hãy lựa chọn cách phù hợp nhất với công việc của mình để nâng cao chất lượng, hiệu quả.
Sử Dụng Hàm If Có 3 Điều Kiện, Hàm If Có Điều Kiện Là Chữ
Hàm IF nhiều điều kiện là gì?
Hàm IF là hàm giúp kiểm tra giá trị đúng – sai của một phép tính Excel. Khi câu lệnh được thực hiện, nếu điều kiện đúng sẽ trả về một giá trị nào đó, nếu sai trả về một giá trị khác. Dấu ngăn cách giữa các thành phần trong công thức có thể là “;” hoặc”,” tùy cài đặt, set-up của từng máy. Trong quá trình thực hiện, Excel cũng sẽ hiển thị công thức cho người dùng tham khảo.
=IF (logical_test, [value_if_true], [value_if_false])
Trong đó:
Logical_test: là giá trị hoặc biểu thức có tính logic
Value_if_true: Giá trị mà hàm trả về nếu biểu thức trả về giá trị đúng
Value_if_false: Giá trị trả về nếu hàm logic có kết quả sai
Ví dụ: Từ điểm số, kiểm tra kết quả đậu – trượt. Nếu điểm số lớn hơn hoặc bằng 5, lên lớp, ngược lại là “học lại”
Nếu value_if_true bị bỏ qua trong công thức, kết quả sẽ trả về 0 nếu điều kiện đúng được đáp ứng.
Nếu muốn kết quả trả về chuỗi trống, nhập “”
Hàm if với điều kiện là chữ, cần đặt chuỗi ký tự cần so sánh trong dấu “”.
Cách sử dụng hàm IF có nhiều điều kiện
Nếu hàm if thường chỉ có một điều kiện thì hàm if nhiều điều kiện sẽ có nhiều điều kiện khác nhau, có thể 2 điều kiện, 3 điều kiện hoặc nhiều hơn.
Hàm if 2 điều kiện
Hàm if 2 điều kiện thường được sử dụng khi bài toán có 3 đối số cần so sánh.
Công thức tổng quát:
=IF (logical_test_1, [value_if_true], IF((logical_test_2, [value_if_true], [value_if_false]))
Ví dụ: Tính số tiền sau giảm giá vé tàu biết trẻ em từ 10 tuổi trở xuống được giảm nửa vé và miễn phí vé cho người già lớn hơn 70 tuổi.
Công thức sẽ là:
Hàm if nhiều điều kiện hay còn gọi là IF lồng. Ta sử dụng hàm if có 3 điều kiện khi có 4 tiêu chí cần so sánh.
Công thức tổng quát:
=IF (logical_test_1, [value_if_true], IF((logical_test_2, [value_if_true], IF(logical_test_3, [value_if_true], [value_if_false])))
Ví dụ, dựa vào điểm số, xếp loại kết quả học tập của các học sinh theo các tiêu chí:
Từ 6.5 – 7.9: Học sinh khá
Từ 3.5 – 6.4: Học sinh trung bình
Nhỏ hơn 3.5: Học sinh Yếu
Để có công thức rút gọn nhất, người dùng cần sử dụng hàm iF nhiều điều kiện, cụ thể ở đây là hàm if có 3 điều kiện. Lưu ý, khi sắp xếp, nên sắp xếp các điều kiện quan trọng nhất xếp lên trên.
Hàm if với điều kiện là chữ
Khi sử dụng hàm if điều kiện chữ, công thức sẽ không phân biệt chữ hoa với chữ thường. Muốn sử dùng hàm if trong so sánh ngày, người dùng cần đặt trong lệnh DATEVALUE.
VD: Vẫn bài toán trên, nếu học sinh thuộc vùng 3 sẽ được cộng thêm 1 điểm, 0.5 điểm cho vùng 2 và không thêm điểm với học sinh vùng 1.
Công thức:
=IF(C3=”vùng 1″;0;IF(C3=”vùng 2″;0,5;1))
Ngoài ra, nếu muốn tìm kiếm chính xác, phân biệt rõ chữ hoa – thường trong điều kiện, thêm EXACT trước điều điện so sánh.
=IF (EXACT (logical_test), [value_if_true], [value_if_false])
Hàm if với điều kiện ngày tháng
Khi sử dụng hàm if để so sánh ngày tháng, hàm sẽ không thể nhận diện như một chuỗi ký tự.
Ví dụ: Kiểm tra danh sách những người được nhận ưu đãi, biết ưu đãi áp dung với người sinh sau ngày 01/01/2000.
Cách Dùng Hàm Vlookup Giữa 2 File, 2 Sheet
Chắc có những bạn chưa biết về hàm Vlookup. Đây là hàm khá thông dụng và được sử dụng khá thường xuyên trên bảng tính excel.
Ở một khái niệm cơ bản và bao quát nhất thì hàm thực hiện việc truy xuất dữ liệu với các điều kiện cho trước.
2. Áp dụng hàm Vlookup giữa 2 sheet
Ví dụ hướng dẫn cụ thể sau:
Cho 2 sheet với 2 mục nội dung khác nhau. Sheet 1 là mục danh sách các nhân viên đang cần nhập số tiền thưởng tết. Sheet 2 là những điều kiện xếp loại ứng với số tiền thưởng tết.
Áp dụng hàm Vlookup giữa 2 sheet (1)
Áp dụng hàm Vlookup giữa 2 sheet (2)
– Giữ nguyên công thức vừa nhập, chọn vào sheet 2 và chọn vùng dữ liệu điều kiện lương A1: B4 sau đó sử dụng F4 để lần lượt chọn vào các ô dữ liệu A1: B4.
Áp dụng hàm Vlookup giữa 2 sheet (3)
– Trở lại Sheet 1, tại công thức vừa thiết lập nhập thêm vào công thức 2,1:
Áp dụng hàm Vlookup giữa 2 sheet (4)
– Khi đó kéo sao chép công thức xuống dưới các ô còn lại cột lương thưởng sẽ hiển thị ra hết kết quả:
Áp dụng hàm Vlookup giữa 2 sheet (5)
3. Áp dụng hàm vlookup giữa 2 file
Áp dụng hàm Vlookup giữa 2 file (1)
Và:
Áp dụng hàm Vlookup giữa 2 file (2)
Áp dụng hàm Vlookup giữa 2 file (3)
Áp dụng hàm Vlookup giữa 2 file (4)
– Khi quay lại File 1 thì nhập thêm cho công thức 2,0 vào và thực hiện sao chép công thức và cho ra toàn bộ kết quả thưởng của từng nhân viên.
Áp dụng hàm Vlookup giữa 2 file (5)
Cùng với đó, bạn cũng hoàn toàn có thể nắm trọn hệ thống hàm Excel quan trọng và cần thiết nhất với khóa học Trở thành cao thủ Excel trong 10 giờ tại Unica!
Khóa học “Trở thành cao thủ Excel trong 10 giờ” có gì dành cho bạn?
Khóa học Trở thành cao thủ Excel trong 10 giờ
Khóa học do giảng viên – Giám đốc Trung tâm gia sư Đông Phương Nguyễn Thành Phương hướng dẫn sẽ đem đến cho bạn những kiến thức và kỹ năng tin học văn phòng với Excel cực tuyệt vời và nhanh chóng chỉ sau một khóa học duy nhất.
Với 60 bài giảng trong khóa học, giảng viên sẽ giúp bạn bổ sung, cung cấp và hướng dẫn áp dụng chính xác các công thức hàm Excel để thực hiện phân tích, xử lý và thống kê số liệu một cách hiệu quả nhất, thành công nâng cao hiệu suất công việc của bạn.
Không chỉ có vậy trong suốt khóa học của mình giảng viên cũng sẽ chia sẻ đến cho bạn những kiến thức và kinh nghiệm xử lý số liệu thực chiến cực hay, giúp bạn có thể ứng dụng luôn chúng vào coonvg việc của mình.
Cập nhật thông tin chi tiết về Hàm Vlookup 2 Điều Kiện, Cách Dùng Hàm Vlookup 2 Điều Kiện. trên website Utly.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!