Bài tập excel dùng các hàm if vlookup năm 2024
Hàm IF và hàm VLOOKUP là 2 hàm cơ bản, quan trọng và có nhiều công dụng trong quá trình học Excel. Vậy nếu chúng ta kết hợp hàm IF và VLOOKUP với nhau thì sẽ được kết quả gì? Hãy cùng chúng mình tìm hiểu qua các ví dụ cụ thể dưới đây nhé. Show
Như các bạn đã biết, hàm IF là hàm điều kiện còn hàm VLOOKUP là hàm dò tìm của Excel. Khi dùng hàm IF kết hợp VLOOKUP thì chúng ta có thể tìm kiếm theo điều kiện tùy chọn. Điều này sẽ giúp việc tìm kiếm trở nên đơn giản và hiệu quả hơn. XEM NHANH BÀI VIẾT Công thức chung khi chúng ta dùng VLOOKUP kết hợp IF sẽ có dạng như sau:
Kết quả trả về sẽ có dạng True/False. Trong đó True là kết quả tìm kiếm thỏa mãn điều kiện, còn False là không thỏa mãn điều kiện. Những trường hợp thực tế khi hàm IF kết hợp VLOOKUP thì các bạn xem trong ví dụ dưới đây: Ví dụ 1: So sánh kết quả VLOOKUP với một giá trị cụ thểGiả sử chúng ta có một bảng dữ liệu như sau: Trong cột A chúng ta có tên các mặt hàng, trong cột B thì có số lượng hàng đang có. Bây giờ yêu cầu đặt ra là kiểm tra số lượng hàng tồn kho của từng loại. Nếu còn hàng thì là Yes, còn hết hàng thì No. Kết quả cần được điền vào cột E. .jpg)Bài tập hàm vlookup kết hợp hàm if Khi đó chúng ta sẽ có công thức tìm kiếm theo điều kiện như sau:
Giải thích công thức: VLOOKUP(E1,$A$2:$B$10,2,FALSE):
\=0,"Không","Có":
Kết quả thu được sẽ như sau: Sử dụng hàm vlookup có điều kiện IFNgoài cách đặt công thức trả về kết quả là Có/Không thì chúng ta có thể thay đổi thành Đúng/Sai hoặc Hết hàng/Tồn kho đều được. Công thức cụ thể như sau:
Ví dụ 2: So sánh kết quả VLOOKUP với một ô khácChúng ta có thể dùng hàm IF kết hợp VLOOKUP để so sánh kết quả tìm kiếm của hàm VLOOKUP với một giá trị ô khác. Chẳng hạn, chúng ta có thể kiểm tra giá trị tìm được với con số nằm ở ô G2 như sau: Công thức cụ thể là:
Giải thích công thức: VLOOKUP(E1,$A$2:$B$10,2,FALSE): Hàm VLOOKUP tìm kiếm giá trị trong ô E1 chính xác trong phạm vi dữ liệu từ cột A2 đến B10. \>=G2,"Có","Không":
Ví dụ 3: Các giá trị VLOOKUP trong danh sách ngắn hơnNếu muốn so sánh từng ô trong cột mục tiêu với danh sách khác thì chúng ta dùng công thức IF ISNA VLOOKUP. Trong đó, chúng ta sẽ đặt điều kiện là kết quả phù hợp được trả về là Đúng hoặc Có, ngược lại thì là Sai hoặc Không. hàm if vlookup và ISNA để tạo danh sách tra cứuCông thức cụ thể như sau:
Giải thích công thức: Đây là một hàm IF sử dụng hàm ISNA và hàm VLOOKUP để thực hiện kiểm tra dựa trên kết quả tìm kiếm. VLOOKUP(A2,$D$2:$D$4,1,FALSE):
ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)):
,"Không","Có":
Nếu công việc của bạn yêu cầu kiểm tra xem trong bảng có những sản phẩm nào còn tồn kho khi so sánh với danh sách đã hết hàng thì công thức của chúng ta sẽ có dạng như sau:
Xem thêm: Cách dùng hàm IF ELSE trong VBA Excel tự động kiểm tra điều kiện Ví dụ 4: Hàm IF kết hợp VLOOKUP để thực hiện các phép tính khác nhauChúng ta có thể dùng hàm IF kết hợp VLOOKUP để thực hiện các phép tính khác nhau theo tiêu chí mà chúng ta đặt ra. Ví dụ: Công ty có một đội ngũ bán hàng được thưởng hoa hồng theo hiệu quả kinh doanh. Trong đó, người có doanh số từ 200$ sẽ nhận được 20% hoa hồng, còn người những người khác nhận 10%. Lúc này, điều kiện mà chúng ta cần đặt ra cho hàm VLOOKUP sẽ là: Tìm các giá trị lớn hơn hoặc bằng 200$, nếu có thì nhân nó với 20%, nếu không thì nhân với 10%. Như vậy, chúng ta cần viết 3 hàm VLOOKUP lồng nhau rồi đặt hàm IF ở ngoài cùng thành công thức có dạng như sau:
Trong đó A2:C10 là vùng dữ liệu chứ tên người bán, còn C2:C10 là doanh số bán hàng của từng người. hàm vlookup kết hợp hàm if nhiều điều kiệnCông thức IF ISNA VLOOKUP và IFNA VLOOKUPCách ẩn lỗiN/A bằng công thức IF ISNA VLOOKUPCó thể bạn chưa biết khi Excel hiện lỗi N/A thì không hẳn là công thức của bạn sai. Nếu VLOOKUP không tìm thấy một giá trị được chỉ định thì cũng sẽ hiện lỗiN/A. Để ẩn lỗi này đi và thay nó thành văn bản riêng thì chúng ta có thể dùng hàm IF kết hợp VLOOKUP, và hàm ISNA theo công thức như sau:
Lưu ý: Phần “Not found” có thể được thay thế bằng bất kỳ văn bản nào khác mà bạn thấy phù hợp để thông báo về việc không tìm thấy một giá trị. Ví dụ: Chúng ta có bảng chứa thông gồm tên người bán, doanh thu bán hàng. Yêu cầu đặt ra là tìm ra số tiền tương ứng với tên người bán nhập vào F1. Vậy công thức cụ thể của chúng ta sẽ là: ẩn lỗi N/A bằng công thức IF ISNA VLOOKUPKhi đó, nếu tên được nhập vào F1 có nằm trong bảng dữ liệu bên trái thì số tiền chính xác cũng sẽ hiện ra. Nếu tên được nhập vào ô F1 không thuộc bảng bên trái thì sẽ có thông báo Not found (nghĩa là: Không tìm thấy) thay vì lỗi N/A:hàm if kết hợp vlookup và ISNA để ẩn lỗiNAChú ý: Cũng là trường hợp nêu trên nhưng trong các phiên bản Excel mới (2013, 2016, 2019, Office 365) thì bạn sẽ có thêm một hàm khác để kiểm tra logic và ẩn lỗi N/A là IFNA. Hàm này sẽ có dạng thức cơ bản như sau:
Áp dụng cụ thể vào trường hợp này thì công thức hàm sẽ là:
0 Nếu bạn muốn tìm thấy tất cả các lỗi thì chúng ta có thể kết hợp hàm VLOOKUP với hàm IFERROR nhé. Công thức đặt điều kiện nếu không tìm thấy trả về 0Nếu bạn muốn đặt điều kiện là không tìm thấy kết quả thì trả về 0 thì chúng ta vẫn sẽ dùng hàm IF kết hợp VLOOKUP, và ISNA theo công thức như sau:
1 Trong đó, phần value_if_true chúng ta sẽ điền số 0 thay vì một đoạn văn bản. Áp dụng vào bảng dữ liệu nêu trên thì công thức cụ thể là:
2 Công thức khác có thể dùng để thay thế nếu bạn đang sử dụng Excel từ bản 2013 trở lên là:
3 Công thức đặt điều kiện nếu không tìm thấy, hãy trả về ô trốngĐây là kiểu viết khác của câu lệnh “Vlookup if then”, chúng ta sẽ nhập công thức vào chuỗi trống ("") thay vì lỗi N/A:
4 Áp dụng vào bảng dữ liệu nêu trên thì công thức cụ thể sẽ như sau:
5 hàm if kết hợp vlookup và ISNA tìm giá trịNếu bạn dùng Excel từ bản 2013 trở lên thì chúng ta có thêm một công thức khác để thay thế là:
6 Xem thêm: CÁCH XỬ LÝ LỖI HÀM VLOOKUP TRONG EXCEL KHÔNG CHẠY Kết luậnNhư vậy, chúng mình đã hướng dẫn cho các bạn về cách dùng hàm IF kết hợp VLOOKUP trong Excel qua các ví dụ cụ thể. Hy vọng các bạn có thể áp dụng được kiến thức trong bài viết này vào công việc thực tế. |