Cách sử dụng hàm tìm kiếm có điều kiện trong excel cực đơn giản

Nếu bạn là một nhân viên văn phòng, giáo viên hay kế toán thì bạn thường xuyên phải làm việc và thao tác với các con số trên bảng tính Excel. Làm cách nào để có thể tìm kiếm dữ liệu theo điều kiện nhanh chóng theo tên, chuỗi ký tự hay giá trị số nào đó để bạn có thể dễ dàng phục vụ cho nhu cầu công việc và quản lí dữ liệu hiệu quả. Trong bài viết dưới đây, Đào tạo tin học sẽ chia sẻ đến các bạn Cách sử dụng các hàm tìm kiếm có điều kiện trong excel một cách đơn giản và dễ dàng để thực hiện, cùng xem hướng dẫn chi tiết dưới đây nhé!

Khóa học Tin học văn phòng tại Daotaotinhoc.vn bao gồm Microsoft Excel, Microsoft Word là các khóa học chuyên sâu kiến thức và cách sử dụng các công cụ tin học phiên bản 2013 do giảng viên – chuyên gia đầu ngành Nguyễn Quang Vinh hướng dẫn. Đồng thời anh cũng là giảng viên đứng Top các từ khóa về đào tạo tin học trên Google, Youtube như: Đào tạo tin học AB, Đào tạo tin học A, Đào tạo tin học B, video tin học B văn phòng, … Khóa học Tin học văn phòng cơ bản sẽ trang bị cho bạn đầy đủ các kỹ năng về soạn thảo văn bản Word, bảng tính Excel với học phí rất hấp dẫn.

ĐĂNG KÍ KHÓA HỌC MICROSOFT EXCEL CƠ BẢN ĐẾN NÂNG CAO

Bạn đang đọc: Cách sử dụng hàm tìm kiếm có điều kiện trong excel cực đơn giản

ĐĂNG KÍ KHÓA HỌC MICORSOFT WORD CƠ BẢN

Mục Chính
CÁC HÀM TÌM KIẾM CÓ ĐIỀU KIỆN TRONG EXCEL
1. Hàm VLOOKUP
2. Hàm VLOOKUP 2 ĐIỀU KIỆN
Tạo cột phụ
Sử dụng công thức mảng
3. Sử dụng tích hợp hàm INDEX / MATCH
Nên sử dụng hàm tìm kiếm có điều kiện kèm theo trong Excel nào ?
Lỗi thường gặp khi dùng hàm tìm kiếm có điều kiện kèm theo trong excel
Hàm VLOOKUP
Lỗi hiển thị # N / A
Lỗi hiển thị # REF !
Lỗi hiển thị # VALUE !

CÁC HÀM TÌM KIẾM CÓ ĐIỀU KIỆN TRONG EXCEL

1. Hàm VLOOKUP

Thông thường, để tìm kiếm dữ liệu thỏa mãn nhu cầu điều kiện kèm theo trong một khoanh vùng phạm vi hay một bảng tính Excel thì tất cả chúng ta thường hay nghĩ đến hàm VLOOKUP. Vì đây là hàm tìm kiếm có điều kiện kèm theo trong excel với công dụng chính dùng để tìm kiếm giá trị trong một bảng tính cho trước .– Hàm tìm kiếm giá trị trong Excel– Hàm VLOOKUP có điều kiện kèm theoCông thức:

=VLOOKUP(Giá trị bạn muốn dò tìm, Vùng chứa dữ liệu [Cột dò tìm + Cột kết quả], Số cột trong ô bạn muốn trả về, trả về kết quả chính xác/tương đối gần đúng trong đó 1/TRUE hoặc 0/FALSE).

Nếu giá trị TRUE được bỏ qua thì kết quả khớp tương đối gầ đúng được trả về. Tức là nếu kết quả chính xác mà không được tìm thấy thì hàm VLOOKUP sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn giá trị bạn muốn dò tim.

Lưu ý khi sử dụng hàm tìm kiếm có điều kiện trong excel – hàm VLOOKUP:

Có 2 kiểu tìm kiếm khi dùng hàm VLOOKUP là tìm kiếm trả về kết quả chính xác và tìm kiếm trả về kết quả tương đối.

Khi bạn muốn tìm kiếm theo kiểu trả về kết quả tương đối thì danh sách tìm kiếm phải được sắp xếp

Hàm VLOOKUP sẽ tìm kiếm giá trị từ trái qua phải

Công thức hàm VLOOKUP có thể kết hợp được với nhiều hàm khác nhau như IF, CHOOSE, ..

Khi sử dụng hàm VLOOKUP, bạn cần cố định điều kiện tìm kiếm và bảng tìm kiếm một cách linh hoạt, phù hợp

Số cột cần tìm kiếm nhỏ hơn hoặc bằng số cột của bảng tìm kiếm

Ví dụ sử dụng hàm VLOOKUP tìm kiếm có điều kiện dữ liệu trong Excel:

Để tính phụ cấp của nhân viên cấp dưới được lãnh trong đợt dịch Covid 19 do Công ty quyết định hành động phụ cấp tùy theo từng chức vụ. Dưới đây là list nhân viên cấp dưới và chức vụ, Sếp nhu yếu bạn tính mức phụ cấp cho từng nhân viên cấp dưới với chúc vụ tương ứng để được hưởng phụ cấp theo lao lý của Công ty .

Đối với những bạn kế toán không biết sử dụng hàm tìm kiếm có điều kiện kèm theo trong excel thì những bạn sẽ dò tìm từng chức vụ của từng nhân viên cấp dưới, sau đó dò tìm lại từng chức vụ tương ứng từng mức phụ cấp. Bạn tìm kiếm đúng từng mức phụ cấp tương ứng với từng chức vụ, sau đó điền vào để xác lập mức phụ cấp. Tuy nhiên cách sử dụng này khá là mất thời hạn, nếu bảng tính chỉ có một vài nhân viên cấp dưới thì bạn hoàn toàn có thể triển khai nhanh, nhưng với Công ty có hàng nghìn người thì thao tác bằng tay thủ công này không hề triển khai, nhiều lúc hiệu quả dò tìm của bạn hoàn toàn có thể có sai sót .Với hàm VLOOKUP, bạn hoàn toàn có thể triển khai cách đơn thuần vừa nhanh vừa đúng chuẩn như sau :Tại ô D4, bạn điền công thức : = VLOOKUP ( C4, USD H USD 2 : USD I $ 9,2,0 ) .

Dấu USD trong công thức được sử dụng để cố định và thắt chặt những dòng, những cột của bảng 2 khi bạn copy công thức sang những ô khác mà không bị biến hóa dữ liệu. Còn số 2 là thứ tự của cột dữ liệu bạn cần lấy giá trị mức phụ cấp tương ứng. Ở cuối công thức có hiệu quả trả về = 0 tức là để dò tìm tác dụng đúng chuẩn .

Xem thêm: Hướng dẫn sử dụng hàm Vlookup trong Excel

2. Hàm VLOOKUP 2 ĐIỀU KIỆN

Đối với công thức hàm VLOOKUP thường thì ở trên, bạn hoàn toàn có thể thấy được hàm chỉ dò tìm được 1 vùng dữ liệu tham chiếu, 1 giá trị hoặc 1 chuỗi văn bản. Với những điều kiện kèm theo tìm kiếm dữ liệu có nhiều ô, nhiều giá trị khác nhau và nhiều chuỗi văn bản, nhiều giá trị thì hàm VLOOKUP 2 điều kiện kèm theo sau đây sẽ giúp bạn thuận tiện thực thi. Nếu bạn sử dụng hàm tìm kiếm có điều kiện kèm theo trong excel với hàm VLOOKUP 2 điều kiện kèm theo, thì bạn hoàn toàn có thể thuận tiện lựa chọn sử dụng với 2 cách là dùng cột phụ và dùng công thức mảng excel. Mỗi cách triển khai đều có ưu và điểm yếu kém riêng không liên quan gì đến nhau, cũng tùy vào trường hợp mà bạn hoàn toàn có thể xem xét lựa chọn sử dụng cách nào .

Tạo cột phụ

Cách thực thi đơn thuần khi sử dụng hàm VLOOKUP tìm kiesm 2 điều kiện kèm theo là tạo cột phụ. Từ những điều kiện kèm theo trong bảng tính, bạn chỉ cần tổng hợp lại và dùng hàm VLOOKUP để tìm kiếm 2 điều kiện kèm theo. Tùy vào những nhu yếu đơn cử mà bạn sẽ ghép những cột điều kiện kèm theo lại theo cách khác nhau, thường thì hàm được sử dụng để ghép những điều kện lại với nhau bằng kí hiệu “ và ” .Ví dụ : Mã nhân viên cấp dưới được ghép từ 2 cột như hình bên dưới. Bạn sẽ ghép điều kiện kèm theo bằng cách tạo công thức với kí hiệu “ và ” như sau : = B4 và C4 .

Ví dụ các bước sử dụng hàm VLOOKUP 2 điều kiện để tìm kiếm

Để tìm kiếm số lượng mẫu sản phẩm dựa trên mẫu sản phẩm và số ca thao tác, bạn hoàn toàn có thể dò tìm bằng hàm VLOOKUP 2 điều kiện kèm theo theo cách tạo cột phụ như những bước thực thi như sau :

– Bước 1: Bạn tạo một cột phụ trước bảng tính để thực hiện ghép các điều kiện lại với nhau và đặt tên cho cột vừa tạo là “Cột phụ”. Tại ô đầu tiền của Cột phụ bạn nhập công thức ghép điều kiện =[Sản phẩm]&[Ca]: =B4&C4 để ghép điều kiện Sản phẩm và Ca, sau đó sao chép công thức vào các ô còn lại của Cột phụ.

– Bước 2: Để có được kết quả số lượng sản phẩm có được. Tại ô G15, bạn nhập công thức hàm VLOOKUP: =VLOOKUP(I7&I8,$B$4:$E$9,4,0). Nếu bạn muốn tìm số lượng các sản phẩm khác và các ca khác thì chỉ cần nhập sản phẩm và ca đó vào bảng tìm kiếm mà không cần phải thực hiện tạo bảng khác.

Sử dụng công thức mảng

Công thức mảng là công thức mảng trong Excel hoàn toàn có thể thực thi nhiều phép tính và trả về hiệu quả trên một ô hoặc nhiều ô trong mảng .Công thức mảng sẽ tính tính bằng cách :+ Nhập công thức thống kê giám sát vào ô hoặc vùng ô cần nhập công thức .+ Công thức mảng sẽ được kết thúc bằng tổng hợp phím Ctrl + Shift + Enter+ Lưu ý khi sử dụng công thức mảng trong hàm tìm kiếm có điều kiện kèm theo trong excel : để bảo toàn vùng thống kê giám sát cũng như đồng nhất dữ liệu khi update, chỉnh sửa công thức .

Ví dụ sử dụng công thức hàm VLOOKUP với công thức mảng

Với cùng ví dụ như trên, để tìm kiếm số lượng mẫu sản phẩm theo từng ca, bạn nhập công thức mảng tại ô I9 như sau : = VLOOKUP ( I7 và I8, CHOOSE ( { 1,2 }, C4 : C9 và D4 : D9, E4 : E9 ), 2,0 ) và kết thúc bằng tổng hợp phím Ctrl + Shift + Enter .

Xem thêm: Giáo án dạy học Toán 11 theo định hướng phát triển phẩm chất năng lực – https://thomaygiat.com

Với những giá trị trog hàm VLOOKUP, bạn đã quen thuộc tuy nhiên tại sao khi sử dụng hàm VLOOKUP tìm kiếm 2 điều liện với công thức mảng thì lại có công thức hàm CHOOSE bên trong. Công thức hàm CHOOSE trong hàm VLOOKUP tìm kiếm 2 điều kiện kèm theo trong công thức mảng : = CHOOSE ( { 1,2 }, C4 : C9 và D4 : D9, E4 : E9 )Trong đó :

{1,2}: Hàm CHOOSE với giá trị trả về 1 mảng 2 chiều gồm 2 cột. Ở cột 1 sẽ lấy sau dấu phẩy đầu tiên, ở cột 2 sẽ lấy ở sau dấu phẩy thứ 2.
C4:C9&D4:D9: Các giá trị trong cột này được ghép từ cột Sản phẩm và cột Ca
E4:E9: Đây là giá trị của cột sản phẩm.

Lưu ý khi sử dụng hàm Vlookup 2 điều kiện bằng công thức mảng.
– Đối với công thức mảng, bạn cần kết thức công thức bằng cách nhấn tổ hợp phím Ctrl + Shift + Enter.
– Dấu ngoặc nhọn {} để đóng công thức mảng sẽ được Excel tự động chèn thêm vào sau khi kết thúc nhập công thức.

– Công thức mảng là công thức khó để vận dụng, ếu bạn không thành thạo việc sử dụng công thức mảng trong Excel thì hoàn toàn có thể sử dụng hàm VLOOKUP để tìm kiếm 2 điều kiện kèm theo để tránh sai sót trong quy trình thống kê giám sát. Tuy nhên, nếu bạn thành thạo sử dụng công thức mảng thì sẽ hỗ trở rất nhiều trong việc làm .

3. Sử dụng tích hợp hàm INDEX / MATCH

Ngoài việc sử dụng hàm tìm kiếm có điều kiện kèm theo trong excel là hàm VLOOKUP thì bạn hoàn toàn có thể sử dụng hàm INDEX / MATCH phối hợp để tìm kiếm có nhiều điều kiện kèm theo trong bảng tính Excel và trả về nhiều hiệu quả .

Hàm INDEX và hàm MATCH

Công thức hàm INDEX:

= INDEX ( vùng giá trị hiệu quả tìm kiếm, hàng lấy tác dụng tìm kiếm, cột lấy hiệu quả tìm kiếm )

Công thức hàm MATCH:

= MATCH ( vùng giá trị cần tìm kiếm, mảng để tìm kiếm, kiểu khớp )

Kết hợp hàm INDEX và hàm MATCH để tìm kiếm có điều kiện:
=INDEX(Vùng giá trị kết quả tìm kiếm, MATCH(vùng giá trị cần dò tìm, Cột tìm kiếm chứa giá trị cần tìm, Kết quả tìm kiếm trả về chính xác/tương đối gần đúng)

Ví dụ sử dụng hàm INDEX và hàm MATCH để tìm kiếm có điều kiện

Để tìm kiếm lớp của một số ít bạn học viên từ tên của bạn học viên đó, bạn hoàn toàn có thể sử dụng tích hợp hàm INDEX và hàm MATCH để dò tìm như sau :Tại ô F19, bạn nhập công thức : = INDEX ( $ A $ 20 : USD A $ 28, MATCH ( E19, USD B USD 20 : USD B $ 28,0 ) ). Kết quả sẽ trả về như hình ảnh bên dưới .

Nên sử dụng hàm tìm kiếm có điều kiện kèm theo trong Excel nào ?

Mỗi hàm tìm kiếm có điều kiện kèm theo trong Excel đều có ưu điểm và điểm yếu kém riêng, tùy vào trường hợp mà bạn hoàn toàn có thể linh động ứng dụng. Việc sử dụng hàm INDEX và hàm MATCH để tìm kiếm có điều kiện kèm theo sẽ thuận tiện hơn trong nhiều trường hợp. Ví dụ khi bạn sử dụng hàm VLOOKUP để tìm kiếm có điều kiện kèm theo mà cột chứa giá trị tác dụng dò tìm nằm bên trái thay vì nằm bên phải của vùng dữ liệu cần tìm thì bạn phải sử dụng tích hợp với hàm CHOOSE để tìm tác dụng tương ứng. Còn với việc tích hợp hàm INDEX và hàm MATCH, bạn hoàn toàn có thể thuận tiện ứng dụng công thức ở trên := INDEX ( Vùng giá trị cần tìm kiếm, MATCH ( vùng giá trị cần dò tìm, Cột tìm kiếm chứa giá trị cần tìm, Kết quả tìm kiếm trả về đúng chuẩn / tương đối gần đúng )

Lỗi thường gặp khi dùng hàm tìm kiếm có điều kiện kèm theo trong excel

Hàm VLOOKUP

Lỗi hiển thị # N / A

Trong hàm VLOOKUP, giá trị ở đầu cuối bạn cần lựa chọn phép toán trả về hiệu quả đúng chuẩn hay gần đúng. Tuy nhiên, giá trị này không bắt buộc bạn phải điền, hầu hết những trường hợp, bạn cần tìm kiếm một giá trị đơn cử như tên nhân viên cấp dưới hay giá tiền, bạn cần phép toán đúng mực. Do vậy, khi sử dụng hàm tìm kiếm có điều kiện kèm theo trong excel để tìm kiếm một giá trị đơn cử và duy nhất, thì FALSE là giá trị mà bạn cần nhập vào cuối công thức. Nhưng nếu bạn để trống giá trị này, công thức sẽ được mặc định giá trị TRUE và giá trị TRUE sẽ dựa trên dữ liệu được sắp xếp theo thứ tự tăng dần. Và lúc này tác dụng không đúng mực được trả về .

Cách khắc phục: Nếu bạn dò tìm giá trị cụ thể và duy nhất, tham số cuối cùng trong công thức bạn nên để giá trị FALSE.

Lỗi hiển thị # REF !

Lỗi hiển thị # REF thường gặp khi số cột được lấy hiệu quả trong bảng tìm kiếm có giá trị nhỏ hơn trong khoanh vùng phạm vi ô tìm kiếm. Nếu bãn đã kiểm tra và trong trường hợp cần trợ giúp thêm về lỗi hàm VLOOKUP này, bạn hoàn toàn có thể truy vấn vào trang VLOOKUP # REF !

Lỗi hiển thị # VALUE !

Lỗi hiển thị # REF thường gặp khi số cột được lấy hiệu quả trong bảng tìm kiếm có giá trị nhỏ hơn 1 hoặc không phải dạng số. hoặc đối sốđược cung ứng ở đầu cuối trong công thức hàm không bằng TRUE hoặc FALSE .Trên đây là 1 số ít hướng dẫn cách sử dụng hàm tìm kiếm có điều kiện kèm theo trong excel đơn thuần và nhanh gọn để tương hỗ cho việc làm của bạn. Hy vọng qua bài viết này, những bạn sẽ biết cách sử dụng và triển khai tìm kiếm dữ liệu trong excel với những hàm này .

Chúc các bạn thành công!

Xem thêm: Quỹ đầu tư phát triển là gì? Nguyên tắc và cách hạch toán