Note: The other languages of the website are Google-translated. Back to English

Làm cách nào để vlookup và nối nhiều giá trị tương ứng trong Excel?

Như chúng ta đã biết, Vlookup trong Excel có thể giúp chúng ta tra cứu một giá trị và trả về dữ liệu tương ứng trong một cột khác, nhưng nói chung, nó chỉ có thể lấy giá trị tương đối đầu tiên nếu có nhiều dữ liệu khớp. Trong bài viết này, tôi sẽ nói về cách vlookup và nối nhiều giá trị tương ứng chỉ trong một ô hoặc một danh sách dọc.

Vlookup và trả về nhiều giá trị phù hợp theo chiều dọc với công thức

Vlookup và nối nhiều giá trị phù hợp trong một ô với Hàm do Người dùng Xác định

Vlookup và nối nhiều giá trị phù hợp trong một ô với Kutools cho Excel


Giả sử, tôi có dải dữ liệu sau, để nhận tất cả các giá trị tương ứng dựa trên một giá trị cụ thể theo chiều dọc như hình minh họa sau đây, bạn có thể áp dụng công thức mảng.

doc vlookup nối 1

1. Nhập công thức này: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") vào một ô trống nơi bạn muốn đặt kết quả, ví dụ: E2, rồi nhấn Ctrl + Shift + Enter các phím với nhau để nhận giá trị tương đối dựa trên một tiêu chí cụ thể, xem ảnh chụp màn hình:

doc vlookup nối 2

Chú thích: Trong công thức trên:

A1: A16 là phạm vi cột chứa giá trị cụ thể bạn muốn tìm kiếm;

D2 cho biết giá trị cụ thể bạn muốn vlookup;

B1: B16 là dải cột mà bạn muốn trả về dữ liệu tương ứng;

$ 1: $ 16 cho biết tham chiếu hàng trong phạm vi.

2. Sau đó, chọn ô E2 và kéo chốt điền xuống các ô cho đến khi bạn nhận được các ô trống và tất cả các giá trị phù hợp được liệt kê vào cột như hình minh họa sau:

doc vlookup nối 3


Thay vì nhận các giá trị tương đối theo chiều dọc, đôi khi, bạn muốn các giá trị phù hợp vào một ô và nối chúng bằng một dấu phân tách cụ thể. Trong trường hợp này, Hàm do Người dùng Xác định sau đây có thể giúp ích cho bạn.

1. Giữ phím tắt ALT + F11 phím để mở Microsoft Visual Basic cho các ứng dụng cửa sổ.

2. Nhấp chuột Chèn > Mô-đunvà dán mã sau vào Mô-đun Cửa sổ.

Mã VBA: Vlookup và nối nhiều giá trị phù hợp trong một ô

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Sau đó, lưu và đóng mã này, quay lại trang tính và nhập công thức này: = cusvlookup (D2, A1: B16,2) vào một ô trống nơi bạn muốn đặt kết quả và nhấn đăng ký hạng mục thi , tất cả các giá trị tương ứng dựa trên một dữ liệu cụ thể đã được trả lại vào một ô có dấu cách, xem ảnh chụp màn hình:

doc vlookup nối 4

Chú thích: Trong công thức trên: D2 cho biết các giá trị ô mà bạn muốn tra cứu, A1: B16 là phạm vi dữ liệu mà bạn muốn tìm nạp dữ liệu, số 2 là số cột mà giá trị phù hợp sẽ được trả về, bạn có thể thay đổi các tham chiếu của luận đề theo nhu cầu của mình.


Nếu bạn có Kutools cho Excel, Với khả Các hàng kết hợp nâng cao bạn có thể nhanh chóng hoàn thành công việc này một cách dễ dàng. Tính năng này có thể giúp bạn kết hợp tất cả các giá trị phù hợp với một dấu phân cách cụ thể dựa trên cùng một dữ liệu trong một cột khác.

Kutools cho Excel : với hơn 300 bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn trong 30 ngày.

Sau khi cài đặt Kutools cho Excel, vui lòng làm như sau:

1. Chọn phạm vi dữ liệu mà bạn muốn nhận các giá trị tương ứng dựa trên dữ liệu cụ thể.

2. Sau đó nhấn vào Kutools > Hợp nhất & Tách > Các hàng kết hợp nâng cao, xem ảnh chụp màn hình:

3. Trong Các hàng kết hợp nâng cao hộp thoại, bấm vào tên cột mà bạn muốn kết hợp dựa trên, sau đó bấm Khóa chính nút, xem ảnh chụp màn hình:

doc vlookup nối 6

4. Sau đó bấm vào tên cột khác mà bạn muốn trả về các giá trị phù hợp và bấm Kết hợp để chọn một dấu phân tách để tách các giá trị kết hợp, hãy xem ảnh chụp màn hình:

doc vlookup nối 7

5. Và sau đó nhấp vào Ok , tất cả các giá trị tương ứng dựa trên các giá trị giống nhau đã được kết hợp với nhau bằng dấu phân tách cụ thể, xem ảnh chụp màn hình:

doc vlookup nối 8 2 doc vlookup nối 9

 Tải xuống và dùng thử miễn phí Kutools cho Excel ngay!


Kutools cho Excel: với hơn 300 bổ trợ Excel tiện dụng, dùng thử miễn phí không giới hạn trong 30 ngày. Tải xuống và dùng thử miễn phí ngay!

Các công cụ năng suất văn phòng tốt nhất

Kutools cho Excel giải quyết hầu hết các vấn đề của bạn và tăng 80% năng suất của bạn

  • Tái sử dụng: Chèn nhanh công thức phức tạp, biểu đồ và bất cứ thứ gì bạn đã sử dụng trước đây; Mã hóa ô với mật khẩu; Tạo danh sách gửi thư và gửi email ...
  • Thanh siêu công thức (dễ dàng chỉnh sửa nhiều dòng văn bản và công thức); Bố cục đọc (dễ dàng đọc và chỉnh sửa số lượng ô lớn); Dán vào Dải ô đã Lọchữu ích. Cảm ơn !
  • Hợp nhất các ô / hàng / cột mà không làm mất dữ liệu; Nội dung phân chia ô; Kết hợp các hàng / cột trùng lặp... Ngăn chặn các ô trùng lặp; So sánh các dãyhữu ích. Cảm ơn !
  • Chọn trùng lặp hoặc duy nhất Hàng; Chọn hàng trống (tất cả các ô đều trống); Tìm siêu và Tìm mờ trong Nhiều Sổ làm việc; Chọn ngẫu nhiên ...
  • Bản sao chính xác Nhiều ô mà không thay đổi tham chiếu công thức; Tự động tạo tài liệu tham khảo sang Nhiều Trang tính; Chèn Bullets, Hộp kiểm và hơn thế nữa ...
  • Trích xuất văn bản, Thêm Văn bản, Xóa theo Vị trí, Xóa không gian; Tạo và In Tổng số phân trang; Chuyển đổi giữa nội dung ô và nhận xéthữu ích. Cảm ơn !
  • Siêu lọc (lưu và áp dụng các lược đồ lọc cho các trang tính khác); Sắp xếp nâng cao theo tháng / tuần / ngày, tần suất và hơn thế nữa; Bộ lọc đặc biệt bằng cách in đậm, in nghiêng ...
  • Kết hợp Workbook và WorkSheets; Hợp nhất các bảng dựa trên các cột chính; Chia dữ liệu thành nhiều trang tính; Chuyển đổi hàng loạt xls, xlsx và PDFhữu ích. Cảm ơn !
  • Hơn 300 tính năng mạnh mẽ. Hỗ trợ Office / Excel 2007-2021 và 365. Hỗ trợ tất cả các ngôn ngữ. Dễ dàng triển khai trong doanh nghiệp hoặc tổ chức của bạn. Đầy đủ tính năng Dùng thử miễn phí 30 ngày. Bảo đảm hoàn lại tiền trong 60 ngày.
tab kte 201905

Tab Office mang lại giao diện Tab cho Office và giúp công việc của bạn trở nên dễ dàng hơn nhiều

  • Cho phép chỉnh sửa và đọc theo thẻ trong Word, Excel, PowerPoint, Publisher, Access, Visio và Project.
  • Mở và tạo nhiều tài liệu trong các tab mới của cùng một cửa sổ, thay vì trong các cửa sổ mới.
  • Tăng 50% năng suất của bạn và giảm hàng trăm cú nhấp chuột cho bạn mỗi ngày!
officetab dưới cùng
Nhận xét (16)
Chưa có xếp hạng. Hãy là người đầu tiên xếp hạng!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Làm thế nào để có được kết quả. Hãy giúp tôi. dữ liệu data1 kết quả a 1 a1 b 2 a2 c b1 b2 c1 c2
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Khi sử dụng cusvlookup, có một cách để thêm họ cũng như bằng dấu phẩy ở giữa có thể xuất hiện trong Cột C
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Thích hàm cho Excel 2013 nhưng đã sửa đổi nó một chút để thay đổi ký tự phân cách thành ";" thay vì "" và sau đó loại bỏ tiền tố ";" từ các giá trị được nối kết Các giá trị khớp với kết quả trong ví dụ của tôi sẽ có; result01 hoặc; result01; result02. Đã thêm phần bổ sung If Left (xResult, 1) = ";" để loại bỏ bất kỳ dấu ";" bổ sung nào. ở đầu chuỗi nếu nó là ký tự thứ nhất. Tôi chắc rằng có một cách làm gọn gàng hơn nhưng nó đã hiệu quả với tôi. :) Hàm CusVlookup (pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" Cho Mỗi rng Trong pWorkRng Nếu rng = pValue Thì xResult = xResult & ";" & rng.Offset (1, pIndex - 0) If Left (xResult, 1) = ";" Sau đó xResult = MID (xResult, 1) Kết thúc Nếu Kết thúc Nếu Tiếp theo CusVlookup = xResult Kết thúc Hàm
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Đặt điều kiện if cho kết quả nếu trống.

Hàm CusVlookup (lookupval, lookuprange As Range, indexcol As Long)
'cập nhật bởi Extendoffice 20151118
Dải Dim x As
Làm mờ kết quả theo chuỗi
kết quả = ""
Đối với mỗi x Trong phạm vi tra cứu
Nếu x = tra cứu thì
If Not result = "" Thì
result = result & "" & x.Offset (0, indexcol - 1)
Khác
result = x.Offset (0, indexcol - 1)
Cuối Nếu
Tiếp theo x
CusVlookup = kết quả
Cuối Chức năng
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Điều này thật tuyệt vời nhưng tôi đang tìm kiếm thứ khác, tôi có một bảng với RollNo StudentName sub1, sub2, sub3 ... Total Result, Khi tôi nhập Rollnumber, nó sẽ cho kết quả như "SName Sub1 64, sub2 78, ... Tổng 389, kết quả vượt qua ", có thể
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để xóa các giá trị trùng lặp trong nối không?
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, Jacob,
Có thể bài viết sau đây có thể giúp bạn giải quyết vấn đề của bạn.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Hãy cố gắng, hy vọng nó có thể giúp bạn!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để liệt kê các giá trị trùng lặp chỉ một lần, bằng cách sử dụng mã vba và công thức ở trên không? Tôi không chắc chắn nơi đặt câu lệnh countif> 1 trong thanh công thức hoặc trong chính vba. Xin vui lòng giúp đỡ
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
bạn có thể thêm hai điều kiện bổ sung để bỏ qua các ô trống và bỏ qua các ô trùng lặp: For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells (i) .Value = Condition thì
If ConcatenateRange.Cells (i) .Value <> "" Then 'SKIP BANKS
Nếu InStr (xResult, ConcatenateRange.Cells (i) .Value) = 0 Thì 'BỎ QUA NẾU TÌM KIẾM DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells (i) .Value
Cuối Nếu
Cuối Nếu
Cuối Nếu
Tiếp theo
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Tôi phải nói rằng, tôi đã cố gắng lấy công thức để kết hợp nhiều giá trị và trả chúng về một ô trong 2 ngày nay. "Làm thế nào để" này đã cứu tôi !! Cảm ơn nhiều! Tôi sẽ không bao giờ có được nó nếu không có Mô-đun của bạn!
Tôi có 2 câu hỏi. Tôi có dấu phân cách dưới dạng dấu phẩy thay vì dấu cách và do đó nó bắt đầu bằng dấu phẩy. Có cách nào để ngăn chặn dấu phẩy bắt đầu nhưng giữ nguyên phần còn lại không?
Câu hỏi thứ hai của tôi là; Khi tôi sử dụng điều khiển điền, nó sẽ thay đổi các giá trị phạm vi cũng như giá trị ô mà tôi muốn tra cứu. Tôi muốn nó tiếp tục thay đổi số ô mà tôi muốn tra cứu nhưng vẫn giữ nguyên các giá trị phạm vi. Làm thế nào tôi có thể làm cho điều này xảy ra?

Cảm ơn rất nhiều vì sự giúp đỡ của bạn!!
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
CusVlookup đã làm việc tuyệt vời cho tôi. Một cách khác để có một dấu phân cách khác là gói vào hai hàm thay thế. Dấu cách đầu tiên (từ trong ra ngoài) thay thế khoảng trống đầu tiên bằng không có dấu cách, dấu cách thứ hai thay thế tất cả các dấu cách khác bằng dấu "/" trong của tôi. Có thể sử dụng "," nếu bạn muốn có dấu phẩy.
= SUBSTITUTE (SUBSTITUTE (cusVlookup (D2, Table1,2), "", "", 1), "", "/")

Ngoài ra, nếu giá trị tra cứu của bạn không phải là cột đầu tiên, bạn có thể sử dụng 0 hoặc số âm để chuyển đến cột bên trái.
= SUBSTITUTE (SUBSTITUTE (cusVlookup (D2, Table1, -1), "", "", 1), "", "/")
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Chào Jeff,
Cảm ơn những chia sẻ của bạn, chắc hẳn bạn là một người đàn ông có tấm lòng nhân hậu.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Điều này đang hoạt động tuyệt vời đối với tôi - có cách nào để thay đổi nó để kiểm tra xem ô có chứa thay vì khớp hoàn toàn không? Về cơ bản, tôi có một danh sách các nhiệm vụ trong đó:
Cột A: Sự phụ thuộc (ví dụ: 10003 10004 10008)
Cột B: Tham chiếu Nhiệm vụ (ví dụ: 10001)
Cột C: Nhiệm vụ Phụ thuộc (cột cho kết quả công thức) - nơi nó sẽ tra cứu tham chiếu nhiệm vụ để xem những hàng nào chứa nó trong Cột A, sau đó liệt kê Tham chiếu nhiệm vụ của những tác vụ đó.

Ví dụ:

Hàng | Cột A | Cột B | Cột C
1 | | 10001 | 10002 10003
2 | 10001 | Năm 10002 | 10003
3 | 10001 10002 | Năm 10003 |
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
bạn muốn sử dụng hàm Guid () để kiểm tra một cái gì đó trong một chuỗi văn bản trong một ô. Bạn cũng có thể sử dụng Left () và Right () nếu bạn đang tìm kiếm chi tiết bắt đầu hoặc kết thúc.
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Có cách nào để lấy "tên" duy nhất cho "class1" không
Nhận xét này đã được giảm thiểu bởi người điều hành trên trang web
Xin chào, sym-john!
Biết đâu bài viết dưới đây có thể giải quyết được vấn đề của bạn, mời bạn xem:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
Không có bình luận nào được đăng ở đây
Để lại ý kiến ​​của bạn
Đăng với tư cách khách
×
Đánh giá bài viết này:
0   Nhân vật
Các vị trí được đề xuất

Kết nối với chúng tôi

Bản quyền © 2009 - www.extendoffice.com. | Đã đăng ký Bản quyền. cung cấp bởi ExtendOffice. | BẢN ĐỒ CHI NHÁNH
Microsoft và logo Office là các nhãn hiệu hoặc nhãn hiệu đã đăng ký của Microsoft Corporation tại Hoa Kỳ và / hoặc các quốc gia khác.
Được bảo vệ bởi Sectigo SSL