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

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

Thông thường, để tìm kiếm dữ liệu thỏa mãn điều kiện trong một phạm vi hay một bảng tính Excel thì 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 trong excel với chức nă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

Cô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 lãnh trong đợt dịch Covid 19 do Công ty quyết định phụ cấp tùy theo từng chức vụ. Dưới đây là danh sách nhân viên và chức vụ, Sếp yêu cầu bạn tính mức phụ cấp cho từng nhân viên với chúc vụ tương ứng để được hưởng phụ cấp theo quy định của Công ty.

Đối với các bạn kế toán không biết sử dụng hàm tìm kiếm có điều kiện trong excel thì các bạn sẽ dò tìm từng chức vụ của từng nhân viên, 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 định mức phụ cấp. Tuy nhiên cách sử dụng này khá là mất thời gian, nếu bảng tính chỉ có một vài nhân viên thì bạn có thể thực hiện nhanh, nhưng với Công ty có hàng nghìn người thì thao tác thủ công này không thể thực hiện, đôi khi kết quả dò tìm của bạn có thể có sai sót.

Với hàm VLOOKUP, bạn có thể thực hiện cách đơn giản vừa nhanh vừa chính xác như sau:

Tại ô D4, bạn điền công thức: =VLOOKUP(C4,$H$2:$I$9,2,0).

Dấu $ trong công thức được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác mà không bị thay đổi 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ó kết quả trả về = 0 tức là để dò tìm kết quả chính xác.

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

Đối với công thức hàm VLOOKUP thông thường ở trên, bạn có thể thấy được hàm chỉ dò tìm được 1 vùng dữ liệu tham chiếu, 1 gía trị hoặc 1 chuỗi văn bản. Với các điều kiện 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 sau đây sẽ giúp bạn dễ dàng thực hiện. Nếu bạn sử  dụng hàm tìm kiếm có điều kiện trong excel với hàm VLOOKUP 2 điều kiện,  thì bạn có thể dễ dàng 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 thực hiện đều có ưu và nhược điểm riêng biệt, cũng tùy vào trường hợp mà bạn có thể cân nhắc lựa chọn sử dụng cách nào.

Cách thực hiện đơn giản khi sử dụng hàm VLOOKUP tìm kiesm 2 điều kiện là tạo cột phụ. Từ các điều kiện 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. Tùy vào các yêu cầu cụ thể mà bạn sẽ ghép các cột điều kiện lại theo cách khác nhau, thông thường hàm được sử dụng để ghép các điều kện lại với nhau bằng kí hiệu “&”.

Ví dụ: Mã nhân viên được ghép từ 2 cột như hình bên dưới. Bạn sẽ ghép điều kiện bằng cách tạo công thức với kí hiệu “&” như sau: =B4&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 sản phẩm dựa trên sản phẩm và số ca làm việc, bạn có thể dò tìm bằng hàm VLOOKUP 2 điều kiện theo cách tạo cột phụ như các bước thực hiện 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.

Công thức mảng là công thức mảng trong Excel có thể thực hiện nhiều phép tính và trả về kết 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 tính toán 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ổ 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 trong excel : để bảo toàn vùng tính toán cũng như đồng bộ dữ liệu khi cập nhật, 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 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&I8,CHOOSE({1,2},C4:C9&D4:D9,E4:E9),2,0) và kết thúc bằng tổ hợp phím Ctrl + Shift + Enter.

Với các 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 trong công thức mảng: =CHOOSE({1,2},C4:C9&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ó để áp 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ì có thể sử dụng hàm VLOOKUP để tìm kiếm 2 điều kiện để tránh sai sót trong quá trình tính toán. 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 công việc.

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

Hàm INDEX và hàm MATCH

Công thức hàm INDEX:

=INDEX(vùng giá trị kết quả tìm kiếm, hàng lấy kết quả tìm kiếm, cột lấy kết 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ố bạn học sinh từ tên của bạn học sinh đó, bạn có thể sử dụng kết 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:$A$28,MATCH(E19,$B$20:$B$28,0)).Kết quả sẽ trả về như hình ảnh bên dưới.

Mỗi hàm tìm kiếm có điều kiện trong Excel đều có ưu điểm và nhược điểm riêng, tùy vào trường hợp mà bạn có thể linh hoạt ứng dụng. Việc sử dụng hàm INDEX và hàm MATCH để tìm kiếm có điều kiện sẽ dễ dàng 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 mà cột chứa giá trị kết quả 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 kết hợp với hàm CHOOSE để tìm kết quả tương ứng. Còn với việc kết hợp hàm INDEX và hàm MATCH, bạn có thể dễ dàng ứ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ề chính xác/tương đối gần đúng)

Trong hàm VLOOKUP, giá trị cuối cùng bạn cần chọn lựa phép toán trả về kết quả chính xác 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 các trường hợp, bạn cần tìm kiếm một giá trị cụ thể như tên nhân viên hay giá tiền, bạn cần phép toán chính xác. Do vậy, khi sử dụng hàm tìm kiếm có điều kiện trong excel để tìm kiếm một giá trị cụ thể 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 kết quả không chính xá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 thường gặp khi số cột được lấy kết quả trong bảng tìm kiếm có giá trị nhỏ hơn trong 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 có thể truy cập vào trang VLOOKUP #REF!

Lỗi hiển thị #REF thường gặp khi số cột được lấy kết 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 cấp cuối cùng trong công thức hàm không bằng TRUE hoặc FALSE.

Trên đây là một số hướng dẫn cách sử dụng hàm tìm kiếm có điều kiện trong excel đơn giản và nhanh chóng để hỗ trợ cho công việc của bạn. Hy vọng qua bài viết này, các bạn sẽ biết cách sử dụng và thực hiện tìm kiếm dữ liệu trong excel với các hàm này.

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

4.6/5 – (5 votes)