Hoppa till huvudinnehåll

Hur slår jag upp och returnerar matchande värde med cellkommentar?

När du använder Vlookup-funktionen för att returnera matchningsvärdet extraherar det bara värdet utan formateringen, till exempel fyllningsfärg, teckensnitt eller kommentar etc. Men ibland kan du behöva slå upp och returnera det matchade värdet inklusive kommentar också för att få följande skärmdumpresultat. Hur kunde du lösa detta jobb i Excel?

Vlookup och returnera matchande värde med cellkommentar med hjälp av VBA-kod

Vlookup och returnera matchande värde med cellkommentar med hjälp av VBA-kod

Nedanstående VBA-kod kan hjälpa dig att slå upp och returnera matchat värde med sin kommentar, gör så här:

1. Håll ner ALT + F11 nycklar för att öppna Microsoft Visual Basic för applikationer fönster.

2. Klicka Insert > Modulerna, kopiera och klistra sedan in följande kod i Modulerna Fönster.

VBA-kod: Vlookup och returnera matchande värde med cellkommentar:

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. Och spara sedan koden och stäng kodfönstret, ange denna formel: = vlookupcomment (H2, A2: C10,3, FALSE) in i en tom cell för att hitta resultatet och tryck på ange nyckel, det matchade värdet såväl som kommentaren returneras på en gång, se skärmdump:

Anmärkningar: I ovanstående formel, H2 är det uppslagsvärde du vill returnera motsvarande värde, A2: C10 är datatabellen du vill använda, numret 3 är kolumnnumret som innehåller det matchade värdet du vill returnera.

Bästa kontorsproduktivitetsverktyg

🤖 Kutools AI Aide: Revolutionera dataanalys baserat på: Intelligent utförande   |  Generera kod  |  Skapa anpassade formler  |  Analysera data och generera diagram  |  Anropa Kutools funktioner.
Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup  |   VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar   |  Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler   |  Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser:  12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50+ Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

Beskrivning

Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
Comments (21)
No ratings yet. Be the first to rate!
Archana Navin
Archana Navin
This comment was minimized by the moderator on the site
skyyang - Thank you, it worked for me. However, only my last comment gets pulled up. How do I get all the comments posted for the specific cell.

Thanks,
AN
Reply
0
0
fana
fana
This comment was minimized by the moderator on the site
txs a lot. highly appreciated!
Reply
0
0
Gekido
Gekido
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).
Reply
0
0
fana
fana     Gekido
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....
Reply
0
0
skyyang
skyyang     fana
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!
Reply
Report
0
0
Sajjad
Sajjad
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.
Reply
0
0
skyyang
skyyang     Sajjad
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!
Reply
Report
0
0
Julie
Julie
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’s 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
Reply
0
0
camille
camille
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!
Reply
0
0
CYC
CYC
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.
Reply
0
0
Meheimer
Meheimer
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?
Reply
0
0
Meheimer
Meheimer     Meheimer
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
Reply
0
0
Biplab Das
Biplab Das     Meheimer
This comment was minimized by the moderator on the site
I want only Comment as Cell Value using Vlookup...
Reply
0
0
Shamim Taifur
Shamim Taifur
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
Reply
0
0
skyyang
skyyang     Shamim Taifur
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.
Reply
Report
0
0
Frank Man
Frank Man
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?
Reply
0
0
skyyang
skyyang     Frank Man
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!
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations