Hàm Index-Match lồng Offset (4 người xem)

[tienmanbd]

Nhờ anh chị giúp dùm hàm Index-Match lồng Offset. khi em đưa vào data Validation list mục Screw có 2 mã hàng, nhưng chỉ trả về 1 giá trị thay vì nhiều giá trị

=INDEX(OFFSET(Data!$B$1,MATCH($C7,Data!$A:$A,0),0,COUNTIF(Data!$A:$A,$C7),1),MATCH([@[Part Name]],OFFSET(Data!$C$1,MATCH($C7,Data!$A:$A,0),0,COUNTIF(Data!$A:$A,$C$6),1),0))

Em cảm ơn nhiều!

 

[Phan Thế Hiệp]

Nhờ anh chị giúp dùm hàm Index-Match lồng Offset. khi em đưa vào data Validation list mục Screw có 2 mã hàng, nhưng chỉ trả về 1 giá trị thay vì nhiều giá trị

=INDEX(OFFSET(Data!$B$1,MATCH($C7,Data!$A:$A,0),0,COUNTIF(Data!$A:$A,$C7),1),MATCH([@[Part Name]],OFFSET(Data!$C$1,MATCH($C7,Data!$A:$A,0),0,COUNTIF(Data!$A:$A,$C$6),1),0))

Em cảm ơn nhiều!

Bạn đang muốn tạo 1 danh sách động, với điều kiện so dữ liệu theo dòng, để cung cấp cho Data Validation (DV). Nhưng DV chỉ chấp nhận "List" được tạo từ dữ liệu có "địa chỉ" hiện hữu tồn tại trên 1 sheet cụ thể, hay nói gọn là chỉ chấp nhận Range (Vùng) của 1 sheet, không chấp nhận 1 Array (Mảng nằm trong bộ nhớ máy tính) bất kỳ, mà mảng này được tạo từ kết quả của sự tính toán, trích lọc qua công thức.

Muốn vậy:

1. Bạn phải dùng 1 đoạn code VBA (nhấn Alt+F11, chọn Sheet1(Date) để xem), nhằm cập nhật số dòng khi bạn di chuyển con trỏ lên xuống.

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.Calculate
   End Sub

   Nhớ "Save As" với đuôi ".xlsb" hoặc ".xlsm"
2. Tạo danh sách động theo số dòng con trỏ đang đứng, Sheet "Date":

   T6=IFERROR(T(OFFSET(Data!$B$1,AGGREGATE(15,6,ROW($1:$2000)/(INDIRECT("Date!C"&CELL("row"))=Data!$A$2:$A$2000)/(INDIRECT("Date!D"&CELL("row"))=Data!$C$2:$C$2000),ROW($A1)),)),"")

   Enter, fill xuống.
3. Nhấn Ctrl+F3 tạo Name "ListCode"

   =OFFSET(Date!$T$6,,,COUNT(1/(Date!$T$6:$T$100<>"")))

4. Chọn Sheet Date!F6: F21, vào Data Validation: Chọn List = ListCode
5. Đứng tại F7, kiểm tra "droplist" đã đúng như yêu cầu của bạn chưa!

Thân

 

[tienmanbd]

Anh vui lòng cho em hỏi, khi em chọn từ dòng E6 đến dòng cuối cùng và dán công thức vào mục Source của Data validation list thì báo lỗi. Nhờ anh hướng dẫn thêm giúp em. Nhân tiện anh cho em hỏi thêm, ở mục số 3 nêu trên em có phải sửa từ T6 thành E6 ko?

 

[Phan Thế Hiệp]

Anh vui lòng cho em hỏi, khi em chọn từ dòng E6 đến dòng cuối cùng và dán công thức vào mục Source của Data validation list thì báo lỗi. Nhờ anh hướng dẫn thêm giúp em. Nhân tiện anh cho em hỏi thêm, ở mục số 3 nêu trên em có phải sửa từ T6 thành E6 ko?

Bạn không được thay thế T6 bằng E6, vì cột T dành trích lọc ra danh sách mã Code phù hợp với dòng con trỏ đang đứng.
Còn cột E chỉ để nhận những gì mà cột T cung cấp.

Thân

 

[tienmanbd]

Anh ơi em thấy có bạn viết công thức này và áp data validation vẫn ra, mà ko dùng đến Code

=OFFSET(INDIRECT("a!$C$"&(MATCH($C3,A!$A$2:$A$9999,0))),MATCH(D3,INDIRECT("a!$B$"&MATCH(C3,A!$A$2:$A$9950,0)&":"&"$B$999"),0)-1,0,COUNTIF(INDIRECT("a!$B$"&MATCH(C3,A!$A$2:$A$9999,0)&":"&"$B$"&(MATCH(C3,A!$A$2:$A$9999,0)+COUNTIF(A!$A$2:$A$9999,C3))),D3),1)

 

[Phan Thế Hiệp]

Anh ơi em thấy có bạn viết công thức này và áp data validation vẫn ra, mà ko dùng đến Code

=OFFSET(INDIRECT("a!$C$"&(MATCH($C3,A!$A$2:$A$9999,0))),MATCH(D3,INDIRECT("a!$B$"&MATCH(C3,A!$A$2:$A$9950,0)&":"&"$B$999"),0)-1,0,COUNTIF(INDIRECT("a!$B$"&MATCH(C3,A!$A$2:$A$9999,0)&":"&"$B$"&(MATCH(C3,A!$A$2:$A$9999,0)+COUNTIF(A!$A$2:$A$9999,C3))),D3),1)

Đúng như bạn thắc mắc, công thức của bạn đưa chỉ sử dụng được với điều kiện: sheet "A" phải được sort trước theo 2 cột A và B (Model và Description), tức phải thỏa các thông tin mã Model phải liền nhau. Sau khi sort, thì khi tạo danh sách cho Data Validation bằng việc so khớp theo dòng của "Sheet1", nó tìm được Range (Vùng, có địa chỉ hiện hữu trên sheet "A", ví dụ: A!C16: C17, ứng với Model: 10-2015 và "Screw") khớp với điều kiện và mang vào Data Validation, nên nó không cần đoạn Code VBA để cập nhật. Khi dữ liệu sheet "A" không được sort trước, thì kết quả sẽ ra không chính xác. (Xem file kèm)

Công thức tôi cung cấp cho bạn thì bất kể dữ liệu có được sort hay không, ví dụ các dòng thỏa nằm ở dòng 15, 67, 121, thì nó chỉ trích đúng các Code tại các dòng đó vào danh sách cột phụ T.

Ngoài ra, nếu bạn có thể đảm bảo dữ liệu sheet "A" luôn được sort, thì công thức xác định Vùng để tạo List có thể ngắn gọn hơn

Thân