Hàm IF và hàm VLOOKUP là hai hàm có rất nhiều ứng dụng, việc kết hợp hai hàm giúp các bạn giải quyết các bài tập excel và ứng dụng rất nhiều trong thực tế. Bài viết của hoctin.vn sẽ hướng dẫn các bạn cách sử dụng hàm IF kết hợp VLOOKUP trong Excel để so sánh giá trị có điều kiện, tùy biến, thay đổi cột tham chiếu và tránh lỗi N/A khi sử dụng hàm VLOOKUP.
Mục Lục Bài Viết
Trước khi tìm hiểu các cách sử dụng hàm VLOOKUP kết hợp hàm IF, hãy cùng ôn lại cú pháp của hàm IF và hàm VLOOKUP. Hoctin.vn đã có các bài viết hướng dẫn chi tiết về hai hàm này:
Hàm IF trả về giá trị do người dùng quy định khi thực hiện một so sánh logic với cú pháp:
=IF(logical_test, [value_if_true], [value_if_false])
Trong đó:
- Logical_test: Điều kiện muốn kiểm tra với 2 kết quả là TRUE (đúng) và FALSE (sai), ví dụ 10 > 2 có kết quả là TRUE và 10 < 2 có kết quả là FALSE.
- Value_if_true: giá trị bạn muốn trả về nếu kết quả của logical_test là TRUE.
- Value_if_false: giá trị bạn muốn trả về nếu kết quả của logical_test là FALSE.
Tìm đọc thêm về: Hàm IF tạo so sánh logic trong Excel, hướng dẫn và ví dụ cụ thể
Hàm VLOOKUP là hàm dò tìm, tìm kiếm giá trị trong khu vực các cột (tạm gọi là khu vực dữ liệu dò tìm) và trả về kết quả là giá trị của một ô trong một cột do người dùng lựa chọn từ khu vực dữ liệu dò tìm, khi tiêu thức tìm kiếm khớp với một giá trị trong cột đầu tiên của khu vực dữ liệu dò tìm.
Hàm VLOOKUP có phần khó hiểu hơn hàm IF với cấu trúc:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])
Trong đó:
- Lookup_value: giá trị bạn muốn tìm kiếm, tra cứu.
- Table_array: nơi bạn muốn tìm Lookup_value, tạm gọi là khu vực dữ liệu dò tìm.
- Col_index_ num: số thứ tự của cột trong dải ô chứa giá trị cần trả về trong kết quả của hàm VLOOKUP.
- [Range_lookup]: phạm vi tìm kiếm
- Nếu Range_lookup = 1 hoặc TRUE: dò tìm kết quả phù hợp gần đúng
- Nếu Range_lookup = 0 hoặc FALSE: dò tìm kết quả chính xác, nếu không tìm thấy sẽ hiện lỗi N/A.
Tìm đọc thêm về: Cách sử dụng hàm Vlookup trong excel và ví dụ cụ thể
1, Hàm IF Kết Hợp VLOOKUP Thực Hiện So Sánh Với Tiêu Thức So Sánh Nằm Trong Một Bảng Điều Kiện:
Ví dụ khi làm danh sách thí sinh đỗ đại học từ việc so sánh tổng điểm các môn thi của thí sinh với điểm sàn từng ngành họ đăng ký (mỗi ngành có một mức điểm tối thiểu khác nhau).
Như ví dụ dưới đây, khu vực dữ liệu $B$14:$C$18 được cố định trong excel là điểm sàn các ngành được trường đại học công bố. Bảng dữ liệu cần xử lý là một danh sách các thí sinh với ngành học khác nhau, có thông tin về tổng điểm. Yêu cầu: kiểm tra thí sinh có đỗ ngành học đã đăng ký hay không?
Hoctin.vn đã xử lý thông tin này bằng cách sử dụng hàm IF để so sánh (Tổng điểm và Điểm chuẩn) kết hợp với VLOOKUP (tìm kiếm Điểm chuẩn từ thông tin Ngành học thí sinh đăng ký) như ảnh dưới đây:
Trong ví dụ trên:
-
Tại ô D3, để tìm kiếm Kết quả (Đạt/không đạt) của thí sinh có Tổng điểm tại ô C3, gõ công thức:
=IF(C3>=VLOOKUP(B3,$B$14:$C$18,2,0),"đạt","không đạt")
Nếu Tổng điểm C3 lớn hơn hoặc bằng Điểm chuẩn của Ngành học tương ứng VLOOKUP(B3,$B$14:$C$18,2,0) thì trả về “đạt”, nếu không trả về “không đạt”.
- Copy công thức này cho toàn bộ cột D, ta sẽ tìm được kết quả xét tuyển tương ứng với điểm của từng thí sinh.
2, Sử Dụng Hàm VLOOKUP IF Điều Chỉnh Cột Trả Về Kết Quả Trong Hàm VLOOKUP
Tiếp tục ví dụ về danh sách thí sinh đỗ đại học từ việc so sánh tổng điểm các môn thi của thí sinh với điểm sàn từng ngành họ đăng ký như ví dụ 1 ở trên. Bài toán trở nên phức tạp hơn khi các ngành có nhiều tổ hợp xét tuyển.
Lúc này, việc xét thí sinh có đỗ hay không phải dựa trên Điểm chuẩn sẽ khó hơn ví dụ 1 ở trên vì Điểm chuẩn tùy thuộc vào cả Ngành học và cả Tổ hợp xét tuyển.
Để có thể vận dụng kết hợp các hàm với nhau, các bạn cần nắm rõ được hướng giải quyết vấn đề trước, sau đó mới xác định cách giải quyết chi tiết: việc sử dụng hàm nào, thứ tự các hàm ra sao.
Hoctin.vn đã xử lý thông tin này bằng cách tìm Điểm chuẩn của Ngành học và Tổ hợp xét tuyển tại cột E bằng cách kết hợp hàm VLOOKUP để tìm kiếm Điểm chuẩn với hàm IF (để quy định Col_index_num tương ứng cột tổ hợp xét tuyển nào) như ảnh dưới đây:
Trong ví dụ trên, việc sử dụng hàm IF kết hợp VLOOKUP được thực hiện như sau:
Tại ô E4, để tìm kiếm Điểm chuẩn của Ngành học tại ô B4 và Tổ hợp xét tuyển tại ô C4, gõ công thức
=VLOOKUP(B4,$B$15:$E$19,IF(C4=$C$14,2,(IF(C4=$D$14,3,4))),0)
- Hàm VLOOKUP: Dò tìm Điểm chuẩn của Ngành học. Vùng dữ liệu dò tìm Table_array là $B$15:$E$19, col_index_num (số của cột được dùng để trả kết quả hàm VLOOKUP) được quyết định bởi hàm IF.
-
Hàm IF: Nếu Tổ hợp xét tuyển là A00 thì trả kết quả là 2, là A01 trả về 3, còn lại trả về 4 (chỉ có 3 tổ hợp, không phải A00 và A01 thì là D01)
3, Hàm IF Kết Hợp VLOOKUP Tra Cứu Từ Hai Vùng Dữ Liệu
Khi sử dụng hàm VLOOKUP trong Excel, bạn có thể có nhiều bảng tra cứu. Bạn có thể sử dụng hàm IF để kiểm tra xem một điều kiện có được đáp ứng hay không rồi trả về một bảng tra cứu nếu điều kiện được đáp ứng (TRUE) hoặc một bảng tra cứu khác nếu FALSE.
Ví dụ tính hoa hồng đại lý bán bảo hiểm cho từng hợp đồng sử dụng hàm VLOOKUP có điều kiện IF.
Từ năm thứ hai trở đi, mức % hoa hồng theo từng sản phẩm bảo hiểm thấp hơn năm đầu tiên. Do đó với mỗi hợp đồng bảo hiểm, đại lý phải kiểm tra xem hợp đồng là năm đầu tiên hay năm thứ hai trở đi để tìm mức % hoa hồng phù hợp theo sản phẩm.
Bảng dữ liệu cần xử lý gồm các thông tin: Tên khách hàng, tên sản phẩm (dùng để tra cứu % hoa hồng), năm thứ bao nhiêu (để tra cứu % hoa hồng của sản phẩm đang theo bảng Năm đầu tiên hay Từ năm thứ hai trở đi).
Hoctin.vn đã xử lý thông tin này bằng cách kết hợp hàm VLOOKUP (tìm kiếm % hoa hồng của sản phẩm) và quy định Table_array bằng hàm IF (nếu Hợp đồng ký năm thứ nhất thì trả về bảng Năm đầu tiên là vùng dữ liệu $A$5:$B$8, nếu không thì trả về bảng Từ năm thứ hai trở đi là vùng dữ liệu $D$5:$E$8) như ảnh dưới đây:
Trong ví dụ trên việc sử dụng hàm IF kết hợp VLOOKUP được thực hiện như sau:
Tại ô E12, để tìm kiếm % hoa hồng của hợp đồng có Sản phẩm tại ô B12, gõ công thức
=VLOOKUP(B12,IF(C12<2,$A$5:$B$8,$D$5:$E$8),2,0)
- Hàm IF: Nếu “Năm thứ” C12 nhỏ hơn 2 (tức là năm đầu) thì trả về bảng “Năm đầu tiên”, nếu không trả về bảng “Từ năm thứ hai trở đi”.
- Hàm VLOOKUP: Dò tìm sản phẩm của hợp đồng có mức % hoa hồng là bao nhiêu. Vùng dữ liệu dò tìm Table_array là $A$5:$B$8 hay $D$5:$E$8 phụ thuộc kết quả hàm IF ở trên.
4, Kết Hợp Hàm IF Và VLOOKUP Để Tránh Lỗi N/A Khi Sử Dụng Hàm VLOOKUP
Ở ví dụ dưới đây, bạn có thể thấy tại ô C10 cho kết quả lỗi. Bởi vì ô C7 đang trống nên hàm VLOOKUP không thể dò được giá trị nào ở vùng dữ liệu dò tìm khớp C7. Sử dụng hàm IF kết hợp VLOOKUP để tránh lỗi:
=IF(C7=“”,”Not available”,VLOOKUP(C7,$C$2:$E$5,2,0))
- Nếu ô C7 (lookup_value của hàm vlookup) trống thì sẽ trả về text “Lookup_value trống”
- Nếu ô C7 không rỗng thì sẽ sử dụng hàm VLOOKUP.
Tương tự, bạn có thể thay hàm IF bằng hàm IFERROR hoặc hàm IFNA với cú pháp:
=IFERROR(Value,value_if_error), cụ thể:
=IFERROR(VLOOKUP(C7,$C$2:$E$5,2,0),”Not available”)
=IFNA(Value,value_if_na), cụ thể:
=IFNA(VLOOKUP(C7,$C$2:$E$5,2,0),”Not available”)
Trên đây là những bài tập excel cơ bản về hàm IF, VLOOKUP cũng như ứng dụng hàm IF kết hợp VLOOKUP trong thực tế.
Chúc các áp dụng thành công và vận dụng tốt trong công việc và học tập.
Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm excel:
Hàm HLOOKUP
Hàm IF
Hàm AND
Hàm OR