This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 0%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES2%3C/text%3E%3C/svg%3E)
Hello All,
I have an excel file in which values are given in the format below in file-1 and I need to print the output in file-2 in which if I see only lmn.microsoft.com in Column1, then I need to print ValueA in Column-2.
File:1
Column A Column B
abc.shashank.com, pqr.shashank.com, xyz.shashank.saxena
cde.microsoft.com, stu.microsoft.com, lmn.microsoft.com ValueA
I have checked the Vlookup, but it needs full value to fetch the output from File-1, partial word is not working.
Can anyone help with this.
Regards,
@Anonymous
Could you please take a screenshot of File-1 and File-2?
As the sample "File:1" that you shared, its format and layout were cleaned by the post itself, so if you could take a screenshot on these data on Excel would be better.
Thanks for your understanding.
Hello @Emily Hua-MSFT ,
please use the below sheet for File-1,
Hi @Anonymous ,
Thanks for your sharing.
According to your images, I make a sample as following.
I use the formula =LOOKUP(1,0/FIND(A1,Sheet1!A$1:A$2),Sheet1!B$1:B$2) in B1 of Sheet 2 to to find the corresponding values.
If I have any misundertandings, you could let me know.
-------Update--------
If File-1 and File-2 are in one worksheet, you could refer to following.
Based on the image above of my sample, the formula is =LOOKUP(1,0/FIND(F2,A$2:A$3),B$2:B$3).
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hello @Emily Hua-MSFT ,
Thanks for the answer and what we need to do if the sheet is opposite, please find the below screenshot for your reference
Hi @Anonymous
I converted your this reply as comment as you have extra questions about my answer. You are welcome to post an answer if you have suggestions/solutions to share here. More information about, please check Answer and Comment in Microsoft Q&A.
Thanks for your understanding.
Thanks @Emily Hua-MSFT for the help but I need to learn how exactly it came because the data which I shared is not the actual data and for applying this formula to the original data, I have to learn the usage of each syntax and then only I will create a file for me.
Could you help me to get this.
@Anonymous
Sure ~
But I would delete the second formula that for situation: File-1 is the lookup sheet, and File-2 is the output file.
And I would reply back with the explanations later.
Thanks for your understanding.
Could you please provide an explanation on the second formula only.
@Anonymous
Yes, but I need to modify the previous second formula as a new one =LOOKUP(1,0/COUNTIF(A2,"*"&K$2:K$3&"*"),L$2:L$3).
There are 2 parts in this formula, the innermost formula is the Countif function, which supports wildcard characters of asterisk, an asterisk matches any sequence of characters. Another one is Lookup function.
COUNTIF(A2,"*"&K$2:K$3&"*") will get the number of cells that meet a criterion, that the number for each cell in range "K$2:K$3" included in A2. The The result is a numeric array like following image. 
0/COUNTIF(A2,"*"&K$2:K$3&"*") to convert the qualified value (1) in the numeric array to 0, and convert unqualified values (0) as errors #DIV/0!. 
@Anonymous
（Continue the above part）
Lookup(1,lookup_vector, L$2:L$3) will according to the numeric array (lookup_vector) above to find the largest value that is less than or equal to 1. More informatin about this Lookup function, please refer to following screenshot. 
