Nêu Cách Sử Dụng Hàm VLOOKUP IF AND RIGHT? - Nguyen Ngoc

Trong Đó:

lookup_value: Giá trị dùng để dò tìm

table_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4)

col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.

range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).

VÍ DỤ VỀ HÀM VLOOKUP TRONG EXCEL

1. Cách Dò Tìm Tương Đối:

VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách dưới:

Ví dụ về hàm Vlookup excel

Ta sử dụng công thức ở cột D6 là: =VLOOKUP(C6,$C$16:$D$20,2,1)

Kết quả thu được:

Kết quả khi dùng hàm vlookup

2. Cách Dò Tìm Tuyệt Đối.

Với cách dò tìm tuyệt đối thì bạn sẽ tìm được chi tiết hơn cách dò tìm tương đối.

VD: Điền thông tin Quê quán và trình độ của các nhân viên vào bảng căn cứ vào mã nhân viên đã cho tương ứng phía dưới.

Ví dụ dò tìm tuyệt đối khi dùng hàm vlookup

Để điền thông tin quê quán của nhân viên ta dùng công thức Vlookup cho ô E6 như sau: =VLOOKUP(A6,$D$12:$F$17,2,0)

A6 là giá trị đem dò tìm

$D$12:$F$17 là bảng dò tìm

2 : số thứ tự cột dữ liệu trên bảng dò

0 : Kiểu dò tìm chính xác

Cách sử dụng dò tuyệt đối với hàm Vlookup

Tương tự để điền vào ô trình độ của nhân viên ta làm như sau:

Với ô F6 có công thức : =VLOOKUP(A6,$D$12:$F$17,3,0)

Kết quả dùng hàm Vlookup dò giá trị tuyệt đối

Giá trị tương đối và giá trị tuyệt đối

Với giá trị tuyệt đối bạn sẽ tìm kiếm được kết quả chính xác nhất, các giá trị tuyệt đối sẽ không bị thay đổi địa chỉ dù xuất hiện ở các cột, ô mới. Do đó chúng ta sử dụng giá trị tuyệt đối trong trường hợp cần tìm chính xác nhất thông tin và sử dụng giá trị trương đối trong trường hợp tìm kiếm các kết quả chính xác, gần chính xác nhất. Để tìm hiểu thêm, bạn đọc có thể tham khảo thêm bài viết về tham chiếu các hàm trong Excel nhé.

HÀM VLOOKUP KẾT HỢP HÀM HLOOKUP, LEFT, RIGHT, MATCH

Ở nội dung trên Taimienphi.vn đã hướng dẫn bạn tìm hiểu cơ bản về hàm Vlookup, để giúp các bạn hiểu hơn và áp dụng thực tế hơn, bây giờ chúng tôi sẽ đưa ra một tình huống bài tập cụ thể như sau. Bài toán này là sự kết hợp giữa hàm Vlookup và các hàm trong Excel như hàm Hlookup, hàm Left, hàm Right và hàm Match.

Cho dữ liệu như bảng dưới đây với quy ước như sau: - Bảng 1: BẢNG THỐNG KÊ TIÊU THỤ GẠO - Bảng 2: BẢNG TRA TÊN HÀNG, TÊN HÃNG SẢN XUẤT VÀ ĐƠN GIÁ

Yêu Cầu Cần Giải Quyết:

Câu 1: Căn cứ vào 2 ký tự bên trái và 2 ký tự bên phải của Mã SP trong Bảng 1, hãy tra trong Bảng 2 đề điền giá trị cho cột Tên Hàng - Tên Hãng Sản xuất (Tính dữ liệu cho cột C5:C10). VD: KD-NĐ Tức là gạo Khang Dân - Nam Định. Câu 2: Hãy điền Đơn Giá cho mỗi mặt hàng đựa vào Mã SP ở Bảng 1 và tra ở Bảng 2. (Tính dữ liệu cho cột D5:D10). Câu 3: Tính thành tiền = Số lượng * Đơn giá

Đáp Án:

Câu 1: - Giải thích: Chúng ta cần đưa ra được công thức lấy dữ liệu Tên Hàng và Tên Tỉnh Sản xuất, sau đó ghép 2 công thức tính toán này lại thì sẽ ra được đáp án Câu 1. + Lấy Tên hàng: Lấy 2 ký tự bên trái trong cột Mã SP ở Bảng 1 (A5:A10) so với 2 ký tự trong cột Mã Hàng ở Bảng 2, nhưng vùng dữ liệu ta lấy sang cả cột Tên Hàng ở Bảng 2 (A15:B20) hoặc (A15:E20). >> Ta dùng Hàm Vlookup: Tại một ô nào đó chưa có dữ liệu, bạn nhập thử công thức sau: =VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE) Sau khi nhập xong, bấm Enter, nếu ra từ "Khang Dân", tức là bạn đã hoàn thiện được 40% đáp án Câu 1 này rồi đó, đơn giản phải không nào, tiếp tục nhé. + Lấy Tên Tỉnh Sản xuất: Lấy 2 ký tự bên phải trong cột Mã SP(A5:A10) ở Bảng 1 so với 2 ký tự trong "Hàng" Mã gạo - Tên tỉnh sản xuất ở Bảng 2, vùng dữ liệu lấy sẽ (A16:E20). >> Lấy dữ liệu so theo hàng nên ta dùng hàm Hlookup. Cũng tại 1 ô chưa có dữ liệu, bạn nhập thử công thức sau: =HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE) Sau khi nhập xong, bạn cũng tiếp tục bấm Enter, nếu ra từ "Nam Định", tức là bạn đã hoàn thiện được thêm 40% đáp án Câu 1 rồi. Lúc này bạn đã hình dung ra đáp án rồi đúng không nào. Công việc tiếp theo ghép 2 hàm này lại để tính toán được dữ liệu trong cột Tên Hàng - Tên Tỉnh Sản xuất + Ghép 2 công thức: Có nhiều công thức để ghép hay nối chuỗi lại với nhau, ở bài toán này Taimienphi.vn hướng dẫn các bạn cách nối chuỗi sử dụng hàm &. Trong bài toán này, chúng tôi sẽ sử dụng dấu gạch ngang (có dấu cách trống) để ngăn cách 2 công thức đã tính toán ra kết quả ở trước, tức là Tên Hàng và Tên Tỉnh Sản Xuất, cụ thể là: " - " Tổng quan lại thì hàm sẽ như sau =+vlookup&" - "&hlookup (Không còn dấu = ở đầu hàm Hlookup nữa nhé) Lúc đó, tại C5, bạn nhập công thức: C5=+VLOOKUP(LEFT(A5,2),$A$15:$B$20,2,FALSE)&" - "&HLOOKUP(RIGHT(A5,2),$C$16:$E$20,2,FALSE) Bấm Enter để xem kết quả, Nếu ra chuẩn đáp án thì phải là "Khang Dân - Nam Định" Kết quả đã ok, ở ô tiếp theo là C6:C10, bạn chỉ cần trỏ chuột vào cạnh công thức ở ô C5 và kéo xuống C10, là xong Tương tự thế,

C6=+VLOOKUP(LEFT(A6,2),$A$15:$B$20,2,FALSE)&" - "&HLOOKUP(RIGHT(A6,2),$C$16:$E$20,2,FALSE) Thế là đã xong Câu 1 của yêu cầu bài toán đưa ra rồi, tiếp tục chúng ta giải đáp Câu 2 các bạn nhé Câu 2: Tính Đơn giá Công thức sẽ như sau: Tại ô D5 các bạn nhập công thức

D5=+VLOOKUP(LEFT(A5,2),$A$16:$E$20,MATCH(RIGHT(A5,2),$A$16:$E$16,0),FALSE) Câu 3: Tính thành tiền Ồ, câu hỏi này thường xuất hiện trong những bài toán tính toán dữ liệu như bảng lương, chi phí, tổng thành tiền, ... Khá là dễ rồi phải không các bạn. Công thức tính thành tiền sẽ như sau, Tại ô F5 các bạn nhập E5=+D5*E5

SỬA LỖI #NA HÀM VLOOKUP

Lỗi #NA là gì?

Lỗi #NA được trả về trong công thức excel khi không tìm thấy giá trị thích hợp

Khi sử dụng hàm Vlookup, chúng ta gặp lỗi #NA khi không tìm thấy điều kiệm tìm kiếm trong vùng điều kiến, chính xác ở đây là cột đầu tiên trong vùng điều kiện của hàm Vlookup

Với hai cách dò tìm tương đối và tuyệt đối của hàm Vlookup, hàm dò tìm giá trị theo cột bạn dễ dàng thống kê dữ liệu trên bảng tính Excel. Vlookup giúp cho việc thống kê các chi tiết để làm báo cáo lọc ra các danh sách cần thiết, công việc của bạn sẽ chính xác và rút ngắn thời gian hơn. Đây là hàm được dùng khá nhiều trong quá trình thao tác trên bảng tính.

Để học tốt cũng như hiểu rõ hơn về hàm tìm kiếm bạn có thể tham khảo hàm Hlookup trong Excel đã được Taimienphi.vn giới thiệu. Hàm Hllookup giúp bạn tìm kiếm và tham chiếu theo cột, hoặc theo dòng, Khi bạn sử dụng hàm này, nếu thỏa mãn điều kiện đặt ra.

Bên cạnh hàm tìm kiếm Vlookup trong Excel, bạn có thể tham khảo Hàm COUNTIF trong Excel, Hàm COUNTIF là một hàm đếm khá tiện lợi, với những điều kiện ban đầu, Hàm COUNTIF sẽ cho bạn những kết quả chính xác nhất.

Hàm tính toán cũng có khá nhiều hàm khác nhau trong Excel, và Hàm SUMIF là một trong những hàm được nhiều người sử dụng, Hàm SUMIF là hàm tính tổng có điều kiện, giúp bạn tính tổng các cột, các hàng riêng lẻ mà không phải tính tổng tất cả các dữ liệu trong file Excel

Sử dụng Excel không thể không nhắc tới Hàm MID, đây là hàm cơ bản trong Excel hỗ trợ cắt những chuỗi ký tự trong một dãy ký tự cho trước trong bảng tính Excel, hàm cơ bản MID được sử dụng rất nhiều trong các trường hợp cần lấy ra những ký tự đặc biệt nhất trong các chuỗi ký tự dài có thể lên tới hàng trăm ký tự

Hàm IF cho phép bạn tạo một so sánh lô-gic giữa một giá trị và một giá trị dự kiến bằng cách kiểm tra một điều kiện và trả về kết quả rằng điều kiện đó là True (đúng) hay False (sai).

• =IF(Điều gì đó là True, thì làm gì, nếu không thì làm gì khác)

Nhưng nếu bạn cần kiểm tra nhiều điều kiện, mà chúng ta hãy nói gì tất cả điều kiện cần phải là True hoặc False (và), hoặc chỉ một điều kiện cần phải là True hoặc False (hoặc), hoặc nếu bạn muốn kiểm tra nếu điều kiện nào không đáp ứng tiêu chí của bạn? Tất cả các hàm 3 có thể được sử dụng dung, nhưng sẽ phổ biến để xem chúng từng cặp với hàm IF.

Chi tiết Kỹ thuật

Dưới đây là tab Tổng quan về cách lập cấu trúc và, hoặc và không hàm riêng lẻ. Khi bạn kết hợp từng của chúng với câu lệnh IF, họ đọc như thế này:

• AND – =IF(AND(Điều gì đó là True, Điều gì đó khác là True), Giá trị nếu True, Giá trị nếu False)

• OR – =IF(OR(Điều gì đó là True, Điều gì đó khác là True), Giá trị nếu True, Giá trị nếu False)

• NOT – = IF (NOT(Điều gì đó là True), Giá trị nếu True, Giá trị nếu False)

Ví dụ

Sau đây là ví dụ về một số thông thường lồng IF(AND()), IF(OR()) và IF(NOT()) câu lệnh. AND và hoặc chức năng có thể hỗ trợ tối đa 255 điều kiện riêng lẻ, nhưng nó không nên dùng nhiều hơn một vài vì công thức phức tạp, lồng nhau có thể rất khó để xây dựng, kiểm tra và duy trì. Hàm NOT chỉ cần một điều kiện.

Dưới đây là cách diễn giải các công thức theo lô-gic:

Công thức

Mô tả

=IF(AND(A2>0,B2<100),TRUE, FALSE)

Nếu A2 (25) lớn hơn 0, và B2 (75) là nhỏ hơn 100, sau đó trả về TRUE, nếu không trả về FALSE. Trong trường hợp này cả hai điều kiện có đúng, nên trả về TRUE.

=IF(AND(A3="Red",B3="Green"),TRUE,FALSE)

Nếu A3 ("màu lam") = "Màu đỏ" và B3 ("màu xanh lá cây") bằng "Màu xanh lá cây" rồi trả về TRUE, nếu không trả về FALSE. Trong trường hợp này chỉ có điều kiện đầu tiên là true, do vậy trả về FALSE.

=IF(OR(A4>0,B4<50),TRUE, FALSE)

Nếu A4 (25) lớn hơn 0, hoặc B4 (75) là nhỏ hơn 50, sau đó trả về TRUE, nếu không trả về FALSE. Trong trường hợp này, chỉ có điều kiện đầu tiên là TRUE, nhưng vì hoặc chỉ cần một đối số là true công thức trả về TRUE.

=IF(OR(A5="Red",B5="Green"),TRUE,FALSE)

Nếu A5 ("màu lam") bằng "Màu đỏ", hoặc B5 ("màu xanh lá cây") bằng "Màu xanh lá cây" rồi trả về TRUE, nếu không trả về FALSE. Trong trường hợp này, đối số thứ hai là True, do vậy công thức trả về TRUE.

=IF(NOT(A6>50),TRUE,FALSE)

Nếu A6 (25) không lớn hơn 50, sau đó trả về TRUE, nếu không trả về FALSE. Trong trường hợp này 25 là không lớn hơn 50, vì vậy công thức trả về TRUE.

=IF(NOT(A7="Red"),TRUE,FALSE)

Nếu (IF) A7 (“Blue”) không (NOT) là “Red” thì trả về TRUE, nếu không thì trả về FALSE.

Lưu ý rằng tất cả các ví dụ có dấu đóng ngoặc sau khi các điều kiện tương ứng được nhập. Các đối số đúng/sai còn lại bên trái rồi như là một phần của câu lệnh IF ngoài. Bạn cũng có thể thay thế văn bản hoặc các giá trị số cho các giá trị đúng/sai được trả về các ví dụ.

Sau đây là một số ví dụ về việc sử dụng AND, OR và NOT để đánh giá các ngày.

Dưới đây là cách diễn giải các công thức theo lô-gic:

Công thức

Mô tả

=IF(A2>B2,TRUE,FALSE)

Nếu A2 lớn hơn B2, trả về TRUE, nếu không trả về FALSE. 03/12/14 là lớn hơn 01/01/14, do vậy công thức trả về TRUE.

=IF(AND(A3>B2,A3<C2),TRUE,FALSE)

Nếu A3 lớn hơn A3 B2 và nhỏ hơn C2, trả về TRUE, nếu không trả về FALSE. Trong trường hợp này cả hai đối số là true, vì vậy công thức trả về TRUE.

=IF(OR(A4>B2,A4<B2+60),TRUE,FALSE)

Nếu A4 lớn hơn B2 hoặc A4 là nhỏ hơn B2 + 60, trả về TRUE, nếu không trả về FALSE. Trong trường hợp này đối số đầu tiên là true, nhưng thứ hai là false. Từ hoặc chỉ cần một trong các đối số là true, công thức trả về TRUE. Nếu bạn sử dụng hướng dẫn đánh giá công thức từ tab công thức, bạn sẽ thấy cách Excel đánh giá công thức.

=IF(NOT(A5>B2),TRUE,FALSE)

Nếu A5 không lớn hơn B2, sau đó trả về TRUE, nếu không trả về FALSE. Trong trường hợp này, A5 là lớn hơn B2, do vậy công thức trả về FALSE.

Sử dụng AND, OR và NOT cùng Định dạng Có điều kiện

Bạn cũng có thể sử dụng AND, OR và không để đặt tiêu chí định dạng có điều kiện với tùy chọn công thức. Khi bạn thực hiện điều này bạn có thể bỏ qua các hàm IF và sử dụng AND, OR và không có trên riêng của họ.

Từ trang đầu tab, hãy bấm định dạng có điều kiện > quy tắc mới. Tiếp theo, chọn tùy chọn "sử dụng công thức để xác định những ô nào cần định dạng", hãy nhập công thức của bạn và áp dụng định dạng lựa chọn của bạn.

Sử dụng ví dụ Ngày khi nãy, sau đây là những công thức cần dùng.

Công thức

Mô tả

=A2>B2

Nếu A2 lớn hơn B2 thì định dạng ô đó, nếu không thì không làm gì cả.

=AND(A3>B2,A3<C2)

Nếu A3 lớn hơn B2 và (AND) A3 nhỏ hơn C2 thì định dạng ô đó, nếu không thì không làm gì cả.

=OR(A4>B2,A4<B2+60)

Nếu A4 lớn hơn B2 hoặc (OR) A4 nhỏ hơn B2 cộng 60 (ngày) thì định dạng ô đó, nếu không thì không làm gì cả.

=NOT(A5>B2)

Nếu A5 không lớn hơn B2, định dạng ô đó, nếu không làm gì. Trong trường hợp này A5 là lớn hơn B2, vì vậy kết quả sẽ trả về FALSE. Nếu bạn cần thay đổi công thức để =NOT(B2>A5) nó sẽ trả về TRUE và ô sẽ được định dạng.