Searching Columns for Values

[Sineadd]

Hi

When I use the below formula in cell E2 it searches to see if the value in D2 matches any value in colum A and if it does it returns the value in the notes column.
My issue is some data has more than one number ( Like in A2) so this returns a blank - Is there a formula to use that is able to search if the value is in the cell and retur the value in the notes field

Number	Notes		Number	Notes
1234 / 5678	Yes		1234​	=IFERROR(INDEX($B:$B,MATCH(D2,$A:$A,0)),"")
2356​	No		2356​	No
1269​	Yes		1269​	Yes
5698 / 69852	No		69852​
12345678​	Yes		12345678​	Yes

 

[awoohaw]

Match will only get you the first instance of a type of match. The 3rd argument of 0 means exact match, so you will not get a hit on 69852 because there is a additional text in that cell ("5698 / "). There is not a "wild card" match argument. But, using wildcard character may help. MATCH("*"&D2&"*",$A:$A,0) may help.

Mr Excel Questions 9.xlsm
	A	B	C
1
2	2	dk	kdfjakljfdlk
3			fjdkfjadk
4			dkfjadfj
Sheet2

Cell Formulas
Range	Formula
A2	A2	=MATCH("*"&B2&"*",$C$2:$C$4,0)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Number	Notes		Number	Notes
2	1234 / 5678	Yes		1234	Yes
3	2356	No		2356	No
4	1269	Yes		1269	Yes
5	5698 / 69852	No		69852	No
6	12345678	Yes		12345678	Yes
Master

Cell Formulas
Range	Formula
E2:E6	E2	=XLOOKUP("*"&D2&"*",$A$2:$A$6&"",$B$2:$B$6,"",2)

 

[Sineadd]

How about

Fluff.xlsm
	A	B	C	D	E
1	Number	Notes		Number	Notes
2	1234 / 5678	Yes		1234	Yes
3	2356	No		2356	No
4	1269	Yes		1269	Yes
5	5698 / 69852	No		69852	No
6	12345678	Yes		12345678	Yes
Master

Cell Formulas
Range	Formula
E2:E6	E2	=XLOOKUP("*"&D2&"*",$A$2:$A$6&"",$B$2:$B$6,"",2)

Thank you - This is working perfect

 

[Fluff]

You're welcome & thanks for the feedback.