Hướng Dẫn Cách Viết Hàm VLOOKUP Kết Hợp Hàm IF

Hàm IF và hàm VLOOKUP là hai hàm phổ biến trong Excel mà hầu như chúng ta đều biết. Vậy bạn có biết cách kết hợp 2 hàm này trong cùng 1 công thức để tăng hiệu quả không? Hãy cùng Học Excel Online tìm hiểu cách viết hàm IF kết hợp hàm VLOOKUP trong Excel nhé:

Ví dụ 1: Dùng IF để bẫy lỗi cho hàm VLOOKUP

Ở hình trên, chúng ta thấy công thức ở ô E2 cho kết quả lỗi. Bởi vì nội dung ở ô D2 không có giá trị, tức là Lookup_Value không có nội dung nên sẽ báo lỗi #N/A

Để khắc phục lỗi này, chúng ta có thể sử dụng kết hợp hàm IF như sau:

=IF(D2=””,””,VLOOKUP(D2,$A$2:$B$7,2,0))

• Nếu ô D2 (lookup_value của hàm vlookup) là rỗng thì sẽ rỗng
• Nếu ô D2 không rỗng thì sẽ sử dụng hàm Vlookup

Ví dụ 2: Dùng IF để tùy biến vị trí cột tham chiếu trong hàm Vlookup

Ở ví dụ này, chúng ta muốn khi thay đổi điều kiện ở ô E1 để xét kết quả của hàm Vlookup tương ứng với điều kiện này.

Nếu E1 là Số tiền thì sẽ

Cột Giới tính: Cột 3

Chúng ta kết hợp hàm IF như sau:

=IF(D2=””,””,VLOOKUP(D2,$A$2:$C$7,IF(E1=”Số tiền”,2,3),0))

Khác với ví dụ 1, ở đây chúng ta cần mở rộng bảng tham chiếu bao gồm cả cột C

Ví dụ 3: Xác định đơn giá sản phẩm khi đơn giá có sự thay đổi theo tháng

Cho bảng dữ liệu như sau:

Trong bảng dữ liệu này chúng ta thấy thời gian phát sinh gồm 2 tháng 4 và 5. Và trong tháng 4, các sản phẩm có 1 đơn giá. Sang tháng 5 đơn giá đó thay đổi theo bảng đơn giá tháng 5. Vậy làm thế nào để xác định được đúng đơn giá theo từng thời điểm phát sinh?

Cách làm là chúng ta phải xác định được tháng của từng lần phát sinh, rồi dựa vào đó để biện luận xem lần phát sinh đó là tháng mấy.

Bước 1: Xác định tháng với hàm MONTH

Hàm MONTH là hàm giúp xác định số tháng trong 1 ô chứa dữ liệu thời gian.

Khi xét MONTH(A2) ta thu được kết quả bằng 4, MONTH(A5) cho kết quả bằng 5

Bước 2: Biện luận bằng hàm IF

Kết quả của hàm MONTH chính là căn cứ để xác định bảng tham chiếu của hàm VLOOKUP. Nếu kết quả bằng 4 thì tham chiếu trong bảng đơn giá tháng 4, kết quả bằng 5 thì tham chiếu trong bảng đơn giá tháng 5.

Vậy nên ta có thể viết:

D2=IF(MONTH(A2)=4,sử dụng vlookup tham chiếu tới bảng tháng 4, sử dụng tham chiếu tới bảng tháng 5)

Ở đây chỉ có tháng 4 hoặc tháng 5, nên chúng ta có thể biện luận theo hướng không phải tháng 4 thì là tháng 5.

Bước 3: Tham chiếu bằng hàm VLOOKUP

Với việc sử dụng bảng tháng 4 để tham chiếu, ta có hàm vlookup như sau:

=VLOOKUP(B2,G3:H7,2,0)

Trong đó:

• B2 là tên sản phẩm là đối tượng cần tham chiếu
• G3:H7 là vùng bảng tham chiếu đơn giá tháng 4 (ở đây có thể tính cả dòng tiêu đề là G2:H7 vẫn không sai)
• 2 là cột kết quả cần tham chiếu, chính là đơn giá của sản phẩm tương ứng
• 0 là phương thức tham chiếu chính xác theo tên sản phẩm.

Tương tự nếu tham chiếu bảng đơn giá tháng 5 chúng ta có:

=VLOOKUP(B2,J3:K7,2,0)

Bước 4: Kết hợp các hàm trong cùng 1 công thức

Khi xác định được từng hàm riêng lẻ, chúng ta có thể ghép các hàm vào trong công thức theo thứ tự:

D2=IF(MONTH(A2)=4,=VLOOKUP(B2,G3:H7,2,0),VLOOKUP(B2,J3:K7,2,0))

Tuy nhiên chúng ta cần lưu ý là các bảng tham chiếu đơn giá tháng 4, tháng 5 là những bảng có phạm vi xác định. Nếu tham chiếu tới các vùng bảng này phải cố định tọa độ lại để tránh vùng tham chiếu bị thay đổi

(xem thêm bài: Cách giữ ô tham chiếu cố định trong Excel)

Khi đó công thức sẽ được sửa lại là:

=IF(MONTH(A2)=4,VLOOKUP(B2,$G$3:$H$7,2,0),VLOOKUP(B2,$J$3:$K$7,2,0))

Filldown công thức từ D2 tới D8 chúng ta có kết quả là:

Kết quả đã tạo ra được sự khác biệt trong đơn giá của tháng 4 với tháng 5 rồi.

Như vậy với bất kỳ cách tổ chức dữ liệu nào chúng ta cũng đều có thể tùy biến công thức cho phù hợp để đưa ra được kết quả.

Lưu ý:

Trước khi xây dựng công thức, chúng ta cần phải xác định từng bước, chia nhỏ vấn đề cho rõ ràng. Sau khi đã xác định rõ các hàm cần dùng, vị trí đặt các hàm và làm rõ nội dung từng hàm, chúng ta mới ghép lại vào 1 công thức. Như vậy dù công thức có dài, có phức tạp nhưng chúng ta vẫn có thể hiểu rõ và hoàn toàn hiểu, tự viết lại được

Ví dụ 4: Kết hợp VLOOKUP và hàm IF để trả về giá trị TRUE/FALSE hoặc 1 và 0

Ví dụ 4.1: Giả sử bạn có 1 danh sách hàng hoá trong kho và số lượng của các loại hàng hoá này, nếu bạn muốn có một phân tích đơn giản bằng việc điền vào dòng tương ứng với số lượng bằng 0 là “hết hàng”, điền vào dòng tương ứng với số lượng lớn hơn 0 là “còn hàng” thì chúng ta có thể viết hàm VLOOKUP kết hợp với hàm IF như sau:

Công thức trên tra cứu số lượng của sản phẩm Nho, sau đó so sánh kết quả này nếu lớn hơn 0 thì hàm IF sẽ trả về kết quả là “Còn hàng”, nếu không kết quả sẽ là “Hết hàng”

Xem thêm: Sử dụng hàm vlookup có điều kiện trong Excel

Ví dụ 4.2: Trong tình hình thị trường có biến động, chúng ta sẽ cần có mức thiết lập tồn kho an toàn khác nhau tuỳ theo từng thời điểm, giả sử trong thời điểm đầu năm, mức tồn kho an toàn của chúng ta được lưu trong ô G1 là 15 đơn vị sản phẩm. Chúng ta muốn kiểm tra sản phẩm “Táo” của chúng ta có trạng thái tồn kho như thế nào, bạn có thể sử dụng công thức như trong hình minh hoạ.

Ví dụ 4.3: Chúng ta có 2 danh sách: danh sách hàng hoá trong kho và danh sách hàng đủ tiêu chuẩn xuất khẩu, vậy làm sao để so sánh 2 danh sách này với nhau và đánh dấu mặt hàng nào trong kho đủ tiêu chuẩn xuất khẩu? Chúng ta có thể kết hợp hàm VLOOKUP với hàm IF và hàm ISNA như trong hình minh hoạ như sau

Sử dụng kết hợp hàm VLOOKUP và hàm IF để tính toán theo điều kiện

Ví dụ 4: Giả sử bạn quản lý 1 đội nhân viên bán hàng, cơ cấu hoa hồng cho nhân viên bán hàng của bạn rất đơn giản: nếu doanh thu đạt được > 200 thì hoa hồng là 10% của doanh thu, nếu doanh thu đạt được <= 200 thì hoa hồng là 5% của doanh thu đó. Bài toán này có thể được giải quyết bằng cách lồng ghép hàm IF và hàm VLOOKUP như trong hình dưới đây

Xử lý lỗi #NA khi VLOOKUP không tìm thấy giá trị trong bảng

Trong trường hợp giá trị cần tìm kiếm không thể được tìm thấy khi sử dụng hàm VLOOKUP thì chúng ta sẽ nhận lại kết quả là lỗi #N/A. Để đưa ra một thông báo có ý nghĩa hơn cho người dùng, chúng ta có cách kết hợp hàm VLOOKUP và hàm IFNA thể xử lý lỗi #N/A bằng cách đưa ra giá trị thay thế như sau.

=IFNA(value, value_if_na)

• tham số value có thể là một công thức, một số, một địa chỉ ô trong Excel
• tham số value_if_na là giá trị trả về trong trường hợp có lỗi #N/A

* Kết luận

Trên đây là 4 ví dụ thường gặp nhất trong việc kết hợp hàm IF với hàm VLOOKUP. Các bạn hãy ghi nhớ để sử dụng phù hợp trong từng hoàn cảnh.

Để có thể sử dụng tốt việc kết hợp các hàm với nhau, chúng ta cần nắm rõ được logic của vấn đề trước, sau đó mới xác định sử dụng hàm nào, đặt thứ tự các hàm tại vị trí nào.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học

Xem thêm: CÁCH KẾT HỢP HÀM VLOOKUP VỚI CÂU LỆNH IF – IF ISNA VLOOKUP