Hướng dẫn ifna vlookup google sheets - ifna vlookup google trang tính
|
Hàm IFNA là một hàm rút gọn của cách sử dụng hàm IF kết hợp với hàm ISNA. Trong bài viết này chúng ta cùng nhau tìm hiểu cách sử dụng hàm IFNA để tránh lỗi #N/A trong Excel nhé. Show
Cấu trúc của hàm IFNASử dụng hàm IFNA thay thế cho cách sử dụng hàm IF kết hợp hàm ISNA Lưu ý: Hàm này chỉ áp dụng trong phiên bản Excel 2013 trở về sau.
=IFNA(value, value_if_na)
Value_if_na: là giá trị thay thế cho thông báo lỗi #N/A nếu có xảy ra lỗi này Sử dụng hàm IFNA thay thế cho cách sử dụng hàm IF kết hợp hàm ISNALưu ý: Hàm này chỉ áp dụng trong phiên bản Excel 2013 trở về sau.
Hàm có cấu trúc như sau: =IFNA(value, value_if_na)
Hàm IFNA chỉ mang mục đích phát hiện, thay thế lỗi #N/A (hay còn gọi là bẫy lỗi #N/A). Bởi vậy nên các trường hợp sử dụng hàm này cũng rất rõ ràng, cụ thể.
Câu lệnh trên có ý nghĩa: Xét lỗi #N/A của hàm VLOOKUP: ISNA(VLOOKUP(B2,$G$3:$H$5,2,0)) Nếu có lỗi, sẽ trả về đoạn text “Không có mã hàng” Nếu không có lỗi, trả về giá trị của hàm VLOOKUP Ở đây ta thấy hàm VLOOKUP lặp lại 2 lần: 1 lần trong mệnh đề kiểm tra lỗi #N/A với hàm ISNA 1 lần trong mệnh đề value_if_false, tức là không xảy ra lỗi #N/A Như vậy chúng ta hoàn toàn có thể rút gọn lại như sau:  Tác giả: duongquan211287· · · · · 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: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. =IF(ISNA(INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0))), “Not found”, INDEX(A2:A10, MATCH(F1, $B$2:$B$10, 0)))
Hàm Vlookup với câu lệnh IfMộ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.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ácMộ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ơnCô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 nhauBê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.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. 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%. Trong đó A2: A10 là tên người bán và C2: C10 là doanh số bán hàng.Not Found. 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. 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.Not Found (Không tìm thấy) sẽ xuất hiện thay vì lỗi # N / A: 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%.
IFNA VLOOKUP trong các phiên bản Excel mới hơnTrong 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ề 0Khi 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.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)) Đâ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:Excel và hẹn gặp lại ở bài viết sau. |
