Cách sử dụng hàm VLOOKUP kết hợp hàm IF được tổng hợp trong bài viết dưới đây chắc hẳn là điều mà nhiều người mong đợi bởi sự phổ biến của hai hàm này trong Excel. Cùng theo dõi chi tiết nhé!
Nội dung bài viết
Ví dụ 1: Sử dụng hàm IF để bẫy lỗi cho hàm VLOOKUP
Trong hình minh họa phía trên, bạn có thể thấy công thức ở ô E2 đưa về kết quả lỗi. Vì lúc này nội dung ở ô D2 đã không có giá trị, có nghĩa là Lookup_Value không có nội dung cho nên sẽ báo lỗi #N/A
Để có thể khắc phục được lỗi này, bạn dùng kết hợp hàm IF theo công thức như sau:
=IF(D2=””,””,VLOOKUP(D2,$A$2:$B$7,2,0))
Trong đó:
- Nếu ô D2 (lookup_value của hàm vlookup) rỗng thì sẽ rỗng
- Nếu ô D2 không rỗng thì sẽ dùng hàm Vlookup
Ví dụ 2: Sử dụng hàm IF để tùy biến vị trí cột tham chiếu trong hàm Vlookup
Trong ví dụ này, bạn muốn khi thực hiện thay đổi điều kiện tại ô E1 để xét kết quả của hàm Vlookup tương ứng với các điều kiện này.
Nếu E1 là Số tiền thì:
Bạn kết hợp hàm IF theo công thức 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 bạn sẽ 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
Ta có bảng dữ liệu như sau:
Trong bảng trên bạn có thể thấy thời gian phát sinh bao gồm 2 tháng 4 và 5. Và trong tháng 4, những sản phẩm có 1 đơn giá. Sang đến tháng 5 thì đơn giá đó thay đổi theo bảng đơn giá tháng 5. Vậy thì làm thế nào để có thể xác định được đúng đơn giá theo mỗi thời điểm phát sinh?
Cách làm là bạn sẽ phải xác định được tháng của từng lần phát sinh, từ đó dựa vào để biện luận được xem lần phát sinh đó là vào tháng mấy. Bạn thực hiện các bước như sau:
Bước 1: Thực hiện xác định tháng với hàm MONTH
Hàm MONTH là hàm có tác dụng xác định số tháng trong 1 ô chứa dữ liệu thời gian.
Khi xét MONTH(A2) bạn thu được kết quả bằng 4, MONTH(A5) sẽ cho kết quả bằng 5
Kết quả của hàm MONTH lúc này chính là căn cứ để giúp xác định bảng tham chiếu của hàm VLOOKUP. Nếu như kết quả bằng 4 thì tham chiếu tại bảng đơn giá tháng 4, còn kết quả bằng 5 thì tham chiếu sẽ nằm trong bảng đơn giá tháng 5.
Vì vậy nên bạn có thể viết:
D2=IF(MONTH(A2)=4, dùng vlookup tham chiếu tới bảng tháng 4, dùng tham chiếu tới bảng tháng 5)
Ở đây bạn chỉ có tháng 4 hoặc tháng 5, nên bạn có thể biện luận theo hướng không phải tháng 4 thì sẽ là tháng 5.
Bước 3: Tham chiếu bằng hàm VLOOKUP
Với việc dùng bảng tháng 4 để tham chiếu, bạn có hàm vlookup như sau:
=VLOOKUP(B2,G3:H7,2,0)
Trong đó:
- B2: tên sản phẩm là đối tượng cần tham chiếu
- G3:H7: vùng bảng tham chiếu đơn giá tháng 4 (trong ví dụ này có thể tính cả dòng tiêu đề là G2:H7 vẫn không sai)
- 2: cột kết quả cần tham chiếu, tức là đơn giá của sản phẩm tương ứng
- 0: phương thức tham chiếu chính xác dựa theo tên sản phẩm.
Tương tự nếu tham chiếu với bảng đơn giá tháng 5 bạn sẽ 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 tiến hành xác định được từng hàm riêng lẻ, bạn có thể thực hiện 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 vậy, bạn cũng cần lưu ý là các bảng tham chiếu đơn giá tháng 4, tháng 5 là bảng có phạm vi xác định. Nếu như tham chiếu tới những vùng bảng này phải thực hiện cố định tọa độ lại để tránh vùng tham chiếu sẽ bị thay đổi lúc kéo hàm.
Lúc này, 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 bạn sẽ có kết quả là:
Kết quả tạo ra cho thấy được sự khác biệt trong đơn giá của tháng 4 với tháng 5 rồi.
Vậy là với bất kỳ cách tổ chức dữ liệu nào bạn cũng đều có thể tùy biến công thức sao cho phù hợp để đưa ra được kết quả chính xác.
Lưu ý:
Trước khi tiến hành xây dựng công thức, bạn cần phải xác định từng bước, thực hiện chia nhỏ vấn đề cho rõ ràng. Sau khi đã xác định rõ những hàm cần dùng, vị trí đặt các hàm cũng như làm rõ nội dung từng hàm, lúc này bạn mới thực hiện ghép lại vào 1 công thức. Vậy là dù công thức có dài, có phức tạp đến đâu nhưng bạn vẫn có thể hiểu rõ và hoàn toàn hiểu, cũng tự viết lại được đấy!
Xem thêm: Hướng dẫn sử dụng hàm TRIM trong Excel
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: Ví dụ bạn đang có 1 danh sách hàng hoá trong kho cùng số lượng của các loại hàng hoá này. Bạn đang muốn có một phân tích đơn giản với việc điền vào dòng tương ứng,trong đó, nếu số lượng bằng 0 là “hết hàng”, số lượng lớn hơn 0 là “còn hàng” thì bạn có thể thực hiện viết hàm VLOOKUP kết hợp với hàm IF như minh họa bên dưới:
Ví dụ 4.2: Nếu tình hình thị trường có biến động, bạn sẽ cần có được mức thiết lập tồn kho an toàn khác nhau tuỳ vào từng thời điểm. Ví dụ, trong thời điểm đầu năm, mức tồn kho an toàn được lưu trong ô G1 đang là 15 đơn vị sản phẩm. Bạn muốn thực hiện kiểm tra sản phẩm “Táo” có trạng thái tồn kho như thế nào, bạn hãy dùng công thức như trong hình minh hoạ bên dưới:
Ví dụ 4.3: Bạn có 2 danh sách: danh sách hàng hoá trong kho cùng với danh sách hàng đủ tiêu chuẩn xuất khẩu. Vậy làm thế nào để thực hiện so sánh 2 danh sách này với nhau, rồi từ đó đánh dấu mặt hàng nào trong kho đủ tiêu chuẩn xuất khẩu? Bạn hãy thực hiện kết hợp hàm VLOOKUP với hàm IF và hàm ISNA tương tự như hình minh hoạ 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: Ví dụ như bạn đang quản lý 1 đội nhân viên bán hàng, bạn đang thiết lập cơ cấu hoa hồng cho nhân viên bán hàng rất đơn giản: nếu như doanh thu đạt được > 200 thì hoa hồng trả cho nhân viên là 10% của doanh thu. Trong khi đó, nếu doanh thu đạt được <= 200 thì hoa hồng trả cho nhân viên là 5% của doanh thu đó. Bài toán sẽ giải quyết dễ dàng bằng việc lồng ghép hàm IF và hàm VLOOKUP như minh họa bên dưới:
Xử lý lỗi #NA khi hàm VLOOKUP không tìm thấy giá trị trong bảng
Ở trường hợp giá trị cần tìm không được tìm thấy khi dùng hàm VLOOKUP thì bạn sẽ thực hiện nhận lại kết quả là lỗi #N/A. Để có thể đưa ra một thông báo có thực sự có ý nghĩa cho người sử dụng, bạn có cách kết hợp hàm VLOOKUP và hàm IFNA để xử lý lỗi #N/A bằng việc đưa ra giá trị thay thế như bên dưới:
=IFNA(value, value_if_na)
- tham số value có thể là một công thức, một số hay một địa chỉ ô trong Excel
- tham số value_if_na là giá trị trả về ở trường hợp có lỗi #N/A
* Kết luận
Trên đây là 4 ví dụ thường gặp minh họa cho cách sử dụng hàm VLOOKUP kết hợp hàm IF trong Excel. Bên cạnh đó, để có thể dùng tốt việc kết hợp các hàm này với nhau, bạn cũng cần nắm rõ được logic của vấn đề trước, từ đó mới xác định dùng hàm nào, đặt thứ tự các hàm tại vị trí nào. Chúc các bạn thực hiện thành công!
Xem thêm: Các lỗi thường gặp khiến hàm VLOOKUP không chạy trong Excel
