CÁCH KẾT HỢP HÀM VLOOKUP VỚI CÂU LỆNH IF - IF ISNA ...

Bài viết dưới đây, Học Excel Online sẽ hướng dẫn cách kết hợp hàm Vlookup và câu lệnh IF trong Excel để tra cứu một hoặc nhiều điều kiện. Ngoài ra bài viết cũng sẽ đề cập đến cách sử dụng công thức IF ISNA VLOOKUP để thay thế lỗi #N/A bằng văn bản riêng hoặc bằng số 0 hay ô trống.

Mặc dù hàm Vlookup và IF đều có những hữu ích riêng nhưng khi kết hợp chúng lại có những tính ứng dụng hữu ích hơn thế. 

Hàm Vlookup với câu lệnh If

Một trong những tình huống phổ biến nhất khi kết hợp IF và Vlookup với nhau là so sánh các giá trị mà hàm Vlookup trả về với giá trị mẫu và sau đó trả về kết quả Yes/No hoặc True/False.

Công thức chung như sau:

IF (VLOOKUP (…) = sample_value , TRUE, FALSE)

Công thức trả về TRUE nếu giá trị trả về bằng giá trị mẫu, FALSE nếu không bằng giá trị mẫu.

Dưới đây là một vài ví dụ thực tế về công thức Vlookup kết hợp với IF.

Ví dụ 1: So sánh kết quả Vlookup với một giá trị cụ thể

Giả sử chúng ta có danh sách các mặt hàng trong cột A và số lượng trong cột B. Bạn muốn kiểm tra số lượng mặt hàng trong E1 là còn hàng hay hết hàng.

Bạn kéo số lượng bằng hàm Vlookup với công thức đối sánh chính xác như sau:

=VLOOKUP(E1,$A$2:$B$10,2,FALSE)

Sau đó, viết một câu lệnh IF so sánh kết quả của Vlookup với 0 và trả về “ NO” nếu nó bằng 0, hoặc là “ YES”:

=IF(VLOOKUP(E1,$A$2:$B$10,2,FALSE)=0,”No”,”Yes”)

Thay vì YES / NO , bạn có thể trả lại TRUE / FALSE hoặc  SOLD OUT/ IN STOCK (bán hết/ còn hàng) hoặc bất kỳ hai lựa chọn nào khác. Ví dụ:

=IF(VLOOKUP(E1,$A$2:$B$10,2)=0,”Sold out”,”In stock”)

Bạn cũng có thể so sánh giá trị mà Vlookup trả về với văn bản mẫu  (sample text). Trong trường hợp này, hãy đảm bảo đặt một chuỗi văn bản trong dấu ngoặc kép, như sau:

=IF(VLOOKUP(E1,$A$2:$B$10,2)=”sample text”,TRUE,FALSE)

Ví dụ 2: So sánh kết quả Vlookup với một ô khác

Một ví dụ khác của Vlookup kết hợp với điều kiện IF là so sánh kết quả đầu ra của Vlookup với giá trị của một ô khác. Chẳng hạn chúng ta có thể kiểm tra xem nó lớn hơn hay bằng một giá trị trong ô G2:

=IF(VLOOKUP(E1,$A$2:$B$10,2)>=G2,”Yes!”,”No”)

Kết quả:

Ví dụ 3: Các giá trị Vlookup trong danh sách ngắn hơn

Công thức IF ISNA VLOOKUP dùng để so sánh từng ô trong cột mục tiêu với danh sách khác. Nếu tìm thấy kết quả phù hợp trả về TRUE hoặc YES, nếu kết quả không phù hợp trả về FALSE hoặc NO.

IF( ISNA( VLOOKUP(…)),”No”,”Yes”)

Nếu Vlookup dẫn đến lỗi # N / A, công thức trả về “NO”, nghĩa là không tìm thấy giá trị tra cứu trong danh sách tra cứu. Nếu kết quả khớp được tìm thấy, “YES” sẽ được trả về. Ví dụ:

=IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)),”No”,”Yes”)

Nếu nghiệp vụ của bạn yêu cầu kết quả ngược lại, chỉ cần hoán đổi “YES” và “NO” để đảo ngược logic của công thức:

=IF(ISNA(VLOOKUP(A2,$D$2:$D$4,1,FALSE)),”Yes”,”No”)

Công thức IF VLOOKUP để thực hiện các phép tính khác nhau

Bên cạnh chức năng trên, hàm IF với câu lệnh Vlookup còn có thể thực hiện các phép tính khác nhau dựa trên những tiêu chí mà bạn chỉ định.

Lấy ví dụ rõ hơn, tính toán hoa hồng của một người bán cụ thể (F1) tùy thuộc vào sự hiệu quả công việc: 20% hoa hồng cho những người kiếm được 200 đô la trở lên, 10% cho những người khác.

Đối với điều này thì bạn kiểm tra xem giá trị mà Vlookup trả về có lớn hơn hoặc bằng 200 hay không và nếu có thì nhân nó với 20%, nếu không thì nhân với 10%.

=IF(VLOOKUP(F1,$A$2:$C$10,3,FALSE)>=200, 

VLOOKUP(F1,$A$2:$C$10,3,FALSE)*20%, 

VLOOKUP(F1,$A$2:$C$10,3,FALSE)*10%)

Trong đó A2: A10 là tên người bán và C2: C10 là doanh số bán hàng.

Công thức IF ISNA VLOOKUP để ẩn lỗi #N/A

Nếu hàm VLOOKUP không tìm thấy một giá trị được chỉ định thì sẽ xuất hiện lỗi # N / A. Để ẩn lỗi đó và thay thế nó bằng văn bản của riêng thì có thể lồng công thức Vlookup vào hàm IF:

IF (ISNA (VLOOKUP (…)), “Not found”, VLOOKUP (…))

Chúng ta có thể nhập bất kỳ các ký tự hoặc văn bản khác thay vì Not Found.

Giả sử bạn có danh sách tên người bán trong một cột và số tiền bán hàng trong cột khác. Nhiệm vụ của bạn là kéo số tiền tương ứng với tên người dùng nhập vào F1. Nếu tên không xuất hiện thì sẽ hiển thị thông báo.

Với các tên trong A2: A10 và số lượng C2: C10, công việc có thể được thực hiện với công thức IF Vlookup như sau:

=IF(ISNA(VLOOKUP(F1,$A$2:$C$10,3,FALSE)),”Notfound”, VLOOKUP(F1,$A$2:$C$10,3,FALSE))

Nếu tên được tìm thấy thì số tiền bán hàng tương ứng sẽ xuất hiện:

Nếu không tìm thấy giá trị tra cứu, thông báo Not Found (Không tìm thấy) sẽ xuất hiện thay vì lỗi # N / A:

Công thức này hoạt động như thế nào?

Logic của công thức rất đơn giản: bạn sử dụng hàm ISNA để kiểm tra lỗi # N / A trên Vlookup. Nếu xảy ra lỗi, ISNA trả về TRUE, còn ngược lại sẽ trả về FALSE. Các giá trị trên sẽ kiểm tra logic của hàm IF như sau:

• Nếu kiểm tra logic là TRUE (lỗi # N / A), thông báo của bạn sẽ được hiển thị.
• Nếu kiểm tra logic là FALSE (giá trị tra cứu được tìm thấy), Vlookup trả về kết quả khớp bình thường.

IFNA VLOOKUP trong các phiên bản Excel mới hơn

Trong Excel for Office 365, Excel 2019, Excel 2016 và 2013, bạn có thể sử dụng hàm IFNA thay vì IF ISNA để phát hiện và xử lý lỗi # N / A:

IFNA (VLOOKUP (…), ” NOT FOUND “)

Trong ví dụ, công thức có dạng như sau:

=IFNA(VLOOKUP(F1,$A$2:$C$10,3, FALSE), “Not found”)

Chú ý: Nếu bạn muốn tìm thấy tất cả các loại lỗi, không chỉ lỗi #N/A thì sử dụng hàm Vlookup kết hợp với hàm IFERROR. 

Excel Vlookup: nếu không tìm thấy trả về 0

Khi không tìm thấy giá trị tra cứu và muốn trả về số 0 thì bạn có thể sử dụng công thức IF ISNA VLOOKUP như đã nói ở trên nhưng có một chút thay đổi. Thay vì một văn bản (text) thì bạn điền số 0 vào đối số value_if_true của hàm IF.

IF (ISNA (VLOOKUP (…)), 0, VLOOKUP (…))

Trong bảng mẫu của chúng tôi công thức như sau:

=IF(ISNA(VLOOKUP(F2,$A$2:$C$10,3,FALSE)), 0, VLOOKUP(F2,$A$2:$C$10,3,FALSE))

Trong các phiên bản gần đây của Excel 2016 và 2013, bạn có thể sử dụng lại kết hợp IFNA Vlookup:

=IFNA(VLOOKUP(I2,$A$2:$C$10,3, FALSE), 0)

Excel Vlookup: nếu không tìm thấy, hãy trả về ô trống

Đây là một biến thể khác của câu lệnh “Vlookup if then”. Để thực hiện nhập vào công thức chuỗi trống(“”) thay vì lỗi #N/A.

IF (ISNA (VLOOKUP (…)), “”, VLOOKUP (…))

Dưới đây là ví dụ công thức hoàn chỉnh:

Đối với tất cả các phiên bản Excel:

=IF(ISNA(VLOOKUP(F2,$A$2:$C$10,3,FALSE)), “”, VLOOKUP(F2,$A$2:$C$10,3,FALSE))

Đối với Excel 2016 và Excel 2013:

=IFNA(VLOOKUP(F2,$A$2:$C$10,3, FALSE), “”)

If với Index Match – vlookup trái với điều kiện If

Hàm Vlookup không phải là cách duy nhất thực hiện tra cứu theo chiều dọc trong Excel. Chúng ta có thể sử dụng hàm INDEX MATCH cho mục đích này và thậm chí còn mạnh mẽ và linh hoạt hơn. Các đối sánh chỉ mục trong có thể hoạt động với IF tương tự như Vlookup.

Ví dụ: bạn có số đơn đặt hàng trong cột A và tên người bán trong cột B. Vậy làm cách nào để kéo số đơn đặt hàng sang cho một người bán cụ thể.

Vlookup không thể sử dụng trong trường hợp này vì không thể tìm kiếm từ phải sang trái. Đối sánh chỉ mục sẽ hoạt động miễn giá trị tra cứu được tìm thấy trong cột tra cứu nếu không lỗi #N/A sẽ hiển thị. Để thay thế ký lỗi bằng ký hiệu riêng thì lồng INDEX vào bên trong IF ISNA:

=IF(ISNA(INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0))), “Not found”, INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0)))

Trong Excel 2016 và 2016, bạn có thể sử dụng IFNA thay vì IF ISNA để làm cho công thức nhỏ gọn hơn:

=IFNA(INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0)), “Not found”)

Theo cách tương tự, bạn có thể sử dụng Đối sánh chỉ mục (Index) trong các công thức If khác.

Trên đây là cách sử dụng Vlookup kết hợp với câu lệnh IF trong Excel. Để có hiểu rõ hơn các công thức được thảo luận trong hướng dẫn này thì bạn có thể tải xuống các biểu mẫu làm việc trên. Hy vọng những thông tin trên sẽ mang đến cho các bạn kiến thức bổ ích về Excel và hẹn gặp lại ở bài viết sau.