VLOOKUP nhiều cột (1 người xem)

[uronmapu]

Chào các bác,

Em có 1 file như đính kèm với các giá trị trong bảng từ cột A đến Cột C.
Cột A là tên, B C D là các cột ngôn ngữ mà người trong cột A biết.
Các bác có lệnh nào để em có thể tập hợp lại theo danh sách như cột H và I không ạ.
Cột H sẽ là danh sách các ngôn ngữ, cột I là tên người biết sử dụng ngôn ngữ đó.

Em cảm ơn nhiều.

 

[bebo021999]

Nếu bạn dùng Excel 365 thì dùng công thức trong vòng 1 nốt nhạc
Nếu Excel cũ hơn thì dùng VBA
(Mình không có 365 nên chỉ có thể giúp VBA)
Bạn chọn cái nào?

 

[Phan Thế Hiệp]

Chào các bác,

Em có 1 file như đính kèm với các giá trị trong bảng từ cột A đến Cột C.
Cột A là tên, B C D là các cột ngôn ngữ mà người trong cột A biết.
Các bác có lệnh nào để em có thể tập hợp lại theo danh sách như cột H và I không ạ.
Cột H sẽ là danh sách các ngôn ngữ, cột I là tên người biết sử dụng ngôn ngữ đó.

Em cảm ơn nhiều.

Nếu Excel cũ hơn dùng Vlookup(), kết hợp các công thức như Countif(), Index(...,Match()) cũng được.

Thực hiện công thức như sau:

1/ Ô H2=

=IFERROR(VLOOKUP(I2,$A$2:$D$7,COUNTIF(I$2:I2,I2)+1,),"")

Enter, fill xuống.

2/ Ô I2=

=T(IFERROR(IF(COUNTIF(I$1:I1,I1)<VLOOKUP(I1,CHOOSE({1,2},$A$2:$A$7,MMULT(--($B$2:$D$7<>""),{1;1;1})),2,),I1,INDEX($A$2:$A$7,MATCH(I1,$A$2:$A$7,)+1)),A2))

Kết thúc bằng Ctrl+Shift+Enter, fill xuống.

Thân

 

[longtay1111]

Nếu Excel cũ hơn dùng Vlookup(), kết hợp các công thức như Countif(), Index(...,Match()) cũng được.

Thực hiện công thức như sau:

1/ Ô H2=

=IFERROR(VLOOKUP(I2,$A$2:$D$7,COUNTIF(I$2:I2,I2)+1,),"")

Enter, fill xuống.

2/ Ô I2=

=T(IFERROR(IF(COUNTIF(I$1:I1,I1)<VLOOKUP(I1,CHOOSE({1,2},$A$2:$A$7,MMULT(--($B$2:$D$7<>""),{1;1;1})),2,),I1,INDEX($A$2:$A$7,MATCH(I1,$A$2:$A$7,)+1)),A2))

Kết thúc bằng Ctrl+Shift+Enter, fill xuống.

Thân

Công thức của bác Hiệp thật quá tuyệt vời, mỗi bài trả lời của bác là em học hỏi được rất nhiều kiến thức mới, em thấy bác Hiệp dùng Vlookup đoạn này vô cùng sáng tạo.

VLOOKUP(I2,$A$2:$D$7,COUNTIF(I$2:I2,I2)+1,)

Em chân thành cảm ơn bác rất nhiều, em chúc bác sức khỏe ạ.

 

[Hoàng Tuấn 868]

mỗi bài trả lời của bác là em học hỏi được rất nhiều kiến thức mới,

Mình thấy anh Hiệp cũng hay dùng lại các công thức y như cũ mà.

 

[DeTong]

có thể tập hợp lại theo danh sách như cột H và I không

M code: Unpivote Other Columns

 

[Phuocam]

Cột I là tên người biết sử dụng ngôn ngữ đó.
[/QUOTE]
Cột I có thể dùng:

=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($B$2:$D$6)/($B$2:$D$6<>""),ROW(A1))-1),"")

 

[Duy Tùng 9x]

Chào các bác,

Em có 1 file như đính kèm với các giá trị trong bảng từ cột A đến Cột C.
Cột A là tên, B C D là các cột ngôn ngữ mà người trong cột A biết.
Các bác có lệnh nào để em có thể tập hợp lại theo danh sách như cột H và I không ạ.
Cột H sẽ là danh sách các ngôn ngữ, cột I là tên người biết sử dụng ngôn ngữ đó.

Em cảm ơn nhiều.

Nếu bạn dùng phiên bản 365, thì mình xin góp ý thêm một cách này. Hàm kiểu unpivot
=LET(a, B26, b, A2:A6,i, LAMBDA(x,TOCOL(IFS(a<>"",x),2)), HSTACK(i(a),i(b)))

 

[uronmapu]

Nếu bạn dùng Excel 365 thì dùng công thức trong vòng 1 nốt nhạc
Nếu Excel cũ hơn thì dùng VBA
(Mình không có 365 nên chỉ có thể giúp VBA)
Bạn chọn cái nào?

Cảm ơn bác, e dùng excel và em muốn dùng công thức thôi ạ.

Bài đã được tự động gộp: 28/9/24

Nếu Excel cũ hơn dùng Vlookup(), kết hợp các công thức như Countif(), Index(...,Match()) cũng được.

Thực hiện công thức như sau:

1/ Ô H2=

=IFERROR(VLOOKUP(I2,$A$2:$D$7,COUNTIF(I$2:I2,I2)+1,),"")

Enter, fill xuống.

2/ Ô I2=

=T(IFERROR(IF(COUNTIF(I$1:I1,I1)<VLOOKUP(I1,CHOOSE({1,2},$A$2:$A$7,MMULT(--($B$2:$D$7<>""),{1;1;1})),2,),I1,INDEX($A$2:$A$7,MATCH(I1,$A$2:$A$7,)+1)),A2))

Kết thúc bằng Ctrl+Shift+Enter, fill xuống.

Thân

Em cảm ơn bác nhiều, công thức rất hay và đúng kết quả ạ.

Bài đã được tự động gộp: 28/9/24

View attachment 304323
Nếu bạn dùng phiên bản 365, thì mình xin góp ý thêm một cách này. Hàm kiểu unpivot
=LET(a, B26, b, A2:A6,i, LAMBDA(x,TOCOL(IFS(a<>"",x),2)), HSTACK(i(a),i(b)))

Đa tạ bác, công thức này ổn cho 365 ạ. Mình có thể làm thêm cách lọc từ trên xuống của cột ngôn ngữ theo thứ tự bảng chữ cái A B C... đc ko bác

 

[uronmapu]

Em xin hỏi thêm các bác trường hợp sau nữa ạ.
Cũng là file trên như đã đính kèm với các giá trị trong bảng từ cột A đến Cột C của Sheet 1
Cột A là tên, B C D là các cột ngôn ngữ mà người trong cột A biết.
Em có thêm Sheet 2 có cột A cố định là danh sách các ngôn ngữ được sắp xếp tên theo danh sách A, B, C, ... của bảng chữ cái
Kết quả trả về cột C, nếu không có ai biết (ngôn ngữ không có trong danh sách ở Sheet 1, trả về giá trị No One)

@Phan Thế Hiệp
@Duy Tùng 9x

Xin các bác chỉ em thêm ạ, em cảm ơn!

 

[longtay1111]

Em xin hỏi thêm các bác trường hợp sau nữa ạ.
Cũng là file trên như đã đính kèm với các giá trị trong bảng từ cột A đến Cột C của Sheet 1
Cột A là tên, B C D là các cột ngôn ngữ mà người trong cột A biết.
Em có thêm Sheet 2 có cột A cố định là danh sách các ngôn ngữ được sắp xếp tên theo danh sách A, B, C, ... của bảng chữ cái
Kết quả trả về cột C, nếu không có ai biết (ngôn ngữ không có trong danh sách ở Sheet 1, trả về giá trị No One)

@Phan Thế Hiệp
@Duy Tùng 9x

Xin các bác chỉ em thêm ạ, em cảm ơn!

Mình thử dồn về 1 sheet cho dễ nhìn bạn nhé, bạn thử công thức này

IFERROR(VLOOKUP(G2,CHOOSE({1,2},T(INDIRECT(TEXT(AGGREGATE(15,6,ROW($B$2:$D$6)*10^3+COLUMN($B$2:$D$6)/($B$2:$D$6<>""),ROW(INDIRECT("1:"&COUNTA($B$2:$D$6)))),"R000C000"),)),INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($1:$50)/($B$2:$D$6<>""),ROW(INDIRECT("1:"&COUNTA($B$2:$D$6)))))),2,0),"No One")

Có file đính kèm, bạn tham khảo.

Thân

 

[uronmapu]

Mình thử dồn về 1 sheet cho dễ nhìn bạn nhé, bạn thử công thức này

IFERROR(VLOOKUP(G2,CHOOSE({1,2},T(INDIRECT(TEXT(AGGREGATE(15,6,ROW($B$2:$D$6)*10^3+COLUMN($B$2:$D$6)/($B$2:$D$6<>""),ROW(INDIRECT("1:"&COUNTA($B$2:$D$6)))),"R000C000"),)),INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($1:$50)/($B$2:$D$6<>""),ROW(INDIRECT("1:"&COUNTA($B$2:$D$6)))))),2,0),"No One")

Có file đính kèm, bạn tham khảo.

Thân

Em cảm ơn nhiều ạ, kể mà được ở sheet 2 thì tuyệt quá bác ạ.
Em thử lệnh cho Sheet2 ntn nhưng nó trả về 0 hết bác ạ.

=IFERROR(VLOOKUP(A2,CHOOSE({1,2},T(INDIRECT(TEXT(AGGREGATE(15,6,ROW(Sheet1!$B$2:$D$6)*10^3+COLUMN(Sheet1!$B$2:$D$6)/(Sheet1!$B$2:$D$6<>""),ROW(INDIRECT("1:"&COUNTA(Sheet1!$B$2:$D$6)))),"R000C000"),)),INDEX(Sheet1!$A$2:$A$6,AGGREGATE(15,6,ROW(Sheet1!$1:$50)/(Sheet1!$B$2:$D$6<>""),ROW(INDIRECT("1:"&COUNTA(Sheet1!$B$2:$D$6)))))),2,0),"No One")

 

[hongminh_0306]

Em cảm ơn nhiều ạ, kể mà được ở sheet 2 thì tuyệt quá bác ạ.
Em thử lệnh cho Sheet2 ntn nhưng nó trả về 0 hết bác ạ.

=IFERROR(VLOOKUP(A2,CHOOSE({1,2},T(INDIRECT(TEXT(AGGREGATE(15,6,ROW(Sheet1!$B$2:$D$6)*10^3+COLUMN(Sheet1!$B$2:$D$6)/(Sheet1!$B$2:$D$6<>""),ROW(INDIRECT("1:"&COUNTA(Sheet1!$B$2:$D$6)))),"R000C000"),)),INDEX(Sheet1!$A$2:$A$6,AGGREGATE(15,6,ROW(Sheet1!$1:$50)/(Sheet1!$B$2:$D$6<>""),ROW(INDIRECT("1:"&COUNTA(Sheet1!$B$2:$D$6)))))),2,0),"No One")

Bạn dùng công thức này.

=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($1:$100)/($B$2:$D$6=G2),1)),"No One")

 

[Sil_90]

hi có 2 cách giải quyết trong trường hợp này. hy vọng có thể giúp bạn giải quyết vấn đề.
1- là sử dụng công thức Excel:
cột H (language) =IFERROR(INDEX($B$2:$D$6,INT((ROW(A1)-1)/3)+1,MOD(ROW(A1)-1,3)+1),"")
cột I (full name) - =IF(H2="","",INDEX($A$2:$A$6,INT((ROW(A1)-1)/3)+1))
2- sử dụng VBA:

Sub Update_language_name()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long, j As Long
Dim languageCol As Range
Dim outputRow As Long

Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
outputRow = 2
For i = 2 To lastRow
For j = 2 To 4
If ws.Cells(i, j).Value <> "" Then
ws.Cells(outputRow, "H").Value = ws.Cells(i, j).Value
ws.Cells(outputRow, "I").Value = ws.Cells(i, "A").Value
outputRow = outputRow + 1
End If
Next j
Next i
End Sub

 

[uronmapu]

Bạn dùng công thức này.

=IFERROR(INDEX($A$2:$A$6,AGGREGATE(15,6,ROW($1:$100)/($B$2:$D$6=G2),1)),"No One")

Em cảm ơn ạ, công thức chuẩn rồi bác ạ.
Em dùng ntn cho cell ở sheet 2

=IFERROR(INDEX(Sheet1!$A$2:$A$6,AGGREGATE(15,6,ROW($1:$100)/(Sheet1!$B$2:$D$6=A2),1)),"No One")

 

[Phuocam]

Em cảm ơn ạ, công thức chuẩn rồi bác ạ.
Em dùng ntn cho cell ở sheet 2

=IFERROR(INDEX(Sheet1!$A$2:$A$6,AGGREGATE(15,6,ROW($1:$100)/(Sheet1!$B$2:$D$6=A2),1)),"No One")

Có trường hợp 1 môn có nhiều người học không? Nếu có, phải dùng TEXTJOIN() để nối.

 

[uronmapu]

Có trường hợp 1 môn có nhiều người học không? Nếu có, phải dùng TEXTJOIN() để nối.

1 môn chỉ có 1 người thôi bác ạ (hoặc ko có ai), 1 người có thể biết nhiều môn. Em cảm ơn bác.

 

[uronmapu]

Nếu dùng công thức này ở Sheet2 thì khi sử dụng thay đổi dữ liệu ở cột 1 máy tính hơi lâu và đợi khoảng 20-30s bác @hongminh_0306 ạ (dữ liệu của em mới chỉ có khoảng 130 dòng)

=IFERROR(INDEX(Sheet1!$A$2:$A$6,AGGREGATE(15,6,ROW($1:$100)/(Sheet1!$B$2:$D$6=A2),1)),"No One")

Nếu dùng công thức này ở Sheet 2 thì tính toán nhanh, nhưng lại ko tách được thành 2 cột, để em tính tổng hay filter bác @Duy Tùng 9x ơi.
=LET(a, B26, b, A2:A6,i, LAMBDA(x,TOCOL(IFS(a<>"",x),2)), HSTACK(i(a),i(b)))

 

[Phan Thế Hiệp]

Nếu dùng công thức này ở Sheet2 thì khi sử dụng thay đổi dữ liệu ở cột 1 máy tính hơi lâu và đợi khoảng 20-30s

Thử vầy xem có đỡ hơn chút nào không: Sheet2!B2=

=IFERROR(INDEX(Sheet1!$A$2:$A$200,MATCH(1,MMULT(--(Sheet1!$B$2:$D$200=A2),{1;1;1}),)),"No one")

Enter, fill xuống.

Thân