Excel if, look, find, take, and put

[Bookmaster]

Hello,
I need help with the excel function or it could be a VBA.

I have an excel worksheet with tho sheets.



I need a function that will:

On Sheet 1, take the value found in field A2, then look for that value on Sheet 2 in Column B. If found, it should take the value from column A from the same row on Sheet 2 and write it on Sheet 1 in column N line 2.

Then he should take the value in field A3 on Sheet 1 and look for the same value on Sheet 2 in column B. When it is found, it should take the value from column A from the same line on Sheet 2 and write it on Sheet 1, in column N, in row 3

So it takes value from A on Sheet 1, look for that value in Column B on Sheet 2. When it is founds take the value from column A from the same line where the value is found and put it in column N in the line where starting value is taken, on Sheet 1.

Note that values in column B on Sheet 2 are complex, I mean it contains many words and characters. The function should find the same value anywhere in column B.



Example:



On Sheet 1 in field A2 I have the number 10710. The function should take that number, and look for it on Sheet 2 in column B. Let's say we find it in field B67. Now take the number from field A67 on sheet 2 and put it in field N2 on Sheet 1.

Then on Sheet 1 in field A3 I have the value HK1119630. The function should take that value, and look for it on Sheet 2 in column B. Let's say it was found in field B375. Now the function should take the value from field A375 from Sheet 2 and write it into field N3 on Sheet 1



The function should go all the way until it has passed all the values found on Sheet 1 in column A

 

[awoohaw]

Something like this: For office 365, if you have XLOOKUP. IM not sure if you have it in 2021.
Other wise use INDEX/MATCH.

WorkBook1.xlsx
	A	B	C	D	E	F	G	H
3	With Index/Match
4	sheet1				Sheet2
5	Col A	Col N			Col A	ColB
6	10710	91137			110584	64749	Row1
7	HK119630	507540			103134	34040	Row2
8					48222	79357	Row3
9	with XLOOKUP				42379	38271	Row66
10	10710	91137			91137	10710	Row67
11	HK119630	507540			63266	15297	Row68
12					25513	91265	Row373
13					37421	79561	Row374
14					507540	HK119630	Row375
15					34388	61975	Row376
16					59488	16381	Row377
17
Sheet1

Cell Formulas
Range	Formula
B6:B7	B6	=INDEX($E$6:$E$16,MATCH(A6,$F$6:$F$16,0))
B10:B11	B10	=XLOOKUP(A10,$F$6:$F$16,$E$6:$E$16)

 

[Bookmaster]

Hello awoohaw,

Thanks for the fast reply. I do have the XLOOKUP function and I am stuck in it just because I'm always getting results as #N/A.
My function is: =XLOOKUP(A2;'Sheet2'!B:B;'Sheet2'!A:A).
The problem could be that in range B:B I don't have just a value from A2 but also other values. In that case, XLOOKUP would have to search within every cell in the B column.

Do you have any suggestions, please?

 

[awoohaw]

Yep, when doing the XMATCH with the 'exact match' attribute, which is the default you get N/A when there is not an exact match. Does the cell Start with the same value? you could use one of the other atributes.. but that kind of matching also require some consistency. there are more expert ways of matching.. but I am not well versed in that.

Is there a way to parse the data in A2 to separate the lookup value out? Maybe use a helper column?

 

[Peter_SSs]

What about some sample data and expected results with XL2BB that exhibits the sort of variety that you have? Then explain again in relation to that visual sample data.

 

[Bookmaster]

Great!
Thanks for the tip.
Exact formula is = XLOOKUP("*"&A2&"*";'Sheet2'!B:B;'Sheet2'!A:A;;2)

With "*"& I covered all before and with &"*" I covered all after... basically covered found somewhere in line what II was looking for
Thanks for the guidance!

 

[Bookmaster]

We can close this thread now.

 

[Peter_SSs]

Glad you found a solution.

We can close this thread now.

We don't generally close threads in this forum as it is always possible that a better solution may come along later. However, if a post answers your question it can be marked as the solution, so you could consider that for post #6 since you did actually post the solution there.

 

[Bookmaster]

Done

thanks