Hàm tìm kiếm với nhiều điều kiện trong Excel 2003 (= Vlookups và Hlookups 2007)

[adam_tran]

Chẳng biết có ai post chưa, google không thấy, mình thấy cái này hay hay, post lên chia xẻ cùng mọi người.

Các bạn thấy rằng trong Excel 2003 trở về trước, hàm VLOOKUP chỉ tìm với 1 điều kiện. Kết hợp các hàm INDEX, MATCH, IF với hàm mảng sẽ cho kết quả tìm thỏa mãn nhiều điều kiện.

Cú pháp: {=INDEX(LookUpRange, MATCH(1, ((Array1=DK1)*(Array2=DK2)*(....)), 0), Column)}
Thí dụ: Có 1 bảng có các cột: Mã hàng, khách hàng, Bộ phận, Số lượng ... Tìm kiếm Mã hàng A do Khách Hàng B mua, thuộc bộ phận C (SL >0), cột mã hàng là cột đầu

Cú pháp: {=INDEX(Data, MATCH(1, ((KH="A")*(Bophan="B")*(SL>0)), 0), 1)}

Lưu ý: Nếu nhiều dòng thỏa điều kiện thì hàm chỉ lấy dòng đầu tiên giống VLOOKUP

 

[dat_butmuc]

INDEX(), MATCH(), IF ...kết hợp thì có nhiều chứ riêng

(= Vlookups và Hlookups 2007)

thì chưa thấy & cũng chưa hiểu cấu trúc nó ra làm sao -> mặc dùng đang dùng Excel~2007 ?

Bạn có file chi tiết và công thức của nó không ? (đừng dùng hàm tự tạo...)

 

[toanthang1975]

Cũng với VD trên, các bạn giúp mình tìm kiếm điều kiện ở cột E (E2): nếu có rất nhiều dòng thì khi tìm thấy A cột A2 thì đáp sỗ sẽ là sẽ bằng tổng số lượng ở cột D. VD: cột A có 3 chữ A thì sẽ bằng tổng của 3 giá trị D2,D4,D11 (=10+35+89=134).
Thank all

 

[nghiaphuc]

Cũng với VD trên, các bạn giúp mình tìm kiếm điều kiện ở cột E (E2): nếu có rất nhiều dòng thì khi tìm thấy A cột A2 thì đáp sỗ sẽ là sẽ bằng tổng số lượng ở cột D. VD: cột A có 3 chữ A thì sẽ bằng tổng của 3 giá trị D2,D4,D11 (=10+35+89=134).
Thank all

Có phải ý bạn là tính tổng (cột D) của các mã hàng loại A?
Nếu đúng thì chỉ đơn giản là SUMIF thôi: =SUMIF(A2:A16,"A",D216)
Còn nếu thêm nhiều điều kiện thì có thể dùng SUMPRODUCT hoặc SUMIFS (nếu sử dụng Excel 2007, 2010)

 

[phamdung244]

Chẳng biết có ai post chưa, google không thấy, mình thấy cái này hay hay, post lên chia xẻ cùng mọi người.

Các bạn thấy rằng trong Excel 2003 trở về trước, hàm VLOOKUP chỉ tìm với 1 điều kiện. Kết hợp các hàm INDEX, MATCH, IF với hàm mảng sẽ cho kết quả tìm thỏa mãn nhiều điều kiện.

Cú pháp: {=INDEX(LookUpRange, MATCH(1, ((Array1=DK1)*(Array2=DK2)*(....)), 0), Column)}
Thí dụ: Có 1 bảng có các cột: Mã hàng, khách hàng, Bộ phận, Số lượng ... Tìm kiếm Mã hàng A do Khách Hàng B mua, thuộc bộ phận C (SL >0), cột mã hàng là cột đầu

Cú pháp: {=INDEX(Data, MATCH(1, ((KH="A")*(Bophan="B")*(SL>0)), 0), 1)}

Lưu ý: Nếu nhiều dòng thỏa điều kiện thì hàm chỉ lấy dòng đầu tiên giống VLOOKUP

Mình không hiểu sao lại có thêm dấu { trước dấu = Index(.....}. Với lại khi mình copy công thức ở ô A20 sang ô A21 lại không ra được kết quả. Bạn hướng dẫn chi tiết lại giùm mình hen. Cảm ơn bạn nhiều

 

[phanoanh2583]

Mình không hiểu sao lại có thêm dấu { trước dấu = Index(.....}. Với lại khi mình copy công thức ở ô A20 sang ô A21 lại không ra được kết quả. Bạn hướng dẫn chi tiết lại giùm mình hen. Cảm ơn bạn nhiều

dấu đấy hình như là công thức mảng, muốn câu lệnh thực hiện phải nhấn tổ hợp 3 phím ctr , shift, enter

 

[theblueserenade]

Mình muốn hỏi là có cách nào để tìm kiếm nếu nhiều dòng đều thỏa mãn điều kiện và mình muốn lấy tất cả những dòng đó không. Trong ví dụ nếu khách hàng A ở bộ phận B mua mã hàng D nên số lượng >0, mình muốn lấy cả mã hàng D và XXX thỏa mãn điều kiện cho trước.

 

[kyu_86]

Chẳng biết có ai post chưa, google không thấy, mình thấy cái này hay hay, post lên chia xẻ cùng mọi người.

Các bạn thấy rằng trong Excel 2003 trở về trước, hàm VLOOKUP chỉ tìm với 1 điều kiện. Kết hợp các hàm INDEX, MATCH, IF với hàm mảng sẽ cho kết quả tìm thỏa mãn nhiều điều kiện.

Cú pháp: {=INDEX(LookUpRange, MATCH(1, ((Array1=DK1)*(Array2=DK2)*(....)), 0), Column)}
Thí dụ: Có 1 bảng có các cột: Mã hàng, khách hàng, Bộ phận, Số lượng ... Tìm kiếm Mã hàng A do Khách Hàng B mua, thuộc bộ phận C (SL >0), cột mã hàng là cột đầu

Cú pháp: {=INDEX(Data, MATCH(1, ((KH="A")*(Bophan="B")*(SL>0)), 0), 1)}

Lưu ý: Nếu nhiều dòng thỏa điều kiện thì hàm chỉ lấy dòng đầu tiên giống VLOOKUP

Minh ko hieu tai sao trong ham match MATCH(1, ((KH="A")*(Bophan="B")*(SL>0)), 0) , trị do lai la 1 nhi? Moi nguoi ai biet giai thich giup minh voi.

nghiaphuc: Lần sau bạn nhớ gõ tiếng Việt có dấu nhé.

 

[zztantaibazz]

Minh ko hieu tai sao trong ham match MATCH(1, ((KH="A")*(Bophan="B")*(SL>0)), 0) , trị do lai la 1 nhi? Moi nguoi ai biet giai thich giup minh voi.

((KH="A")*(Bophan="B")*(SL>0))
Trong phần này nó sẽ tạo ra 1 mãng gồm {True, false, true, ...}*{True, false, true, ...}*{True, false, true, ...} = {1,0,1,1,1,0....}
Do đó phải dùng số 1 cho LOOKUP_VALUE trong hàm MATCH này

 

[kyu_86]

Xin các cao thủ giúp đỡ e. E loay hoay không biết dùng hàm tìm kiếm thế nào. E gửi lên đây file đính kèm, mong các cao thủ chỉ giáo.

 

[bebo021999]

Tại M2:

=CHAR(LARGE(CODE($B$3:$B$17)*$C$3:$I$17*(Model=$L2),COLUMN(A:A)))

Kết thúc bằng Ctrl-shift-enter.
Copy sang phải và xuống dưới.

 

[kyu_86]

Thank bác, e đã thử vào tình huống cụ thể nhưng kết quả không được như ý. Tình huống thực tế là mã dài hơn. Bác xem file.

Tại M2:

=CHAR(LARGE(CODE($B$3:$B$17)*$C$3:$I$17*(Model=$L2),COLUMN(A:A)))

Kết thúc bằng Ctrl-shift-enter.
Copy sang phải và xuống dưới.

 

[ndu96081631]

Thank bác, e đã thử vào tình huống cụ thể nhưng kết quả không được như ý. Tình huống thực tế là mã dài hơn. Bác xem file.

Công thức tại M2:

=INDEX(Part,MATCH(1,OFFSET(Part,,MATCH($L2,Model,0))))

Công thức tại N2:

=INDEX(Part,MATCH(1,OFFSET(Part,,MATCH($L2,Model,0)),[COLOR=#ff0000]0[/COLOR]))

2 công thức này chỉ khác nhau duy nhất ở cái số 0 màu đỏ thôi

 

[kyu_86]

Bác đúng là cao thủ. E cảm ơn cao thủ đã giúp đỡ

Công thức tại M2:

=INDEX(Part,MATCH(1,OFFSET(Part,,MATCH($L2,Model,0))))

Công thức tại N2:

=INDEX(Part,MATCH(1,OFFSET(Part,,MATCH($L2,Model,0)),[COLOR=#ff0000]0[/COLOR]))

2 công thức này chỉ khác nhau duy nhất ở cái số 0 màu đỏ thôi

 

[ntvronin]

Mình muốn hỏi là có cách nào để tìm kiếm nếu nhiều dòng đều thỏa mãn điều kiện và mình muốn lấy tất cả những dòng đó không. Trong ví dụ nếu khách hàng A ở bộ phận B mua mã hàng D nên số lượng >0, mình muốn lấy cả mã hàng D và XXX thỏa mãn điều kiện cho trước.

Cùng băn khoăn với bạn này? Cao thủ nào biết thì chỉ giúp.

 

[COTOCOTO]

=+if(type(match($d$3,'hàng 3 tháng'!$g$3:$g$845,0))=16,"",match($d$3,'hàng 3 tháng'!$g$3:$g$845,0)). Mình có hàm như này.nhưng muốn tìm các giá trị có chung $d$3 thì sửa hàm kiểu gì ạ mời các thánh giúp giùm em cái . đa tạ . đa tạ.

 

[Ba Tê]

=+if(type(match($d$3,'hàng 3 tháng'!$g$3:$g$845,0))=16,"",match($d$3,'hàng 3 tháng'!$g$3:$g$845,0)). Mình có hàm như này.nhưng muốn tìm các giá trị có chung $d$3 thì sửa hàm kiểu gì ạ mời các thánh giúp giùm em cái . đa tạ . đa tạ.

Thấy file mới nói "chiện" được, xem bạn xài office nào.
Kết quả mẫu là sao? (các giá trị có chung... nằm ở đâu).
Mời các "thánh" chắc hơi bị lâu. Ở đây toàn người bình thường, chưa ai lên cấp "thánh" cả.

 

[nttcntn]

Các Bác có thể giải thích hoạt động của hàm Match với lựa chon Match_type = 1 được không.
Mình thử hoài mà ko hiểu quy luật ra làm sao.

 

[Ba Tê]

Các Bác có thể giải thích hoạt động của hàm Match với lựa chon Match_type = 1 được không.
Mình thử hoài mà ko hiểu quy luật ra làm sao.

http://www.giaiphapexcel.com/forum/...ác-Hàm-dò-tìm-và-tham-chiếu&p=48585#post48585
Chú ý việc dữ liệu sắp xếp theo cách nào. (A-Z; Z-A; Lộn xộn)

 

[nttcntn]

http://www.giaiphapexcel.com/forum/...ác-Hàm-dò-tìm-và-tham-chiếu&p=48585#post48585
Chú ý việc dữ liệu sắp xếp theo cách nào. (A-Z; Z-A; Lộn xộn)

Mình đã đọc bài này rùi nhưng không sao lý giải đc kết quả trong file đã gửi #18