question

shashanksaxena-6941 avatar image
0 Votes"
shashanksaxena-6941 asked emilyhua-msft edited

need to fill the value from Column A to Column B

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,

office-excel-itprooffice-scripts-excel-dev
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@shashanksaxena-6941
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.

0 Votes 0 ·

Hello @emilyhua-msft ,

Please find the sheet below:

File-1(lookup sheet)

143743-image.png


File-2(output file)

143761-image.png


0 Votes 0 ·
image.png (4.7 KiB)

Hello @emilyhua-msft ,

please use the below sheet for File-1,
143762-image.png


0 Votes 0 ·
image.png (7.9 KiB)

1 Answer

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft edited

Hi @shashanksaxena-6941,

Thanks for your sharing.

According to your images, I make a sample as following.
143710-capture61.jpg
143714-capture62.jpg

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.
143973-capture64.jpg
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.




capture61.jpg (31.2 KiB)
capture62.jpg (43.0 KiB)
capture64.jpg (40.4 KiB)
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @emilyhua-msft ,

Thanks for the answer and what we need to do if the sheet is opposite, please find the below screenshot for your reference

143872-image.png


0 Votes 0 ·
image.png (25.6 KiB)

Hi @shashanksaxena-6941
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.

0 Votes 0 ·

Thanks @emilyhua-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.

0 Votes 0 ·
Show more comments