This article illustrates how to copy comments in excel using VLOOKUP. The built-in VLOOKUP function in excel can find and return values only. But, it is not possible to copy comments along with values using this function. Using a user-defined function or UDF can be a way around doing this. Follow the article to learn how to do that.
Download Practice Workbook
You can download the practice workbook from the download button below.
Steps to Copy Comments in Excel Using User-Defined Vlookup Function
Assume you want to look up a value along with the comments (if any) in the range B5:C9. The lookup value is given in cell E5. Now you want to use a VLOOKUP formula to return a value associated with the lookup value along with comments if it contains any. But as we have said, the VLOOKUP function is not fit for this job, i.e. copying the comments. So, we will create a UDF which works with the same mechanism as the VLOOKUP function does.
Follow the steps below to be able to do that.
📌 Step 1: Open a New Module to Insert VBA Code
- First, press ALT+F11 to open the VBA window. Then select Insert >> New Module to create a new module as shown in the following picture.
- How to Extract Comments in Excel (3 Suitable Examples)
- [Fixed!] Comments in Excel Far Away from Cell (3 Possible Solutions)
- How to Add Comment in Excel (4 Handy Methods)
- How to Remove Comments in Excel (7 Quick Methods)
- Creating and Editing Excel Comments to Cells – [An Ultimate Guide]!
📌 Step 2: Write VBA Code to Create UDF
- After that, copy the following code using the copy button.
Function VLOOKUPCMT(lookup_value As Variant, table_array As Range, col_index_num As Long, range_lookup As Long) As Variant Application.Volatile Dim xReturn As Variant Dim yCell As Range xReturn = Application.Match(lookup_value, table_array.Columns(1), range_lookup) If IsError(xReturn) Then VLOOKUPCMT = "Not Found" Else Set yCell = table_array.Columns(col_index_num).Cells(1)(xReturn) VLOOKUPCMT = yCell.Value With Application.Caller If Not .Comment Is Nothing Then .Comment.Delete End If If Not yCell.Comment Is Nothing Then .AddComment yCell.Comment.Text End If End With End If End Function
- Next, paste the copied code onto the blank module as shown in the picture below.
📌 Step 3: Apply the User-Defined Function to Copy Comments
- Now, enter the lookup value (3) in cell E5.
- Finally, enter the following formula in cell F5. After that, you will get the desired result as follows. Here, the VLOOKUPCMT function works the same as the VLOOKUP function.
VBA Code Explanation:
- Function VLOOKUPCMT(lookup_value As Variant, table_array As Range, col_index_num As Long, range_lookup As Long) As Variant
Here, the VLOOKUPCMT function will be the alternative to the VLOOKUP function in excel. It has the same arguments as the VLOOKUP function.
Forces a recalculation whenever a calculation occurs in any other cells.
- Dim xReturn As Variant
Dim yCell As Range
Declaring necessary variables.
- xReturn = Application.Match(lookup_value, table_array.Columns(1), range_lookup)
Assigning values to the xReturn variable.
- If IsError(xReturn) Then
VLOOKUPCMT = “Not Found”
Returns Not Found in case of errors.
- Set yCell = table_array.Columns(col_index_num).Cells(1)(xReturn)
Assigns a cell reference to the yCell variable.
- VLOOKUPCMT = yCell.Value
The VLOOKUPCMT function returns the value of the cell referenced by the yCell variable.
- With Application.Caller
If Not .Comment Is Nothing Then
Delete any existing comments from the formula cell.
- If Not yCell.Comment Is Nothing Then
Copy the comment from the cell referenced by the yCell variable to the cell containing the formula.
Things to Remember
You may need to recalculate every time you lookup for a new value. Otherwise, you may get erroneous results.
Now you know how to copy comments in excel using the VLOOKUP formula. Hopefully, this has solved your problem. Please use the comment section below for further queries or suggestions. Do visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.
- How to Extract Comments from Word Document into Excel
- Difference Between Threaded Comments and Notes in Excel
- How to Read Full Comment in Excel (6 Handy Methods)
- Populate Comment from Another Cell in Excel (and Vice Versa)
- How to Hide Comments in Excel (4 Quick Methods)
- Print Comments in Excel (4 Suitable Ways)
- How to Find Comments in Excel (4 Easy Methods)