Hầu hết chúng ta đều biết đến hàm VLOOKUP trong Excel, nó giúp chúng ta tìm kiếm và truy xuất thông tin từ một bảng dữ liệu lớn. Tuy nhiên, hàm VLOOKUP chỉ cho phép tìm kiếm một giá trị duy nhất và không có tính năng tìm kiếm với nhiều điều kiện. Trong bài viết này, chúng ta sẽ tìm hiểu cách sử dụng hàm VLOOKUP với nhiều điều kiện trong Excel.
Tham khảo thêm: Tổng hợp các khóa học excel cơ bản cho người đi làm
Danh mục nội dung
Giới thiệu về hàm VLOOKUP trong Excel
Hàm VLOOKUP là một trong những hàm phổ biến nhất trong Excel và được sử dụng rộng rãi trong công việc văn phòng, quản lý dữ liệu và phân tích dữ liệu. Hàm VLOOKUP có chức năng giúp bạn tìm kiếm một giá trị cụ thể trong bảng dữ liệu và trả về giá trị liên quan tới giá trị đó.
1. Cách sử dụng hàm VLOOKUP để tìm kiếm giá trị trong bảng dữ liệu
Để sử dụng hàm VLOOKUP, bạn cần có bảng dữ liệu chứa các giá trị cần tìm kiếm và giá trị liên quan tới giá trị đó. Hàm VLOOKUP bao gồm các tham số sau:
Lookup_value: Giá trị cần tìm kiếm trong bảng dữ liệu.
Table_array: Phạm vi của bảng dữ liệu, bao gồm giá trị cần tìm kiếm và giá trị liên quan tới giá trị đó.
Col_index_num: Số thứ tự của cột trong bảng dữ liệu chứa giá trị liên quan tới giá trị cần tìm kiếm. Số thứ tự bắt đầu từ 1 cho cột đầu tiên trong bảng dữ liệu.
Range_lookup: Trạng thái tìm kiếm. Nếu là TRUE hoặc 1, hàm sẽ tìm kiếm giá trị gần nhất với giá trị cần tìm kiếm. Nếu là FALSE hoặc 0, hàm sẽ chỉ tìm kiếm giá trị chính xác.
Ví dụ, bạn có bảng dữ liệu về sản phẩm bao gồm tên sản phẩm, giá tiền và số lượng còn lại. Bạn muốn tìm giá tiền của sản phẩm “Áo khoác” trong bảng dữ liệu. Bạn có thể sử dụng hàm VLOOKUP như sau:
=VLOOKUP(“Áo khoác”, A2:C10, 2, FALSE)
Trong ví dụ này, “Áo khoác” là giá trị cần tìm kiếm, A2:C10 là phạm vi của bảng dữ liệu, 2 là số thứ tự của cột giá tiền và FALSE là trạng thái tìm kiếm để tìm kiếm giá trị chính xác.
2. Cú pháp và cách nhập dữ liệu cho hàm VLOOKUP
Cú pháp của hàm VLOOKUP như sau:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Bạn có thể nhập dữ liệu cho hàm VLOOKUP bằng cách chọn các ô trong bảng dliệu hoặc nhập trực tiếp giá trị vào công thức. Khi nhập dữ liệu, bạn cần tuân thủ đúng cú pháp và sử dụng dấu phẩy để ngăn cách các tham số.
Ngoài ra, bạn cũng có thể sử dụng các phím tắt để nhanh chóng nhập hàm VLOOKUP vào công thức. Bạn có thể bấm tổ hợp phím Ctrl + Shift + L để mở hộp thoại Insert Function và chọn hàm VLOOKUP từ danh sách các hàm trong Excel.
Để đảm bảo chính xác và hiệu quả khi sử dụng hàm VLOOKUP, bạn cần lưu ý một số điểm quan trọng như sau:
– Kiểm tra lại bảng dữ liệu trước khi sử dụng hàm VLOOKUP để đảm bảo các giá trị cần tìm kiếm đều có trong bảng dữ liệu.
– Kiểm tra lại cú pháp và các tham số của hàm VLOOKUP trước khi sử dụng để đảm bảo tính chính xác của công thức.
– Sử dụng trạng thái tìm kiếm phù hợp với mục đích sử dụng của bạn. Nếu bạn muốn tìm kiếm giá trị chính xác, hãy sử dụng trạng thái tìm kiếm FALSE hoặc 0. Nếu bạn muốn tìm kiếm giá trị gần nhất, hãy sử dụng trạng thái tìm kiếm TRUE hoặc 1.
– Sử dụng hàm IFERROR để tránh hiển thị thông báo lỗi #N/A nếu giá trị cần tìm kiếm không có trong bảng dữ liệu.
Sử dụng hàm VLOOKUP với một điều kiện
Khi làm việc với bảng dữ liệu lớn trong Excel, việc tìm kiếm các giá trị cụ thể có thể là một thách thức. Hàm VLOOKUP là một trong những công cụ mạnh mẽ trong Excel để giúp tìm kiếm giá trị trong bảng dữ liệu. Ngoài ra, hàm VLOOKUP cũng cho phép người dùng tìm kiếm với một điều kiện, giúp việc tìm kiếm trở nên nhanh chóng và dễ dàng hơn.
1. Cách sử dụng hàm VLOOKUP để tìm kiếm với một điều kiện
Hàm VLOOKUP với một điều kiện cho phép người dùng tìm kiếm giá trị dựa trên một điều kiện cụ thể nào đó. Điều kiện này có thể được xác định bằng cách sử dụng hàm IF, hàm AND hoặc hàm OR trong Excel.
Ví dụ, bạn có một bảng dữ liệu với các thông tin về sản phẩm và giá cả. Bảng này có các cột như sau:
Cột A: Mã sản phẩm
Cột B: Tên sản phẩm
Cột C: Nhà sản xuất
Cột D: Giá bán
Bạn muốn tìm kiếm giá của sản phẩm có mã sản phẩm là “SP001” và được sản xuất bởi “ABC Company”. Bạn có thể sử dụng hàm VLOOKUP với một điều kiện như sau:
=VLOOKUP(“SP001″,IF(C2:C10=”ABC Company”,A2:D10),4,FALSE)
Trong đó:
“SP001″ là mã sản phẩm bạn muốn tìm kiếm.
IF(C2:C10=”ABC Company”,A2:D10) là điều kiện bạn muốn áp dụng cho việc tìm kiếm. Hàm IF trong trường hợp này sẽ xác định các hàng có nhà sản xuất là “ABC Company” và trả về các giá trị từ cột A đến cột D của các hàng đó.
4 là số cột chứa giá trị bạn muốn trả về (trong trường hợp này là cột D).
FALSE cho biết bạn muốn tìm kiếm chính xác giá trị tương ứng với điều kiện đã xác định, không phải giá trị gần giống nhất.
2. Cú pháp và cách nhập dữ liệu cho hàm VLOOKUP với một điều kiện
Cú pháp cho hàm VLOOKUP với một điều kiện tương tự như hàm VLOOKUP thông thường, chỉ khác ở điều kiện được thêm vào bên trong hàm. Cụ thể, cú pháp của hàm VLOOKUP với một điều kiện là:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Trong đó:
lookup_value: giá trị cần tìm kiếm
table_array: bảng dữ liệu chứa giá trị cần tìm kiếm và giá trị trả về
col_index_num: chỉ số cột chứa giá trị trả về (tính từ cột đầu tiên trong bảng dữ liệu)
range_lookup: (tùy chọn) là một giá trị logic xác định cách tìm kiếm. Nếu range_lookup là TRUE hoặc không được nhập, hàm sẽ tìm kiếm giá trị gần đúng. Nếu range_lookup là FALSE, hàm sẽ tìm kiếm giá trị chính xác.
Khi sử dụng hàm VLOOKUP với một điều kiện, bạn cần thêm điều kiện vào bảng dữ liệu bằng cách sử dụng hàm IF. Cụ thể, bạn sẽ thêm một cột mới trong bảng dữ liệu để đánh dấu các dòng thỏa mãn điều kiện và không thỏa mãn điều kiện, sau đó sử dụng hàm VLOOKUP để tìm kiếm giá trị trong cột mới này.
Ví dụ, giả sử bạn có bảng dữ liệu sau đây:
| STT | Tên sản phẩm | Nhà cung cấp | Giá |
|---|---|---|---|
| 1 | Sản phẩm A | Nhà cung cấp X | 100 |
| 2 | Sản phẩm B | Nhà cung cấp Y | 200 |
| 3 | Sản phẩm C | Nhà cung cấp X | 150 |
| 4 | Sản phẩm D | Nhà cung cấp Z | 300 |
Bạn muốn tìm giá của sản phẩm có tên là “Sản phẩm A” và được cung cấp bởi nhà cung cấp “Nhà cung cấp X”. Bạn có thể sử dụng hàm VLOOKUP với một điều kiện như sau:
Thêm cột mới để đánh dấu các sản phẩm thỏa mãn điều kiện:
| STT | Tên sản phẩm | Nhà cung cấp | Giá | Điều kiện |
|---|---|---|---|---|
| 1 | Sản phẩm A | Nhà cung cấp X | 100 | TRUE |
| 2 | Sản phẩm B | Nhà cung cấp Y | 200 | FALSE |
| 3 | Sản phẩm C | Nhà cung cấp X | 150 | FALSE |
| 4 | Sản phẩm D | Nhà cung cấp Z | 300 | FALSE |
Đầu tiên, ta cần thêm một cột mới để đánh dấu các sản phẩm thỏa mãn điều kiện. Chúng ta sẽ đặt tên cột này là “Điều kiện”. Ta sẽ nhập công thức sau vào ô D2 và kéo xuống các ô phía dưới:
=IF(AND(B2=”Sản phẩm A”,C2=”Nhà cung cấp X”),TRUE,FALSE)
Công thức này sẽ kiểm tra xem Tên sản phẩm ở cột B có phải là “Sản phẩm A” và Nhà cung cấp ở cột C có phải là “Nhà cung cấp X” hay không. Nếu cả hai điều kiện này đều đúng, ô ở cột D sẽ hiển thị giá trị TRUE, ngược lại sẽ hiển thị giá trị FALSE.
Sau đó, chúng ta có thể sử dụng hàm VLOOKUP với điều kiện để tìm giá trị của sản phẩm thỏa mãn điều kiện. Ta sẽ nhập công thức sau vào ô E2:
=VLOOKUP(TRUE,D2:E5,2,FALSE)
Trong công thức này, tham số đầu tiên (TRUE) là giá trị cần tìm kiếm, phải được đặt trong dấu ngoặc kép vì nó là một chuỗi ký tự. Tham số thứ hai (D2:E5) là phạm vi tìm kiếm, bao gồm cả cột điều kiện và cột giá. Tham số thứ ba (2) là chỉ số cột mà giá trị cần tìm kiếm được lấy ra (cột giá). Tham số thứ tư (FALSE) chỉ ra rằng ta muốn tìm kiếm chính xác giá trị TRUE, không phải giá trị gần đúng.
Kết quả sẽ là giá trị 100, chính là giá của sản phẩm A được cung cấp bởi nhà cung cấp X.
Chúng ta có thể sử dụng cách này để tìm kiếm giá trị của bất kỳ sản phẩm nào thỏa mãn điều kiện, chỉ cần thay đổi tên sản phẩm và nhà cung cấp trong công thức tìm kiếm.
3. Ví dụ minh họa về cách sử dụng hàm VLOOKUP với một điều kiện
Để hiểu rõ hơn cách sử dụng hàm VLOOKUP với một điều kiện, chúng ta có thể tham khảo ví dụ sau:
Giả sử bạn có bảng dữ liệu sau đây về các mặt hàng bán trong cửa hàng của mình:
| STT | Tên mặt hàng | Nhà cung cấp | Ngày sản xuất | Giá bán |
|---|---|---|---|---|
| 1 | Ti vi Samsung | 2022-01-01 | 10,000,000 | |
| 2 | Máy giặt LG | 2022-01-01 | 5,000,000 | |
| 3 | Tủ lạnh Panasonic | 2022-01-01 | 8,000,000 | |
| 4 | Máy lạnh LG | 2022-01-01 | 12,000,000 | |
| 5 | Máy sấy quần Panasonic | 2022-01-01 | 3,000,000 |
Bạn muốn tìm giá bán của mặt hàng có tên là “Ti vi” và được cung cấp bởi nhà cung cấp “Samsung”. Để làm điều này, bạn có thể sử dụng hàm VLOOKUP với một điều kiện như sau:
Đặt tiêu đề cho bảng dữ liệu của bạn. Ở đây, các tiêu đề cho các cột lần lượt là “STT”, “Tên mặt hàng”, “Nhà cung cấp”, “Ngày sản xuất” và “Giá bán”.
Thêm cột mới để đánh dấu các mặt hàng thỏa mãn điều kiện. Ở đây, ta đặt tiêu đề cho cột mới là “Điều kiện”.
Trong cột “Điều kiện”, sử dụng hàm IF để kiểm tra xem mỗi hàng có phải là sản phẩm cần tìm không. Nếu là sản phẩm cần tìm, đánh dấu “TRUE”, nếu không, đánh dấu “FALSE”. Công thức được sử dụng như sau:
=IF(AND(B2=”Ti vi”,C2=”Samsung”), “TRUE”, “FALSE”)
Sử dụng hàm VLOOKUP với một điều kiện để tìm giá bán của mặt hàng cần tìm. Công thức được sử dụng như sau:
=VLOOKUP(“TRUE”,D2:E6,2,FALSE)
Trong đó:
“TRUE” là giá trị cần tìm trong cột “Điều kiện”
D2:E6 là phạm vi của bảng dữ liệu
2 là chỉ số của cột “Giá bán” trong phạm vi bảng dữ liệu
FALSE là để chỉ ra rằng ta muốn tìm giá trị chính xác.
Sau khi thêm cột mới “Điều kiện”, bạn có thể sử dụng hàm VLOOKUP với một điều kiện để tìm giá của sản phẩm “Sản phẩm A” và được cung cấp bởi nhà cung cấp “Nhà cung cấp X”. Cú pháp của hàm VLOOKUP với một điều kiện như sau:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Trong đó:
lookup_value: là giá trị mà bạn muốn tìm kiếm, trong ví dụ này là “Sản phẩm A” và “Nhà cung cấp X” được nhập vào một ô khác nhau.
table_array: là phạm vi dữ liệu mà bạn muốn tìm kiếm, bao gồm cả cột điều kiện mới thêm vào. Trong ví dụ này, phạm vi dữ liệu là từ ô A1 đến E5.
col_index_num: là số chỉ mục của cột mà bạn muốn lấy giá trị, tính từ cột đầu tiên trong phạm vi table_array. Trong ví dụ này, vì bạn muốn lấy giá của sản phẩm nên col_index_num sẽ là 4.
range_lookup: là giá trị TRUE hoặc FALSE, cho biết bạn muốn tìm kiếm chính xác (FALSE) hay gần đúng (TRUE). Trong ví dụ này, vì bạn muốn tìm kiếm chính xác nên range_lookup sẽ là FALSE.
Vì bạn muốn tìm giá của sản phẩm “Sản phẩm A” và được cung cấp bởi nhà cung cấp “Nhà cung cấp X”, nên bạn cần ghép hai giá trị này lại với nhau. Bạn có thể sử dụng hàm CONCATENATE để ghép hai giá trị này thành một chuỗi.
Sau khi ghép chuỗi, bạn có được giá trị “Sản phẩm A Nhà cung cấp X”. Bạn nhập giá trị này vào ô A9 và sử dụng công thức sau đây để tìm giá của sản phẩm:
=VLOOKUP(A9,$A$1:$E$5,4,FALSE)
Kết quả trả về sẽ là giá của sản phẩm “Sản phẩm A” và được cung cấp bởi nhà cung cấp “Nhà cung cấp X”, tức là 100.
Đó là một ví dụ cụ thể về cách sử dụng hàm VLOOKUP với một điều kiện trong Excel. Bạn có thể thực hành bằng cách tạo ra bảng dữ liệu của riêng mình và thử áp dụng hàm VLOOKUP với một điều kiện để tìm kiếm thông tin cần thiết. Việc sử dụng hàm VLOOKUP với một điều kiện sẽ giúp bạn tìm kiếm dữ liệu một cách nhanh chóng và chính xác hơn, đồng thời giảm thiểu việc phải thao tác tìm kiếm thủ công trên bảng dữ liệu.
Sử dụng hàm VLOOKUP với nhiều điều kiện
1. Cách sử dụng hàm VLOOKUP để tìm kiếm với nhiều điều kiện
Trong Excel, bạn có thể sử dụng hàm VLOOKUP để tìm kiếm giá trị dựa trên nhiều điều kiện. Điều này được thực hiện bằng cách sử dụng hàm VLOOKUP kết hợp với các hàm khác, chẳng hạn như hàm IF hoặc hàm AND. Với cách này, bạn có thể tìm kiếm giá trị dựa trên nhiều cột trong bảng dữ liệu.
2. Cú pháp và cách nhập dữ liệu cho hàm VLOOKUP với nhiều điều kiện
Cú pháp của hàm VLOOKUP với nhiều điều kiện như sau:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Trong đó:
lookup_value: giá trị cần tìm kiếm.
table_array: bảng dữ liệu mà bạn muốn tìm kiếm giá trị.
col_index_num: số cột trong bảng dữ liệu mà giá trị cần tìm kiếm nằm trong.
range_lookup: một giá trị tùy chọn để xác định cách tìm kiếm giá trị. Nếu range_lookup bằng TRUE hoặc được bỏ trống, hàm sẽ tìm kiếm giá trị gần nhất với giá trị cần tìm kiếm. Nếu range_lookup bằng FALSE, hàm sẽ chỉ tìm kiếm chính xác giá trị cần tìm kiếm.
Để sử dụng hàm VLOOKUP với nhiều điều kiện, bạn cần sử dụng các hàm IF hoặc AND để xác định các điều kiện tìm kiếm. Sau đó, bạn có thể kết hợp các hàm này với hàm VLOOKUP bằng cách sử dụng toán tử & để ghép các điều kiện lại với nhau. Cú pháp chung như sau:
=VLOOKUP(lookup_value&condition1&condition2&…,table_array,col_index_num,[range_lookup])
Trong đó:
lookup_value: giá trị cần tìm kiếm.
condition1, condition2, …: các điều kiện tìm kiếm.
table_array: bảng dữ liệu mà bạn muốn tìm kiếm giá trị.
col_index_num: số cột trong bảng dữ liệu mà giá trị cần tìm kiếm nằm trong.
range_lookup: một giá trị tùy chọn để xác định cách tìm kiếm giá trị.
3. Ví dụ minh họa về cách sử dụng hàm VLOOKUP với nhiều điều kiện
Để minh họa cho cách sử dụng hàm VLOOKUP với nhiều điều kiện, ta sẽ lấy ví dụ sau đây:
Giả sử bạn có bảng dữ liệu như sau:
Bảng sản phẩm:
| STT | Tên sản phẩm | Nhà sản xuất | Giá |
|---|---|---|---|
| 1 | Sản phẩm A | Nhà sản xuất X | 100 |
| 2 | Sản phẩm B | Nhà sản xuất Y | 200 |
| 3 | Sản phẩm C | Nhà sản xuất X | 150 |
| 4 | Sản phẩm D | Nhà sản xuất Z | 300 |
Bảng đơn hàng:
| STT | Tên sản phẩm | Nhà sản xuất | Số lượng | Ngày đặt hàng |
|---|---|---|---|---|
| 1 | Sản phẩm A | Nhà sản xuất X | 5 | 2022-01-01 |
| 2 | Sản phẩm B | Nhà sản xuất Y | 10 | 2022-01-02 |
| 3 | Sản phẩm C | Nhà sản xuất X | 7 | 2022-01-03 |
| 4 | Sản phẩm D | Nhà sản xuất Z | 3 | 2022-01-04 |
Để tính tổng giá trị đơn hàng cho sản phẩm A được đặt hàng từ nhà sản xuất X, ta sẽ sử dụng hàm VLOOKUP với nhiều điều kiện như sau:
Tạo một cột mới trong bảng đơn hàng để đánh dấu các đơn hàng thỏa mãn điều kiện “Sản phẩm A” và “Nhà sản xuất X”. Để làm điều này, ta sẽ sử dụng hàm IF và AND để kiểm tra hai điều kiện này đồng thời. Công thức sẽ như sau:
=IF(AND(B2=”Sản phẩm A”,C2=”Nhà sản xuất X”),TRUE,FALSE)
Trong đó:
B2 và C2 lần lượt là cột “Tên sản phẩm” và “Nhà sản xuất” trong bảng đơn hàng.
“Sản phẩm A” và “Nhà sản xuất X” là các điều kiện mà ta muốn kiểm tra.
Nếu cả hai điều kiện đều đúng thì hàm sẽ trả về giá trị TRUE, ngược lại sẽ trả về giá trị FALSE.
Tạo một ô trống để nhập số lượng sản phẩm A được đặt hàng từ nhà sản xuất X. Ví dụ: ô E2.
Sử dụng hàm VLOOKUP với nhiều điều kiện để tìm giá của sản phẩm A từ bảng sản phẩm. Công thức sẽ như sau:
=VLOOKUP(“Sản phẩm A”&”Nhà sản xuất X”,A2:D5,4,FALSE)
Trong đó:
“Sản phẩm A”&”Nhà sản xuất X” là kết hợp của hai điều kiện mà ta muốn kiểm tra. Dấu & được sử dụng để nối hai chuỗi lại với nhau.
A2:D5 là phạm vi dữ liệu của bảng sản phẩm.
4 là số thứ tự của cột “Giá” trong bảng sản phẩm. Hàm sẽ trả về giá trị ở cột này khi tìm thấy dữ liệu thỏa mãn các điều kiện.
FALSE được sử dụng để chỉ định rằng ta muốn tìm giá trị chính xác (exact match), tức là các điều kiện phải đúng hoàn toàn mới trả về kết quả.
Nhân giá sản phẩm A với số lượng sản phẩm A được đặt hàng từ nhà sản xuất X, bằng cách sử dụng phép nhân trong Excel. Công thức sẽ như sau:
=E2*F2
Trong đó:
E2 là ô chứa số lượng sản phẩm A được đặt hàng từ nhà sản xuất X.
F2 là ô chứa giá của sản phẩm A.
Để sử dụng hàm VLOOKUP với nhiều điều kiện, ta cần kết hợp hàm IF với hàm VLOOKUP. Cụ thể, công thức sẽ có dạng như sau:
=IF(condition1, VLOOKUP(value1, table1, col_index1, FALSE), IF(condition2, VLOOKUP(value2, table2, col_index2, FALSE), default_value))
Trong đó:
condition1, condition2: là các điều kiện để kiểm tra, nếu điều kiện đúng thì sử dụng hàm VLOOKUP tương ứng, ngược lại thì tiếp tục kiểm tra điều kiện tiếp theo.
value1, value2: là giá trị cần tìm kiếm trong bảng dữ liệu.
table1, table2: là phạm vi bảng dữ liệu cần tìm kiếm.
col_index1, col_index2: là số thứ tự của cột chứa giá trị cần trả về trong bảng dữ liệu.
default_value: là giá trị mặc định nếu không tìm thấy giá trị cần tìm kiếm.
Áp dụng công thức trên vào ví dụ trên, ta có thể tính tổng giá trị đơn hàng cho sản phẩm A, được đặt hàng từ nhà sản xuất X, bằng cách sử dụng công thức sau:
=IF(B10=”X”,IF(A10=”A”,VLOOKUP(A10,A2:D5,4,FALSE)*E2,0),0)
Trong đó:
B10 là ô chứa tên nhà sản xuất cần tìm kiếm (X).
A10 là ô chứa tên sản phẩm cần tìm kiếm (A).
A2:D5 là phạm vi bảng sản phẩm.
4 là số thứ tự của cột chứa giá sản phẩm trong bảng sản phẩm.
E2 là ô chứa số lượng sản phẩm A được đặt hàng từ nhà sản xuất X.
Công thức trên sẽ trả về tổng giá trị đơn hàng cho sản phẩm A, được đặt hàng từ nhà sản xuất X. Nếu không tìm thấy sản phẩm hoặc nhà sản xuất, công thức sẽ trả về giá trị 0.
Sử dụng hàm VLOOKUP với hàm IF
1. Cách sử dụng hàm IF để kết hợp với hàm VLOOKUP và tìm kiếm với nhiều điều kiện:
Hàm IF là hàm điều kiện trong Excel, cho phép thực hiện một hành động nào đó nếu điều kiện được xác định là đúng và một hành động khác nếu điều kiện là sai. Hàm IF thường được sử dụng để kiểm tra một giá trị có đáp ứng một điều kiện nào đó hay không.
Khi kết hợp với hàm VLOOKUP, ta có thể sử dụng hàm IF để thực hiện tìm kiếm với nhiều điều kiện. Ví dụ, ta có thể tìm kiếm sản phẩm theo tên và theo nhà sản xuất, sau đó sử dụng hàm IF để kiểm tra xem sản phẩm có đáp ứng cả hai điều kiện hay không.
2. Cú pháp và cách nhập dữ liệu cho hàm VLOOKUP và IF
Cú pháp của hàm VLOOKUP và IF như sau:
=IF(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) = condition, value_if_true, value_if_false)
Trong đó:
lookup_value: Giá trị cần tìm kiếm.
table_array: Bảng chứa dữ liệu cần tìm kiếm.
col_index_num: Số thứ tự của cột chứa giá trị cần trả về.
range_lookup: Giá trị tùy chọn để định nghĩa loại tìm kiếm, có thể là TRUE hoặc FALSE. Nếu để trống hoặc để TRUE, hàm VLOOKUP sẽ tìm kiếm gần đúng, nếu là FALSE, hàm VLOOKUP sẽ tìm kiếm chính xác.
condition: Điều kiện cần kiểm tra.
value_if_true: Giá trị trả về nếu điều kiện đúng.
value_if_false: Giá trị trả về nếu điều kiện sai.
3. Ví dụ minh họa về cách sử dụng hàm VLOOKUP và IF để tìm kiếm với nhiều điều kiện
Giả sử bạn có bảng dữ liệu như sau:
Bảng sản phẩm:
| STT | Tên sản phẩm | Nhà sản xuất | Giá |
|---|---|---|---|
| 1 | Sản phẩm A | Nhà sản xuất X | 100 |
| 2 | Sản phẩm B | Nhà sản xuất Y | 200 |
| 3 | Sản phẩm C | Nhà sản xuất X | 150 |
| 4 | Sản phẩm D | Nhà sản xuất Z | 300 |
Bảng đơn hàng:
| STT | Tên sản phẩm | Nhà sản xuất | Số lượng | Ngày đặt hàng |
|---|---|---|---|---|
| 1 | Sản phẩm A | Nhà sản xuất X | 5 | 2022-01-01 |
| 2 | Sản phẩm B | Nhà sản xuất Y | 10 | 2022-01-02 |
| 3 | Sản phẩm C | Nhà sản xuất X | 7 | 2022-01-03 |
| 4 | Sản phẩm D | Nhà sản xuất Z | 3 | 2022-01-04 |
Bạn muốn tính tổng giá trị đơn hàng cho sản phẩm A, được đặt hàng từ nhà sản xuất X, nếu giá của sản phẩm đó lớn hơn 100 và ngày đặt hàng là trong tháng 1. Để làm điều này, bạn có thể sử dụng hàm VLOOKUP và IF cùng nhau như sau:
=IF(MONTH(C2)=1,IF(VLOOKUP(B2,A2:D5,4,FALSE)>100,VLOOKUP(B2,A2:D5,4,FALSE)*E2),”Không có đơn hàng”)
Trong đó:
C2 là ô chứa ngày đặt hàng của sản phẩm A được đặt hàng từ nhà sản xuất X.
B2 là ô chứa tên sản phẩm.
A2:D5 là bảng sản phẩm.
E2 là ô chứa số lượng sản phẩm A được đặt hàng từ nhà sản xuất X.
Giá trị 4 trong hàm VLOOKUP là chỉ số cột chứa giá trong bảng sản phẩm.
FALSE trong hàm VLOOKUP chỉ ra rằng bạn muốn tìm kiếm chính xác giá trị.
Hàm MONTH(C2)=1 kiểm tra xem ngày đặt hàng có nằm trong tháng 1 hay không. Nếu đúng, hàm IF sẽ tiếp tục thực hiện, nếu không, nó sẽ trả về giá trị “Không có đơn hàng”.
Hàm IF(VLOOKUP(B2,A2:D5,4,FALSE)>100 kiểm tra xem giá của sản phẩm A có lớn hơn 100 hay không. Nếu đúng, hàm IF sẽ tiếp tục thực hiện, nếu không, nó sẽ trả về giá trị “Không có đơn hàng”.
Cuối cùng, nếu cả hai điều kiện đều đúng, hàm IF sẽ tính tổng giá trị đơn hàng cho sản phẩm A được đặt hàng từ nhà sản xuất X và trả về kết quả.
Sử dụng hàm VLOOKUP với hàm INDEX và MATCH
1. Cách sử dụng hàm INDEX và MATCH để kết hợp với hàm VLOOKUP và tìm kiếm với nhiều điều kiện
Hàm INDEX và MATCH là hai hàm có thể kết hợp với nhau để tìm kiếm giá trị trong một bảng dữ liệu theo một hoặc nhiều điều kiện. Hàm INDEX được sử dụng để truy xuất giá trị trong một vùng dữ liệu được chỉ định, trong khi hàm MATCH được sử dụng để tìm kiếm một giá trị cụ thể trong một vùng dữ liệu và trả về chỉ số hàng hoặc cột tương ứng với giá trị đó.
Khi kết hợp hàm INDEX và MATCH với hàm VLOOKUP, ta có thể tìm kiếm giá trị theo nhiều điều kiện hơn so với việc chỉ sử dụng hàm VLOOKUP. Cụ thể, ta có thể sử dụng hàm INDEX và MATCH để xác định cột chứa giá trị cần tìm kiếm trong bảng dữ liệu, sau đó dùng kết hợp với hàm VLOOKUP để tìm kiếm giá trị đó dựa trên các điều kiện khác nhau.
2. Cú pháp và cách nhập dữ liệu cho hàm VLOOKUP, INDEX và MATCH
Hàm VLOOKUP:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value: giá trị cần tìm kiếm trong bảng dữ liệu.
table_array: bảng dữ liệu chứa giá trị cần tìm kiếm.
col_index_num: chỉ số cột trong bảng dữ liệu chứa giá trị cần tìm kiếm.
range_lookup: (tùy chọn) chỉ ra rằng ta muốn tìm kiếm giá trị chính xác hay gần giá trị tìm kiếm nhất. Nếu không nhập, giá trị mặc định là TRUE, có nghĩa là tìm kiếm gần giá trị tìm kiếm nhất.
Hàm INDEX:
=INDEX(array, row_num, [column_num])
array: vùng dữ liệu chứa giá trị cần truy xuất.
row_num: chỉ số hàng trong vùng dữ liệu cần truy xuất.
column_num: (tùy chọn) chỉ số cột trong vùng dữ liệu cần truy xuất. Nếu không nhập, giá trị mặc định là 1.
Hàm MATCH:
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value: giá trị cần tìm kiếm trong vùng dữ liệu.
lookup_array: vùng dữ liệu cần tìm kiếm giá trị.
match_type: (tùy chọn) chỉ ra rằng ta muốn tìm kiếm giá trị chính
3. Ví dụ minh họa về cách sử dụng hàm VLOOKUP, INDEX và MATCH để tìm kiếm với nhiều điều kiện
Giả sử bạn có bảng dữ liệu như sau:
Bảng sản phẩm:
| STT | Tên sản phẩm | Nhà sản xuất | Giá |
|---|---|---|---|
| 1 | Sản phẩm A | Nhà sản xuất X | 100 |
| 2 | Sản phẩm B | Nhà sản xuất Y | 200 |
| 3 | Sản phẩm C | Nhà sản xuất X | 150 |
| 4 | Sản phẩm D | Nhà sản xuất Z | 300 |
Bảng đơn hàng:
| STT | Tên sản phẩm | Nhà sản xuất | Số lượng | Ngày đặt hàng |
|---|---|---|---|---|
| 1 | Sản phẩm A | Nhà sản xuất X | 5 | 2022-01-01 |
| 2 | Sản phẩm B | Nhà sản xuất Y | 10 | 2022-01-02 |
| 3 | Sản phẩm C | Nhà sản xuất X | 7 | 2022-01-03 |
| 4 | Sản phẩm D | Nhà sản xuất Z | 3 | 2022-01-04 |
Bạn muốn tính tổng giá trị đơn hàng cho sản phẩm A, được đặt hàng từ nhà sản xuất X, nếu giá của sản phẩm đó lớn hơn 100 và ngày đặt hàng là trong tháng 1.
Để làm điều này, bạn có thể sử dụng hàm INDEX và MATCH để kết hợp với hàm VLOOKUP như sau:
=IF(MONTH(C2)=1,IF(INDEX(D2:D5,MATCH(B2,A2:A5,0))>100,INDEX(D2:D5,MATCH(B2,A2:A5,0))*E2),”Không có đơn hàng”)
Trong đó:
C2 là ô chứa ngày đặt hàng của sản phẩm A được đặt hàng từ nhà sản xuất X.
B2 là ô chứa tên sản phẩm.
A2:D5 là bảng sản phẩm.
D2:D5 là cột giá của bảng sản phẩm.
E2 là ô chứa số lượng sản phẩm A được đặt hàng từ nhà sản xuất X.
MATCH(B2,A2:A5,0) sẽ tìm kiếm tên sản phẩm trong cột A và trả về chỉ số dòng tương ứng. Chỉ số 0 trong hàm MATCH đảm bảo tìm kiếm chính xác tên sản phẩm.
INDEX(D2:D5,MATCH(B2,A2:A5,0)) sẽ trả về giá của sản phẩm tương ứng với tên sản phẩm được tìm thấy.
Hàm MONTH(C2)=1 kiểm tra xem ngày đặt hàng có nằm trong tháng 1 hay không. Nếu đúng, hàm IF sẽ tiếp tục thực hiện, nếu không, nó sẽ trả về giá trị “Không có đơn hàng”.
Hàm IF(INDEX(D2:D5,MATCH(B2,A2:A5,0))>100 kiểm tra xem giá của sản phẩm A có lớn hơn 100 hay không. Nếu đúng thì kết quả sẽ trả về giá trị TRUE, nếu không thì trả về giá trị FALSE.
Ví dụ: Giả sử ta có bảng dữ liệu như sau:
| STT | Tên sản phẩm | Nhà sản xuất | Số lượng | Ngày đặt hàng |
|---|---|---|---|---|
| 1 | Sản phẩm A | Nhà sản xuất X | 5 | 2022-01-01 |
| 2 | Sản phẩm B | Nhà sản xuất Y | 10 | 2022-01-02 |
| 3 | Sản phẩm C | Nhà sản xuất X | 7 | 2022-01-03 |
| 4 | Sản phẩm D | Nhà sản xuất Z | 3 | 2022-01-04 |
Nếu ta muốn kiểm tra xem giá của sản phẩm A có lớn hơn 100 hay không, ta sử dụng công thức sau:
=IF(INDEX(D2:D5,MATCH(B2,A2:A5,0))>100, “TRUE”, “FALSE”)
Trong đó:
D2:D5 là phạm vi chứa giá sản phẩm
B2 là tên sản phẩm A cần kiểm tra
A2:A5 là phạm vi chứa tên các sản phẩm
Kết quả sẽ trả về là TRUE, vì giá của sản phẩm A là 150, lớn hơn 100.
Lưu ý khi sử dụng hàm VLOOKUP với nhiều điều kiện
1. Hạn chế của hàm VLOOKUP khi sử dụng với nhiều điều kiện
Hàm VLOOKUP rất hữu ích để tìm kiếm giá trị trong một danh sách dữ liệu. Tuy nhiên, khi muốn tìm kiếm với nhiều điều kiện, hàm này có thể gặp một số hạn chế. Đặc biệt là hàm VLOOKUP chỉ hoạt động khi giá trị tìm kiếm được đặt ở cột bên trái của giá trị trả về. Nếu giá trị tìm kiếm nằm ở cột bên phải của giá trị trả về, thì hàm VLOOKUP sẽ không hoạt động.
2. Các lưu ý khi sử dụng hàm VLOOKUP với nhiều điều kiện
Để sử dụng hàm VLOOKUP với nhiều điều kiện, có thể kết hợp với hàm INDEX và MATCH như đã đề cập ở trên. Dưới đây là một số lưu ý khi sử dụng hàm VLOOKUP với nhiều điều kiện:
Sắp xếp danh sách dữ liệu theo các cột điều kiện trước khi sử dụng hàm VLOOKUP. Điều này sẽ giúp tăng tốc độ tìm kiếm và giảm thiểu lỗi.
Hạn chế số lượng điều kiện sử dụng với hàm VLOOKUP, vì nó sẽ làm tăng độ phức tạp của công thức.
Sử dụng hàm IFERROR để tránh hiển thị giá trị lỗi #N/A nếu không tìm thấy giá trị cần tìm.
Có thể sử dụng hàm CONCATENATE hoặc & để ghép nhiều điều kiện lại với nhau trong công thức.
Với những lưu ý trên, bạn có thể sử dụng hàm VLOOKUP với nhiều điều kiện để tìm kiếm dữ liệu một cách nhanh chóng và chính xác.
Ví dụ ứng dụng thực tế
Trong quản lý bán hàng, việc sử dụng hàm VLOOKUP với nhiều điều kiện là rất phổ biến để tìm kiếm thông tin của các khách hàng, sản phẩm, đơn hàng, hoặc các thông tin khác. Dưới đây là một ví dụ ứng dụng thực tế về việc sử dụng hàm VLOOKUP với nhiều điều kiện để quản lý dữ liệu bán hàng.
Ví dụ: Bảng dữ liệu bán hàng của một công ty gồm các cột thông tin: STT, Mã sản phẩm, Tên sản phẩm, Mã khách hàng, Tên khách hàng, Ngày đặt hàng, Số lượng, Giá bán, Thành tiền.
Giả sử ta muốn tìm kiếm thông tin của một đơn hàng với các điều kiện sau đây:
Mã khách hàng là “KH001”
Ngày đặt hàng là “2022-01-01”
Ta sử dụng hàm VLOOKUP kết hợp với hàm INDEX và MATCH như sau:
Sử dụng hàm MATCH để tìm vị trí của các điều kiện trong bảng dữ liệu:
Vị trí cột “Mã khách hàng” là 4: =MATCH(“Mã khách hàng”,A1:H1,0)
Vị trí cột “Ngày đặt hàng” là 6: =MATCH(“Ngày đặt hàng”,A1:H1,0)
Sử dụng hàm INDEX để lấy dữ liệu trong bảng dữ liệu bán hàng:
Kết hợp với hàm MATCH để lấy vị trí hàng của đơn hàng cần tìm kiếm:
=INDEX(A2:H10,MATCH(1,(A2:A10=”KH001″)*(F2:F10=”2022-01-01″),0),1)
Kết quả trả về sẽ là thông tin của đơn hàng có mã khách hàng là “KH001” và ngày đặt hàng là “2022-01-01”. Ta có thể sử dụng tương tự để tìm kiếm các thông tin khác trong bảng dữ liệu bán hàng.
Kết luận
Việc sử dụng hàm VLOOKUP với nhiều điều kiện trong Excel có thể giúp người dùng tìm kiếm và lấy dữ liệu một cách chính xác và nhanh chóng. Việc kết hợp hàm VLOOKUP với hàm INDEX và MATCH cũng giúp giải quyết được những hạn chế của hàm VLOOKUP khi sử dụng với nhiều điều kiện.
Tuy nhiên, khi sử dụng hàm VLOOKUP với nhiều điều kiện, người dùng cần lưu ý những điểm sau đây:
Khi sử dụng nhiều điều kiện, người dùng nên sắp xếp dữ liệu theo thứ tự để tránh nhầm lẫn.
Người dùng cần phải cẩn thận khi nhập các tham số cho hàm VLOOKUP, INDEX và MATCH để đảm bảo việc tìm kiếm và lấy dữ liệu chính xác.
Trong một số trường hợp, việc sử dụng hàm VLOOKUP với nhiều điều kiện có thể không phù hợp, người dùng cần phải tìm các phương pháp khác để giải quyết vấn đề.
Với những lưu ý và kiến thức được cung cấp trong bài viết này, hy vọng sẽ giúp người dùng hiểu rõ hơn về cách sử dụng hàm VLOOKUP với nhiều điều kiện trong Excel và có thể áp dụng thành công vào các tình huống thực tế.
|
Trọn bộ khóa học Excel online chất lượng từ cơ bản đến nâng cao, phù hợp cho mọi đối tượng từ văn phòng đến sinh viên, học phí thấp và được sở hữu trọn đời. Bạn sẽ được tiếp cận với những bài học chi tiết và dễ hiểu, bao gồm tài liệu và bài tập thực hành giúp bạn trau dồi kiến thức và kỹ năng sử dụng Excel. |
FAQs:
Hỏi: Hàm VLOOKUP có thể sử dụng với bảng dữ liệu lớn không?
Trả lời: Có, hàm VLOOKUP có thể sử dụng với bất kỳ bảng dữ liệu nào trong Excel.
Hỏi: Hàm VLOOKUP có thể tìm kiếm được với nhiều điều kiện không?
Trả lời: Không, hàm VLOOKUP chỉ cho phép tìm kiếm với một điều kiện.
Hỏi: Hàm IF có thể kết hợp với hàm VLOOKUP để tìm kiếm với nhiều điều kiện không?
Trả lời: Có, hàm IF có thể kết hợp với hàm VLOOKUP để tìm kiếm với nhiều điều kiện.
Hỏi: Có cách nào để sử dụng hàm VLOOKUP với nhiều điều kiện mà không cần sử dụng hàm IF, INDEX và MATCH?
Trả lời: Không, khi sử dụng với nhiều điều kiện thì cần phải kết hợp hàm VLOOKUP với hàm IF, INDEX và MATCH để tìm kiếm dữ liệu chính xác.
Hỏi: Hàm VLOOKUP có hỗ trợ tìm kiếm dữ liệu trên nhiều sheet khác nhau trong Excel không?
Trả lời: Có, hàm VLOOKUP có thể tìm kiếm dữ liệu trên nhiều sheet khác nhau trong Excel bằng cách sử dụng cú pháp tên_sheet!địa chỉ_cell.