Loncat ke daftar isi utama

Bagaimana cara vlookup dan mengembalikan nilai yang cocok dengan komentar sel?

Saat Anda menerapkan fungsi Vlookup untuk mengembalikan nilai yang cocok, itu hanya akan mengekstrak nilai tanpa pemformatan, seperti warna isian, font atau komentar dll. Namun, terkadang, Anda mungkin perlu vlookup dan mengembalikan nilai yang cocok termasuk komentar juga untuk mendapatkan hasil screenshot berikut. Bagaimana Anda bisa menyelesaikan pekerjaan ini di Excel?

Vlookup dan mengembalikan nilai yang cocok dengan komentar sel dengan menggunakan kode VBA


Vlookup dan mengembalikan nilai yang cocok dengan komentar sel dengan menggunakan kode VBA

Kode VBA di bawah ini dapat membantu Anda untuk vlookup dan mengembalikan nilai yang cocok dengan komentarnya, lakukan hal berikut:

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, lalu salin dan tempel kode berikut di Modul Jendela.

Kode VBA: Vlookup dan mengembalikan nilai yang cocok dengan komentar sel:

Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .Comment Is Nothing Then
                .Comment.Delete
            End If
            If Not xCell.Comment Is Nothing Then
                .AddComment xCell.Comment.Text
            End If
        End With
    End If
End Function

3. Dan kemudian simpan kode dan tutup jendela kode, masukkan rumus ini: = vlookupcomment (H2, A2: C10,3, FALSE) ke dalam sel kosong untuk menemukan hasilnya, dan tekan Enter kunci, nilai yang cocok serta komentar dikembalikan sekaligus, lihat tangkapan layar:

Note: Dalam rumus di atas, H2 adalah nilai pencarian yang ingin Anda kembalikan nilainya yang sesuai, A2: C10 adalah tabel data yang ingin Anda gunakan, nomornya 3 adalah nomor kolom yang berisi nilai cocok yang ingin Anda kembalikan.

Alat Produktivitas Kantor Terbaik

馃 Kutools AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria    VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar    Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel    Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk


Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
Comments (20)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
txs a lot. highly appreciated!
This comment was minimized by the moderator on the site
Hello this is a very good function, but it works for Notes, not Comments (this is how it is translated in my native language at least). Is there some way to change it from Notes to Comments? Also, is there a way to keep the cell format? (color of the original cell, etc).
This comment was minimized by the moderator on the site
i've had the same issue. i can only say that having tried via vba to copy comments and notes... only notes are being copied, not the comments (although the have the same meaning, but a different functionality)... Notes seem to be of a static nature, unlike comments where you can keep "posting" to adding new text...Probably that's the reason....
This comment was minimized by the moderator on the site
Hi, fana,
To extract the matched records with the comment in Office 365, please apply the below code:
Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
'Updateby Extendoffice
    Application.Volatile
    Dim xRet As Variant 'could be an error
    Dim xCell As Range
    xRet = Application.Match(LookVal, FTable.Columns(1), FType)
    If IsError(xRet) Then
        VlookupComment = "Not Found"
    Else
        Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
        VlookupComment = xCell.Value
        With Application.Caller
            If Not .CommentThreaded Is Nothing Then
                .ClearComments
            End If
            If Not xCell.CommentThreaded Is Nothing Then
                .AddCommentThreaded xCell.CommentThreaded.Text
            End If
        End With
    End If
End Function


After pasting the code, apply this formula: =vlookupcomment(H2,A2:C10,3,FALSE) as well.

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Not working for me. I get #NAME? error when i use this formula. Please help.
This comment was minimized by the moderator on the site
Hello, Sajjad,Did you put the VBA code of this article into your workbook? Please check it.Or which Excel version do you use?Thank you!
This comment was minimized by the moderator on the site
This is a wonderful. But when using this code I find the file crashes a lot on Excel 365. when removing auto save I found it鈥檚 a little better. But with multiple users in the file, the file will crash all the time. Does this code use a lot of memory or is it a compatibility issue? Thoughts? Thanks
This comment was minimized by the moderator on the site
Hi! I'm so glad I found this, the thing is, this actually works on notes, and not comments. is there a way to have work on comments and not notes? in notes i cannot tag my coworkers and i cannot reply either. thanks a lot!
This comment was minimized by the moderator on the site
i can do that at the first time. but after i try to use changing formula of multiple cells. It doesn't work now. after even i type vlookupcomment, the cell becomes blank.
This comment was minimized by the moderator on the site
My comment is too large for the default box size. Is there any way to increase the comment box size or possibly decrease the font size?
This comment was minimized by the moderator on the site
If anyone else needs this.


Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant

'Updateby Extendoffice

Application.Volatile

Dim xRet As Variant 'could be an error

Dim xCell As Range

xRet = Application.Match(LookVal, FTable.Columns(1), FType)

If IsError(xRet) Then

VlookupComment = "-"

Else

Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)

VlookupComment = xCell.Value

With Application.Caller

If Not .Comment Is Nothing Then

.Comment.Delete

End If

If Not xCell.Comment Is Nothing Then

.AddComment xCell.Comment.Text

.Comment.Shape.ScaleWidth 2, msoFalse, msoScaleFromTopLeft

.Comment.Shape.ScaleHeight 2.5, msoFalse, msoScaleFromTopLeft

End If

End With

End If

End Function
This comment was minimized by the moderator on the site
I want only Comment as Cell Value using Vlookup...
This comment was minimized by the moderator on the site
Thanks, It's helpful & I have something to askIt returns with cell comment but the Image (Inserted using fill effects ) does not show
Please , help me out of this Issue
This comment was minimized by the moderator on the site
Shamim,
Sorry for that there is no direct code for solving your problem, if anyone has the solution, please comment here.
This comment was minimized by the moderator on the site
Frank
Hello,

In case of spreadsheet protected, when I open my workbook the cell return the error #VALUE!

How we can solve this problem?
This comment was minimized by the moderator on the site
Hello, Frank,
After inserting the code, you should save your workbook as Excel Macro-Enabled Workbook format, so that the code will not lose.
Please try! Thank you!
This comment was minimized by the moderator on the site
Can we use cell in column B (order column) as comment for cell in column C (name). exc, comment for Helen is 80.
thank you for your help.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
Rate this post:
0   Characters
Suggested Locations