Cách dùng các hàm dò tìm thỏa mãn nhiều điều kiện trong Excel

Gitiho sẽ giới thiệu đến các bạn những hàm dò tìm thỏa mãn nhiều điều kiện giúp bạn trích xuất dữ liệu trong bảng tính Excel một cách nhanh chóng. Vậy cụ thể đó là những hàm nào, cách dùng ra sao thì hãy theo chân chúng mình tìm hiểu thông qua bài học Excel này nhé.

Hàm tìm kiếm có điều kiện trong Excel là gì?

Hàm tìm có điều kiện được sử dụng để trả về kết quả chúng ta mong muốn thì phải đính kèm thêm ít nhất một điều kiện trong hàm. 

Điều kiện ở đây có thể được tham chiếu từ một bảng khác, hoặc điều kiện do bạn tự đặt ra. Dù là điều kiện nào thì cũng phải thỏa mãn để điền được vào trong hàm.

Các hàm dò tìm thỏa mãn nhiều điều kiện trong Excel

Hàm tìm kiếm nhiều điều kiện thông thường sẽ bao gồm:

• Hàm VLOOKUP: giúp bạn tìm kiếm dữ liệu dựa vào điều kiện được tham chiếu từ một bảng dữ liệu khác. Thông thường, người ta thường kết hợp sử dụng hàm VLOOKUP với nhiều hàm khác nhau nhằm giúp việc tìm kiếm dữ liệu trả về kết quả như mong muốn.
• Hàm Index và hàm Match: 2 hàm này thường đi kèm với nhau, và cũng dùng với mục đích tìm kiếm dữ liệu có điều kiện, tuy nhiên cách sử dụng lại đơn giản hơn nhiều so với hàm VLOOKUP.

Xem thêm: 23 điều bạn nhất định phải biết khi sử dụng hàm VLOOKUP (Phần 1)

Cách sử dụng các hàm lấy dữ liệu có điều kiện trong Excel

Cách sử dụng hàm VLOOKUP một điều kiện

Hàm VLOOKUP có công thức như sau:

=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 đó: 

Tham số tùy chọn cho biết liệu bạn muốn tìm kiếm gần đúng (TRUE hoặc 1) hay chính xác (FALSE hoặc 0). Nếu để trống, Excel sẽ mặc định là tìm kiếm gần đúng và hàm sẽ trả về kết quả tìm kiếm chính xác trong khoảng gần nhất.

Ví dụ:

Để 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.

Tại ô D4, bạn điền công thức dò tìm dữ liệu có điều kiện như sau: 

=VLOOKUP(C4,$H$2:$I$9,2,0)

Trong đó:

• 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 giúp bạn copy công thức sang các ô khác mà không bị thay đổi tham chiế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 (Cột I). 
• Tham số cuối = 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 XLOOKUP THAY THẾ HÀM VLOOKUP và HLOOKUP

Hàm dò tìm thỏa mãn nhiều điều kiện

Khi sử dụng hàm VLOOKUP nhiều điều kiện, bạn có thể tự do tham chiếu thêm điều kiện khác, giúp kết quả của bạn được chọn lọc và sát với yêu cầu hơn rất nhiều so với hàm VLOOKUP 1 điều kiện. Có hai cách làm như sau:

Tạo cột phụ để tìm kiếm nhiều điều kiện trong Excel

Mục đích tạo cột phụ giúp bạn gộp hai điều kiện lại với nhau, từ đó dễ dàng hơn trong quá trình nhập hàm. Chúng mình có một ví dụ như sau:

Để 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. Nhập công thức ghép điều kiện vào cột phụ:

=B4&C4

Bước 2: 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ỉ thay đổi giá trị dò tìm ở ô I7 và I8 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 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 ô. Thông thường, cách làm này sẽ sử dụng kèm với hàm CHOOSE. 

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 ô L9 như sau: 

=VLOOKUP(L7&L8,CHOOSE({1,2},C4:C9&D4:D9,E4:E9),2,0)

Sau khi điền công thức sau, nhấn tổ hợp phím Ctrl + Shift + Enter để hàm dò tìm thỏa mãn nhiều điều kiện hoạt động.

Cụ thể:

• {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.

Xem thêm: 3 Ký tự đại diện trong Excel nhất định bạn cần biết để tìm kiếm nhanh hơn.

Cách sử dụng hàm Index/ Match

Để hàm INDEX và MATCH là hàm dò tìm thỏa mãn nhiều điều kiện trong Excel, ta phải kết hợp hàm INDEX và MATCH này lại với nhau, với công thức chung như sau:

=INDEX(range_to_return_value_from, MATCH(lookup_value, lookup_range, [match_type]))

Trong đó: 

• range_to_return_value_from: Phạm vi dữ liệu mà bạn muốn trả về giá trị.
• lookup_value: Giá trị bạn muốn tìm kiếm.
• lookup_range: Phạm vi dữ liệu mà bạn muốn tìm kiếm.
• match_type: Tham số tùy chọn cho biết liệu bạn muốn tìm kiếm chính xác hoặc gần đúng.

Tìm kiếm chính xác: sử dụng tham số 0. 

Tìm kiếm gần đúng: sử dụng 1 (hoặc TRUE) cho tìm kiếm lớn hơn hoặc -1 (hoặc FALSE) cho tìm kiếm nhỏ hơn.

Ví dụ:

Để 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 đó, tại ô F19, bạn nhập công thức: 

=INDEX($A$20:$A$28,MATCH(E19,$B$20:$B$28,0))

Trong đó: 

• Hàm MATCH sẽ tìm kiếm giá trị trong ô E19 trong phạm vi cột B từ B20 đến B28. Nếu nó tìm thấy một giá trị khớp chính xác, nó sẽ trả về vị trí của giá trị đó trong dãy từ 1 đến 9.
• Sau khi MATCH tìm thấy vị trí của giá trị trong cột B, hàm INDEX sử dụng vị trí đó để truy xuất giá trị tương ứng trong cột A từ A20 đến A28.

Kết quả sẽ trả về như hình ảnh bên dưới:

Xem thêm: BÀI TẬP EXCEL VỚI CÁCH SỬ DỤNG HÀM VLOOKUP VÀ ỨNG DỤNG NÂNG CAO THƯỜNG GẶP (PHẦN 1)

KẾT THÚC

Hy vọng bạn đã biết thêm về những hàm dò tìm thỏa mãn nhiều điều kiện trong Excel cũng như cách sử dụng chúng. Nếu bạn thấy bài viết của chúng mình hay và bổ ích thì đừng quên ghé thăm blog tin học văn phòng của chúng mình thường xuyên nhé.