Hàm Vlookup Kết Hợp Hàm IF Trong Excel Và Hàm Hlookup + IF

Hàm VLOOKUP kết hợp hàm if  / Hàm hlookup trong excel với các phiên bản hoàn toàn giống nhau. Trường cũng nói về cách sử dụng hàm HLOOKUP kết hợp hàm if.

Vlookup là một hàm tìm kiếm rất phổ biến trong excel. Bài viết gồm có: Cú pháp, ví dụ minh họa, bài tập thực hành. 

1/ Khi nào dùng hàm vlookup và hlookup trong excel.

Vlookup – là hàm tìm kiếm theo cột:

Định nghĩa – Cú pháp – Vlookup nâng cao

Hlookup – là hàm tìm kiếm theo dòng.

Định nghĩa – Cú pháp – Hlookup nâng cao

Điểm chung giữa 2 hàm tìm kiếm này:

– Đều là hàm tìm kiếm giá trị

– Cú pháp hàm đều gồm 4 thành tố: Điều kiện tìm kiếm, vùng tìm kiếm, cột/ dòng chỉ định và kiểu tìm kiếm.

Lưu ý quan trọng: Vùng tìm kiếm phải bắt đầu bằng dòng/ cột chứa điều kiện tìm kiếm. Ngược lại hàm sẽ luôn trả về giá trị #N/A. Ở cuối bài viết này bạn sẽ tìm thấy link xem về lỗi #N/A trong excel. Rất bổ ích nhé các bạn.

Sự khác nhau giữa hai cú pháp hàm excel trên là ở thành tố thứ 3:

+ Cột chỉ định – áp dụng cho hàm vlookup

+ Dòng chỉ định – áp dụng cho hàm hlookup

Đây là yếu tố rất quan trọng nhé các bạn. Bởi nó sẽ quyết định chiều tìm kiếm của hàm mà ta muốn sử dụng. Vận dụng ngược sẽ làm kết quả tìm kiếm sai lệch hoàn toàn.

2/ Ví dụ về minh họa vlookup kết hợp if/ hlookup kết hợp if

Yêu cầu:

Tính Số Điện tiêu thụ trong và ngoài định mức – Dựa trên “Bảng Định Mức” phía dưới.

Nếu số tiêu thụ < số định mức: Số trong định mức = số tiêu thụ

Nếu số tiêu thụ > số định mức: Số trong định mức = số định mức, số ngoài định mức = số tiêu thụ – số định mức

Số tiêu thụ = Số mới – Số cũ

Số định mức: dùng hàm vlookup hoặc hàm hlookup để tìm từ Bảng định mức.

Nhiệm vụ tiếp theo là tìm số tiêu thụ định mức để so sánh với số tiêu thụ là chúng ta sẽ biết được số trong và ngoài định mức.

Có thể bạn quan tâm: ” GIÁO TRÌNH KẾ TOÁN EXCEL MIẾN PHÍ“

Làm thế nào để tính được Số tiêu thụ định mức của từng hộ gia đình.

Theo như Bảng Định Mức ở dưới thì Số tiêu thụ định mức của mỗi hộ được xác định dựa trên 2 yếu tố:

Cột: A, B, C, D

Dòng: NN, CB, ND, KD, SX

Ở trong Bảng Thanh Toán Tiền Điện:

Chúng ta sẽ thấy mỗi một hộ gia đình đều có Mã Hộ ở cột D, trong đó Mã hộ gồm 2 phần:

– 2 Ký tự đầu tiên: là một trong 5 loại sau: NN, CB, ND, KD, SX

– 1 Ký tự cuối cùng: là một trong 4 loại sau: A, B, C, D

Như vậy ta có thể dùng hàm Vlookup để tìm theo A, B, C, D hoặc dùng hàm Hlookup để tìm theo NN, CB, ND, KD, SX.

Trong bài này tôi sẽ hướng dẫn dùng hàm Vlookup.

Cấu trúc của hàm Vlookup ( Lookup_value, Table_array , Col_index_num, Range_lookup )

Học excel Free từ cơ bản đến nâng cao

2.1/ Cách sử dụng hàm Vlookup kết hợp hàm if trong ví dụ này:

=VLOOKUP (RIGHT(D3,1), $I$18:$N$22,

IF(LEFT(D3,2)=”NN”,2,IF(LEFT(D3,2)=”CB”,3,IF(LEFT(D3,2)=”ND”,4,

IF(LEFT(D3,2)=”KD”,5,6)))), 0)

(1) – Lookup_value: Điều kiện tìm kiếm, ở trong trường hợp này chúng ta dùng hàm Right sẽ tách ra được ký tự sau cùng của Mã hộ, và dùng nó để làm điều kiện tìm kiếm ở Bảng Định Mức.

(2) – Table_array: Vùng tìm kiếm, chính là Bảng Định Mức, trong đó cột đầu tiên trong vùng tìm kiếm là cột chứa A, B, C và D

Vùng điều kiện nên được cố định cả dòng cả cột.

Vì như thế khi copy công thức sang ô khác thì vùng điều kiện không bị thay đổi.

Bởi lẽ tất cả các hộ tiêu thụ điện đều có số định mức nằm trong bảng định mức.

(3) – Col_index_num: Số thứ Cột chứa giá trị đang tìm kiếm (Cột chỉ định)

Ở trường số (3) chúng ta sẽ dễ dàng nhận thấy rằng:

Nếu 2 ký tự đầu của mã hộ là NN thì Cột chỉ định sẽ là 2 tính từ cột đầu tiên. Còn nếu không phải NN mà là CB thì cột chỉ định sẽ là 3, …

Chúng ta suy luận tương tự thì chúng ta sẽ tìm được tất cả các trường hợp cho cột chỉ định.

Ở trường này chúng ta sẽ phải dùng hàm Left để tách ra 2 ký tự đầu tiên trong Mã hộ. Sau đó dùng kết quả của hàm này để tìm số thứ tự cột chỉ định

(4) – Range_lookup: Kiểu tìm kiếm là 0 – Kiểu tìm kiếm chính xác 100%.

2.2/ Cách sử dụng hàm Hlookup kết hợp hàm if trong ví này:

=HLOOKUP (LEFT(D3,2), $I$18:$N$22,IF(RIGHT(D3,1)=”A”,2,

IF(RIGHT(D3,1)=”B”,3,IF(RIGHT(D3,1)=”C”,4,5))), 0)

=VLOOKUP (RIGHT(D3,1), $I$18:$N$22,IF(LEFT(D3,2)=”NN”,2,

IF(LEFT(D3,2)=”CB”,3,IF(LEFT(D3,2)=”ND”,4,IF(LEFT(D3,2)=”KD”,5,6)))), 0)

Sự khác nhau giữa 2 hàm này là:

– Tên hàm: Hlookup thay vì Vlookup

– Điều kiện tìm kiếm: khi dùng hàm hlookup thì dòng đầu tiên (row 18) của vùng dữ liệu chứa 2 ký tự đầu của mã hộ NN hoặc CB. Do đó điều kiện tìm kiếm cần tách ra 2 ký tự đầu tiên của mã từng hộ dân.

– Thành tố thứ 3 là Dòng chỉ định, thay vì cột chỉ định như CỘT chỉ định.

Nhìn ở Bảng định mức:

A: là dòng thứ 2 tính từ dòng 18

B: là dòng thứ 3 tính từ dòng 18

….

Ta chỉ cần dùng hàm Right để tách ra ký tự thứ 1 tính từ bên phải Mã hộ dân, sau đó dùng hàm if để so sánh => quyết định số DÒNG CHỈ ĐỊNH của hàm hlookup

 3/ Download bài tập trong ví dụ trên tại đây:

Như vậy, Trường đã hướng dẫn xong các bạn về Hàm vlookup kết hợp hàm if/ Hàm hlookup kết hợp hàm if.

Trong quá trình thực hành nếu bạn gặp khó khăn gì hãy email cho Trường nhé: webkynang.vn@gmail.com

Bây giờ, bạn cần download bài tập về và thực hành để thành thạo cách sử dụng hàm nhé.

Click vào bài viết sau để download:

Bài tập hàm excel nâng cao

Trong trường hợp bạn muốn tải  các bài tập excel khác thì click vào link sau:

Tổng hợp bài tập excel cơ bản đến nâng cao

Mời bạn đón xem các bài viết hay khác

1. Đổi từ Inch sang Centimet trong word 2007 2010 2013

2. Cố định vùng Ô/ vùng dữ liệu trên Excel và ý nghĩa của ký hiệu $

3. Tìm kiếm và Lọc dữ liệu trùng trong excel

4. Tập hợp file excel được download nhiều nhất

5. Vì sao Hàm vlookup bị lỗi #N/A và cách sửa lỗi #N/A