Bài Tập Excel Hàm Vlookup Kết Hợp Hàm If, Bài Tập Excel: Hàm Vlookup Kết Hợp Hàm If

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 lingocard.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.

Đang xem: Bài tập excel hàm vlookup kết hợp hàm if

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. lingocard.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, , )

 

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 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.

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, )

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.: phạm vi tìm kiếmNếu Range_lookup = 1 hoặc TRUE: dò tìm kết quả phù hợp gần đúngNế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.

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?

 

lingocard.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.

Xem thêm: Khóa Học Làm Kem Dưỡng Da Nền Sữa Chua (Nền Kem Yogurt), Khóa Học Làm Kem Dưỡng Da Handmade

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.

 

lingocard.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.

Xem thêm: Bài Tập Câu Điều Kiện Loại 1 Và 2, Bài Tập Câu Điều Kiện Có Đáp Án

 

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).

 

lingocard.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:

Xem thêm bài viết thuộc chuyên mục: Excel