Cách kết hợp VLOOKUP() với IF() trong Excel

Nếu bạn quản lý các bộ dữ liệu lớn trong Excel, hẳn bạn biết việc sắp xếp và phân tích chúng một cách hiệu quả không hề đơn giản. Nhưng tôi có một giải pháp cho bạn: Bạn có thể lồng hàm VLOOKUP() bên trong câu lệnh IF() để tạo các phép tra cứu động dựa trên những điều kiện cụ thể. Với cách tiếp cận này, bạn có thể tra cứu các bảng khác nhau dựa trên một điều kiện và cũng có thể xử lý lỗi một cách linh hoạt hơn.

Cụ thể, VLOOKUP() tìm các điểm dữ liệu cụ thể trong một bảng, còn IF() cho phép bạn đưa ra quyết định có điều kiện dựa trên dữ liệu đó. Cả hai hàm, như chúng ta biết, đều cực kỳ quan trọng. Trong bài viết này, chúng ta sẽ bổ sung thêm một công cụ: Bạn sẽ học cách tận dụng sức mạnh của cả hai hàm cùng lúc để thực hiện tra cứu có điều kiện, kèm theo các ví dụ thực tiễn.

Câu trả lời nhanh: Cách kết hợp VLOOKUP() và IF()

Để tạo một phép tra cứu có điều kiện, hãy bắt đầu với câu lệnh IF() và dùng VLOOKUP() bên trong để trả về các kết quả khác nhau tùy theo điều kiện. Chúng ta có thể dùng đoạn công thức sau để kiểm tra một sản phẩm còn hàng hay không dựa trên số lượng. Hãy làm theo các bước sau:

• Bắt đầu với hàm IF(): =IF()

• Bên trong hàm IF(), sử dụng VLOOKUP().

=IF(VLOOKUP(C2, $A$2:$B$6, 2, FALSE) > 0, "In Stock", "Out of Stock")

Tìm hiểu câu lệnh VLOOKUP() và IF() trong Excel

VLOOKUP() giúp bạn tìm dữ liệu trong một bảng, trong khi IF() cho phép bạn quyết định dựa trên dữ liệu đó. Kết hợp lại, chúng hỗ trợ phân tích thông tin trong bảng tính. Hãy xem từng hàm riêng lẻ rồi ghép chúng lại với nhau. 

VLOOKUP() trong Excel là gì?

VLOOKUP() tìm một giá trị cụ thể ở cột đầu tiên của một phạm vi và trả về một giá trị từ cột khác trên cùng hàng. Đây là cú pháp để bạn có thể dùng công thức VLOOKUP() cho các bộ dữ liệu lớn.

=VLOOKUP(search_key, range, index, is_sorted)

Hãy phân tích cú pháp này để hiểu rõ hơn:

• Search_key là giá trị bạn muốn tìm. Có thể là một số, văn bản hoặc tham chiếu đến ô chứa giá trị tìm kiếm.

• Range xác định phạm vi ô chứa dữ liệu. Cột đầu tiên trong phạm vi này phải chứa search_key. 

• Index là số thứ tự cột trong phạm vi mà bạn muốn lấy giá trị. Cột thứ nhất là 1, cột thứ hai là 2, v.v.

• Is_sorted là một giá trị logic (TRUE hoặc FALSE). Bạn có thể dùng TRUE (hoặc 1) cho số và FALSE (hoặc 0) cho văn bản. Nếu TRUE, VLOOKUP() giả định cột đầu tiên của phạm vi được sắp xếp tăng dần và trả về kết quả gần đúng. Nếu FALSE, VLOOKUP() sẽ tìm khớp chính xác. Nếu không chỉ định đối số này, mặc định là TRUE. 

Hãy hiểu bằng một ví dụ. Ở đây tôi có danh sách sản phẩm với mã ID và giá. Tôi muốn tìm giá cho các sản phẩm cụ thể dựa trên tên của chúng.

Danh sách sản phẩm với mã ID và giá. Nguồn: Tác giả.

Đầu tiên, tôi muốn tìm giá của máy tính bảng. Để làm điều này, tôi nhập giá trị search_key (trong trường hợp của tôi là Tablet) vào bất kỳ ô nào để tìm giá. 

Nhập giá trị để tìm giá của sản phẩm đó. Nguồn: Tác giả.

Sau đó, tôi chọn một ô và gõ =VLOOKUP().

Gõ công thức VLOOKUP(). Nguồn: Tác giả.

Tiếp theo, tôi chọn ô nơi đã nhập search_key.

Chọn cột nơi sản phẩm nằm. Nguồn: Tác giả.

Sau đó, tôi chọn phạm vi của bảng. 

Chọn phạm vi của bảng. Nguồn: Tác giả.

Đếm từ trái sang, tôi nhập số thứ tự cột từ nơi tôi muốn lấy dữ liệu. Ở đây, tôi muốn biết giá, nên tôi nhập 2.

Chọn chỉ mục của các cột. Nguồn: Tác giả.

Sau đó, tôi gõ FALSE để lấy kết quả khớp chính xác.

Gõ FALSE để lấy kết quả khớp chính xác. Nguồn: Tác giả.

Sau khi điền xong các đối số, tôi nhấn Enter:

Nhờ VLOOKUP(), đã truy xuất được giá sản phẩm. Nguồn: Tác giả.

Như bạn thấy trong hình, VLOOKUP() đã truy xuất giá thành công.

IF() trong Excel là gì?

Câu lệnh IF() so sánh các giá trị và kiểm tra chúng với điều kiện đã chỉ định. Đây là cú pháp:

=IF(logical_test, [value_if_true], [value_if_false])

Hãy xem các phần chính để hiểu rõ: 

• Logical_test là giá trị hoặc biểu thức bạn muốn đánh giá là TRUE hoặc FALSE. Đây là điều kiện bạn muốn kiểm tra.

• Value_if_true trả về giá trị nếu logical_test là TRUE.

• Value_if_false trả về giá trị nếu logical_test là FALSE.

Lấy một ví dụ. Ở đây tôi muốn ghi nhận xét cho học sinh dựa trên điểm của họ. Vì vậy, tôi chuẩn bị bảng với hai cột: STUDENTS và GRADES. 

Bảng chứa danh sách học sinh và điểm số. Nguồn: Tác giả.

Tôi chọn một ô và gõ =IF(). Mục tiêu của tôi là kiểm tra xem tổng điểm có lớn hơn 50 hay không, rồi in Excellent; nếu điểm nhỏ hơn 50, tôi in Bad. Sau khi chỉ định điều kiện, tôi nhấn Enter để lấy kết quả.

Gán nhận xét cho tất cả học sinh bằng câu lệnh IF. Nguồn: Tác giả.

Sau đó, tôi sao chép công thức xuống ô có dữ liệu cuối cùng bằng cách kéo. Bạn có thể thấy kết quả. Tôi đã gán nhận xét cho tất cả học sinh chỉ với một công thức. 

Các cách kết hợp VLOOKUP() với IF() trong Excel

Hãy cùng xem các ví dụ thực tế để hiểu VLOOKUP() hoạt động với IF() như thế nào.

Tra cứu có điều kiện

Để tạo tra cứu có điều kiện, như đã nói, hãy bắt đầu với câu lệnh IF() và dùng VLOOKUP() bên trong để trả về các kết quả khác nhau dựa trên điều kiện.

1. Bắt đầu với hàm IF(): =IF().

2. Bên trong hàm IF(), sử dụng VLOOKUP().

Thử một ví dụ mới: Ở đây tôi có danh sách đơn đặt hàng sản phẩm với thời gian đặt tương ứng. Tôi muốn xem một sản phẩm cụ thể có được đặt trước 12:00 PM hay không.

Bảng chứa danh sách sản phẩm cùng mã đơn và thời gian. Nguồn: Tác giả.

Đầu tiên, tôi chọn một cột và nhập công thức sau: 

=IF(VLOOKUP(A3, A2:C5,3, FALSE) < TIME(12, 0, 0), "Ordered Before Noon", "Ordered After Noon")

Ở đây, hàm VLOOKUP() tìm Banana trong cột A và trả về thời gian đặt hàng tương ứng từ cột B.

Câu lệnh IF() kiểm tra xem thời gian đặt có nhỏ hơn 12 không. Nếu có, nó trả về Ordered Before Noon. Ngược lại, nó trả về Ordered After Noon.

Tính thời gian giao bằng cách kết hợp công thức IF() và VLOOKUP(). Nguồn: Tác giả.

Bạn có thể thấy tôi dễ dàng theo dõi các đơn hàng sản phẩm mong muốn bằng tra cứu có điều kiện như thế nào.

Xử lý lỗi

Để xử lý lỗi, hãy bắt đầu với câu lệnh IF() và sử dụng ISNA() với VLOOKUP() bên trong để kiểm tra lỗi. Ví dụ, để hiển thị thông báo tùy chỉnh nếu không tìm thấy sản phẩm:

1. Bắt đầu với hàm IF(): =IF().

2. Bên trong hàm IF(), dùng ISNA() với VLOOKUP() để kiểm tra lỗi.

Hãy làm rõ bằng một ví dụ. Giả sử tôi có một bảng giá sản phẩm và tôi muốn hiển thị thông báo tùy chỉnh bất cứ khi nào không tìm thấy sản phẩm. 

Bảng chứa danh sách sản phẩm và giá. Nguồn: Tác giả.

Để làm vậy, tôi chọn một ô và nhập công thức sau để tìm giá:

=(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)

Sau đó, tôi kết hợp với ISNA() và IF() để xử lý mọi lỗi.

=IF(ISNA(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)), "Product Not Found", (VLOOKUP(B7, $A$2:$B$5, 2, FALSE)))

Xử lý lỗi bằng cách kết hợp công thức IF() và ISNA(). Nguồn: Tác giả.

Ở đây, ISNA() kiểm tra xem hàm VLOOKUP() có trả về lỗi #N/A hay không, tức là khi không có dữ liệu. Nói cách khác, nếu ISNA() đánh giá là TRUE, câu lệnh IF() trả về Product Not Found; nếu không, nó trả về mức giá từ VLOOKUP().

Chỉ mục cột động

Để chọn động chỉ số cột cho VLOOKUP(), hãy bắt đầu với câu lệnh IF() và dùng VLOOKUP() bên trong để chọn các cột khác nhau dựa trên điều kiện. Ví dụ, để tra cứu các cột khác nhau dựa trên một ngưỡng:

1. Bắt đầu với hàm IF(): =IF().

2. Bên trong hàm IF(), dùng VLOOKUP() để kiểm tra ngưỡng và chọn cột.

Ở đây, tôi có một bảng sản phẩm với cột A chứa tên sản phẩm, cột B chứa giá sản phẩm và cột C chứa số lượng tồn. Tôi muốn tra cứu giá sản phẩm hoặc số lượng tồn dựa trên việc giá cao hơn hay thấp hơn một ngưỡng nhất định, như 50.

Bảng chứa danh sách sản phẩm, mã ID và giá. Nguồn: Tác giả.

Tôi chọn một ô và nhập công thức sau:

=IF(VLOOKUP(B9, $B$9:$D$14, 2, FALSE) > 50, VLOOKUP(B9,$B$9:$D$14, 3, FALSE), VLOOKUP(B9,$B$9:$D$14, 2, FALSE))

Áp dụng IF với VLOOKUP() lồng nhau. Nguồn: Tác giả.

Công thức hoạt động như sau: 

• (VLOOKUP(B9, $B$9:$D$14, 2, FALSE) tra cứu sản phẩm trong B9 từ cột A và trả về giá từ cột C.

• Câu lệnh IF() kiểm tra xem giá có lớn hơn $50 không.

• Nếu đúng, mã trả về số lượng tồn: VLOOKUP(B9,$B$9:$D$14, 3, FALSE).

• Nếu sai, mã trả về mã ID sản phẩm: VLOOKUP(B9,$B$9:$D$14, 2, FALSE)).

Nếu bạn làm việc với bộ dữ liệu rất lớn, bạn có thể sao chép công thức bằng cách kéo xuống ô có dữ liệu cuối cùng.

Hiển thị kết quả. Nguồn: Tác giả.

Vậy là xong. Như bạn thấy, chúng ta có thể truy xuất thông tin mong muốn dựa trên các điều kiện cụ thể bằng một công thức kết hợp.

Kỹ thuật nâng cao với VLOOKUP() và IF()

Giờ bạn đã hiểu cơ bản về việc kết hợp câu lệnh IF() với VLOOKUP(), hãy học một số kỹ thuật nâng cao qua các ví dụ tôi tự thử nghiệm.

Kết hợp nhiều tiêu chí

Nếu bạn tra cứu dữ liệu dựa trên nhiều tiêu chí, bạn có thể kết hợp nhiều hàm VLOOKUP() trong một câu lệnh IF() để kiểm tra xem tất cả điều kiện có được đáp ứng hay không. 

Ở đây tôi có một bảng với dữ liệu mua hàng của khách và trạng thái thành viên. Tôi muốn kiểm tra xem một khách hàng có đủ điều kiện tham gia chương trình khách hàng thân thiết hay không, yêu cầu ít nhất 500 ở cột TOTAL PURCHASE ($) và Gold ở cột MEMBERSHIP STATUS.

Bảng chứa thông tin khách hàng. Nguồn: Tác giả.

Tôi tạo thêm một cột tên Eligibility, thể hiện tiêu chí đủ điều kiện. Sau đó, tôi nhập công thức sau và nhấn Enter: 

=IF(AND(VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500, VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold"), "Eligible", "Not Eligible")

Kiểm tra tiêu chí đủ điều kiện của khách hàng bằng cách kết hợp nhiều tiêu chí. Nguồn: Tác giả.

Công thức hoạt động như sau: 

• VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500 kiểm tra tổng tiền mua của khách hàng có đạt tối thiểu $500 hay không.

• VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold") kiểm tra trạng thái thành viên của John có là Gold hay không.

• AND kết hợp các điều kiện để đảm bảo cả hai đều đúng.

• IF() trả về Eligible khi cả hai điều kiện đúng. Nếu không, trả về Not Eligible.

Dùng VLOOKUP() với IF() để tính toán

Bạn có thể dùng VLOOKUP() để tìm một giá trị rồi áp dụng câu lệnh IF() để thực hiện tính toán dựa trên giá trị đó. 

Ở đây tôi chuẩn bị một bảng với sản phẩm và giá của chúng. Sau đó, tôi muốn áp dụng giảm giá 10% cho các sản phẩm trên $100.

Bảng chứa danh sách sản phẩm với giá. Nguồn: Tác giả.

Vì vậy, tôi tạo thêm cột tên DISCOUNT và viết công thức sau trong cột đó để hiển thị mức giảm giá.

=IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9, VLOOKUP(A2, $A$2:$B$10, 2, FALSE))

Cách công thức này hoạt động như sau:

• VLOOKUP(A2, $A$2:$B$10, 2, FALSE) truy xuất giá của sản phẩm là 56.

• =IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, ..., ...) kiểm tra xem giá sản phẩm có lớn hơn 100 hay không. 

• Vì điều kiện đúng, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9 áp dụng giảm 10%. 

• (VLOOKUP(A2, $A$2:$B$10, 2, FALSE)) in ra giá thực tế của sản phẩm nơi điều kiện giảm giá không đúng.

Đó là cách tôi có được kết quả cuối cùng của công thức cho sản phẩm, là 135. Để có kết quả cho tất cả sản phẩm, bạn có thể kéo công thức xuống và sao chép tới ô có dữ liệu cuối cùng.

Sử dụng VLOOKUP() với IF để tính chiết khấu cho sản phẩm có giá lớn hơn 100. Nguồn: Tác giả.

Xử lý bộ dữ liệu lớn

Bạn cũng có thể kết hợp VLOOKUP() với hàm IF() để tối ưu việc truy xuất dữ liệu và xử lý lỗi khi làm việc với bộ dữ liệu khổng lồ.

Đây là ví dụ cuối: Tôi có một bảng thông tin nhân viên và muốn truy xuất phòng ban của nhân viên, đồng thời xử lý trường hợp mã nhân viên không tồn tại.

Bảng chứa danh sách nhân viên với mã ID và phòng ban. Nguồn: Tác giả.

Vì vậy, tôi chọn một ô và gõ công thức sau: 

=IFERROR(VLOOKUP(E3, $A$2:$C$18, 3, FALSE), "Not Found")

Sau khi nhấn Enter, tôi kéo công thức để sao chép sang các ô khác.

Truy xuất phòng ban của nhân viên qua ID và xử lý lỗi nếu không tìm thấy nhân viên. Nguồn: Tác giả.

Và bạn có thể thấy kết quả ở trên. Công thức hoạt động như sau: 

• VLOOKUP(E3, $A$2:$C$18, 3, FALSE) truy xuất phòng ban của nhân viên.

• IFERROR xử lý lỗi. Thay vì hiển thị thông báo lỗi (#N/A), nó sẽ hiển thị một thông điệp thân thiện và tùy chỉnh.

Nói đơn giản, bạn không còn phải lần mò trong các bảng tính nữa. Bởi khi kết hợp VLOOKUP() và IF(), bạn có thể xử lý ngay cả những bộ dữ liệu lớn nhất trong Excel một cách dễ dàng. 

Kết luận

Bằng cách kết hợp VLOOKUP() với câu lệnh IF(), bạn có thể tạo ra các bảng tính chính xác và chống lỗi tốt hơn. Hãy thử nghiệm với các ví dụ tôi đã chia sẻ để xem các kỹ thuật này có thể đơn giản hóa công việc quản lý dữ liệu và nâng cao kỹ năng Excel của bạn như thế nào.

Nếu bạn muốn cải thiện kỹ năng, hãy xem khóa học Introduction to Excel của chúng tôi rồi nâng cấp lên lộ trình kỹ năng Excel Fundamentals để nắm vững những điều cơ bản. Khi đã quen với các hàm này, hãy nâng cao năng lực phân tích với khóa học Data Analysis in Excel. Còn nếu bạn thiên về tài chính, hãy xem khóa Financial Modeling in Excel để tích hợp các phân tích tài chính. 

Ngoài ra, khóa học Data Preparation in Excel sẽ giúp bạn tinh gọn quy trình làm sạch dữ liệu, và khóa Data Visualization in Excel sẽ giúp bạn trình bày dữ liệu một cách thuyết phục.