Function to match contents of comments ?

[elgato74]

Hi

please have a look at the attached workbook.

On sheet1, I have numbers and names. These names are also contained in Comments on sheet2, and i would like a function in Column M that matches the numbers with the names in the comments. Cell M1 I have manually filled for demonstration purposes.

Can this be done with a function or is this a VBA task?

Thank you muchly!

[AlKey]

Copy and paste this formula in M1 and drag it down


=IF(ISERROR(VLOOKUP("*"&A1&"*",CHOOSE({1,2},Sheet1!$C$1:$C$88,Sheet1!$B$1:$B$88),2,0)),"Not Found",VLOOKUP("*"&A1&"*",CHOOSE({1,2},Sheet1!$C$1:$C$88,Sheet1!$B$1:$B$88),2,0))

[elgato74]

mmmm. if i use that formula, it gives me "not found" in all cells

[AlKey]

Please see attached file

[elgato74]

I see. Is it not possible for the formula to look in the comments for a match on sheet1 ? For example "Andreas Vogel" has number 012222002736, but it says "not found" on sheet2.

[AlKey]

Unfortunately formulas can't read comments. The names and phone numbers are pulled from the sheet1. So the only way to look up is by the last name. However, there are few of them have a letter before the last name and some are not present on sheet1 that is way they are not found.

here are modified formulas:

for Phone number

=IF(ISERROR(VLOOKUP("*"&IF(ISERROR(FIND(" ",A1)),A1,REPLACE(A1,1,FIND(" ",A1),""))&"*",CHOOSE({1,2},Sheet1!$C$1:$C$88,Sheet1!$B$1:$B$88),2,0)),"Not Found",VLOOKUP("*"&IF(ISERROR(FIND(" ",A1)),A1,REPLACE(A1,1,FIND(" ",A1),""))&"*",CHOOSE({1,2},Sheet1!$C$1:$C$88,Sheet1!$B$1:$B$88),2,0))

for Names

=IF(ISERROR(VLOOKUP("*"&IF(ISERROR(FIND(" ",A1)),A1,REPLACE(A1,1,FIND(" ",A1),""))&"*",CHOOSE({1,2},Sheet1!$C$1:$C$88,Sheet1!$B$1:$B$88),1,0)),"Not Found",VLOOKUP("*"&IF(ISERROR(FIND(" ",A1)),A1,REPLACE(A1,1,FIND(" ",A1),""))&"*",CHOOSE({1,2},Sheet1!$C$1:$C$88,Sheet1!$B$1:$B$88),1,0))

The only other way to get data from comments is Vba

You can use this UDF from chandoo.org

Please Login or Register  to view this content.

Then use formula:
=getComment(A1)

[elgato74]

Thank you very much. I guess I will have to enter the exact name in the cell and not just in the comment for the formular to match it correctly.

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).