Bạn có hay sử dụng các hàm VLOOKUP trong Excel để hỗ trợ trong việc tính toán hay không? Đây là những hàm cơ bản và thường dùng nhất trong Excel. Nếu bạn chưa biết về hàm VLOOKUP thì bài viết dưới đây sẽ giúp bạn.
Để trở thành một dân thành thạo trong tin học văn phòng, bạn không thể bỏ qua hàm Vlookup - đang được cho là trợ thủ đắc lực trong việc sắp xếp những thông tin mang tính quan trọng bằng công cụ Excel. Do đó cách dùng hàm Vlookup kết hợp hàm IF trong Excel sẽ ra sao?
I. Sử dụng hàm VLOOKUP khi nào?
Ta sử dụng hàm VLOOKUP trong excel để hàm trả về một số giá trị nào đó, thông qua những giá trị khác. Thí dụ: dùng hàm để có thể phân loại học sinh thành Giỏi, Khá, Trung bình, Yếu… thông qua giá trị của điểm trung bình.
Cách sử dụng hàm Vlookup đơn giản và dễ dàng nhất
Việc phân loại này sẽ khá giống đối với khi ta sử dụng hàm If trong Excel. Nhưng hàm IF trong Excel có trường hợp này sẽ có quá nhiều điều kiện để xem xét vậy nên sẽ rất rắc rối. Dùng hàm VLOOKUP trong Excel hoặc hàm HLOOKUP sẽ giúp bạn trở nên đơn giản hóa hơn rất nhiều.
Công thức hàm Vlookup trong Excel: =VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
Trong đó:
- Lookup_value: là giá trị bạn cần tìm.
- Table_array: là bảng thông tin phụ có chứa các giá trị dò tìm và giá trị có thể quy đổi.
- Col_index_num: là số thứ tự của cột để lấy dữ liệu trong bảng thông tin phụ bạn cần dò tìm.
- Range_lookup: là giá trị Logic (TRUE=1, FALSE=0) có thể quyết định số chính xác hoặc số tương đối đối với bảng giới hạn.
II. Phân biệt HLOOKUP và VLOOKUP
Như bạn đã biết, cả hai hàm HLOOKUP VÀ VLOOKUP trong Excel sử dụng để tìm kiếm một số giá trị. Tuy nhiên chúng khác nhau trong cách hoạt động. Chúng ta dễ dàng có thể nhận ra tên của hai hàm khác nhau ở chữ cái đầu – “H” là ngang (horizontal) và “V” là dọc (vertical)
Do vậy, khi bạn sử dụng hàm VLOOKUP để tìm giá trị theo cột ở phía bên trái trong dữ liệu bạn muốn tìm kiếm. Hàm HLOOKUP dùng để tìm giá trị theo hàng ngang. Nó tìm giá trị ở hàng đầu tiên của bảng để trả lại giá trị ở hàng được chỉ định ở trong cùng một cột.
III. Hàm IF là gì?
Hàm if trong Excel hay còn được gọi là hàm có điều kiện (If: là điều kiện, nếu như)
Cách sử dụng: là hàm được dùng để tìm kiếm những giá trị thỏa mãn các điều kiện được đưa ra trong chuỗi. Đây là hàm cơ bản rất dễ dàng khi sử dụng, khi giá trị chỉ cần thỏa mãn một trong hai điều kiện đã được đưa ra, nếu thỏa mãn thì nó sẽ xuất hiện giá trị “TRUE” trong cú pháp, còn không thỏa mãn thì xuất hiện giá trị “FALSE”
Cú pháp: =IF(logical_test, [value_if_true], [value_if_false])
Trong đó:
- logical_test: điều kiện được đưa ra
- value_if_true: nếu thỏa mãn điều kiện sẽ hiện ra giá trị này
- value_if_false: không thỏa mãn điều kiện sẽ hiện ra giá trị này
Để có thể dễ hiểu hơn thì bạn có thể viết như sau: =IF (điều kiện, giá trị 1, giá trị 2)
Nếu như có thỏa mãn “điều kiện” thì kết quả hàm được trả về là “giá trị 1” và ngược lại trả về “giá trị 2.
IV. Hướng dẫn cách viết hàm Vlookup kết hợp hàm IF
Hướng dẫn cách viết hàm Vlookup kết hợp hàm IF
1. Dùng IF kết hợp VLOOKUP để so sánh giá trị
Yêu cầu bài toán đặt ra:
- Nếu nhân viên ở khu vực Miền Bắc có doanh thu >= 200.000.000 thì thưởng 10%, nếu không được thưởng (trả về số 0).
- Nếu nhân viên ở khu vực Miền Trung có doanh thu >=100.000.000 thì được thưởng (trả về 15%), nếu không sẽ trả về số 0.
- Nếu nhân viên ở khu vực Miền Nam có doanh thu >=220.000.000 thì được thưởng 10%, nếu không đạt doanh thu thì sẽ không thưởng (trả về số 0).
Phân tích yêu cầu:
Thực hiện các thao tác với nhân viên đầu tiên, bạn cần sử dụng hàm IF trong Excel để kiểm tra “doanh thu của nhân viên này” cùng với “doanh thu trong khu vực tương ứng ở bảng Thưởng”, rồi nếu điều kiện của hàm if là đúng thì trả về ô thưởng % (ở cột 3 trong bảng Thưởng), nếu không thì trả về “0”.
Để thực hiện điều kiện của hàm IF trong Excel thì các bạn cần dùng hàm Vlookup để dò tìm “doanh thu khu vực tương ứng trong bảng Thưởng” sau đó trả về doanh thu (cột 2) rồi so sánh cùng với doanh thu của nhân viên.
Để trả về ô thưởng % chính là cột 3 trong bảng Thưởng thì các bạn cũng cần thực hiện hàm Vlookup tương tự trong phần điều kiện hàm IF, tuy nhiên cột trả về để cách sử dụng hàm Vlookup là cột 3.
Cách thực hiện:
Bước 1: Chọn ô đầu tiên trong cột Thưởng % và nhập hàm:
=IF(D6>=VLOOKUP(C6;$C$17:$E$20;2;0);VLOOKUP(C6;$C$17:$E$20;3;0);"0")
Trong hàm:
- D6 chính là ô chứa số liệu mà doanh thu của nhân viên đầu tiên.
- VLOOKUP(C6;$C$17:$E$20;2;0), hàm vlookup này dò tìm ô C6 (khu vực của nhân viên đầu tiên) trong cột khu vực của bảng Thưởng (C17:E20) và trả về cột số 2 (doanh thu) trong bảng thưởng
- VLOOKUP(C6;$C$17:$E$20;3;0) hàm này sẽ trả về kết quả là số % thưởng (cột 3) trong bảng Thưởng nếu điều kiện trong hàm IF đúng.
- "0" được trả về khi điều kiện trong hàm IF bị sai và nhân viên sẽ không được thưởng.
Chú ý: Bạn cần cố định vị trí bảng thưởng C17:E20 nếu như các bạn muốn sao chép công thức cho các nhân viên sau. Để cổ định vị trí ta cần: kéo vùng của bảng Thưởng C17:E20 → nhấn phím F4 → hàm sẽ xuất hiện thêm biểu tượng cố định $C$17:$E$20
Bước 2: Nhấn Enter, ngay lập tức kết quả sẽ được hiển thị
Ví dụ: nhân viên đầu tiên này trong khu vực Miền Bắc tuy nhiên doanh thu nhỏ hơn 200.000.000 vậy nên sẽ không được thưởng và kết quả trả về là 0.
Bước 3: Sao chép công thức hàm xuống các ô khác thì sẽ đưa ra được số phần trăm thưởng
2. Dùng IF để bẫy lỗi cho hàm Vlookup
Chúng ta đã thấy công thức ở ô E2 cho ra kết quả lỗi. Bởi vì trong nội dung ở ô D2 không có giá trị, tức là Lookup_Value sẽ không có nội dung nên sẽ báo lỗi #N/A
Để khắc phục lỗi này thì 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ẽ cách sử dụng hàm Vlookup
3. Dùng IF để tùy biến vị trí cột tham chiếu trong cách sử dụng hàm Vlookup
Trong ví dụ này, chúng ta muốn khi thay đổi điều kiện ở ô E1 để xem xét kết quả của hàm Vlookup trong phần mềm Excel tương ứng với điều kiện này.
- Nếu E1 chính là Số tiền
- Cột Giới tính: Cột 3
Chúng ta sẽ 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 cách sử dụng hàm Vlookup, ở đây chúng ta cần phải mở rộng bảng tham chiếu bao gồm cả cột C
V. Kết hợp VLOOKUP và hàm IF để trả về giá trị TRUE/FALSE hoặc 1 và 0
Ví dụ: Giả sử bạn có 1 danh sách hàng hóa trong kho và số lượng của các loại hàng hoá này thì 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 cùng với số lượng bằng 0 đó là “hết hàng”, điền vào dòng tương ứng cùng với số lượng lớn hơn 0 là “còn hàng” vậy thì chúng ta có thể viết cách dùng hàm VLOOKUP kết hợp với hàm IF như sau:
Cách kết hợp VLOOKUP và hàm IF để trả về giá trị TRUE/FALSE
Công thức trên để tra cứu số lượng của sản phẩm Nho, rồi 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”, còn nếu không kết quả sẽ là “Hết hàng”
1. 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ụ: 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 công ty sẽ 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 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à cách dùng hàm VLOOKUP
2. 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 cách dùng hàm VLOOKUP thì chúng ta sẽ nhận lại kết quả là lỗi #N/A. Để có thể đưa ra một vài thông báo có ý nghĩa hơn cho người sử dụng, chúng ta có cách kết hợp hàm VLOOKUP và hàm IFNA có 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 chính là giá trị trả về ở trong trường hợp có lỗi #N/A
VI. Kết luận
Bài viết trên đây là những kiến thức cơ bản về hàm VLOOKUP cũng như cách kết hợp 2 hàm này để có thể giải một số bài toán trong phần mềm Excel. Để có thể thực hiện tốt việc kết hợp các hàm với nhau thì chúng ta cần nắm rõ được logic của vấn đề trước, rồi mới xác định cách 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 có thể áp dụng cách dùng hàm Vlookup thành công nhé!
Trang chủ 
Cover letter 
Mẫu CV 
Tài khoản 
Thêm