Index match formula that can match within a string of text??

[willow1985]

Hello,

I am not even sure if this is possible but am hoping someone can help me write a formula that can match text and return the value from another column.
The problem is what I need matched is a numberic 6 digit number always following rather: "WO " or "W/O "

Any help would be greatly appreciated!!

Here is an example of the data and a picture of where I want to put the formula:

Book1
	A	B
1	WO#	PO#
2	110500
3	140899
4	106270
5	117953
6	110222
7	175123
Sheet1

Book1
	A	B
1	PO#	Notes
2	4696	PLEASE SHIP FEDEX WO 117953
3	4697	W/O 106270 SHIP FEDEX
4	4698	SHIP FEDEX ACCOUNT XXXX999WO 140899
5	4699	Please ship fedex
6	4700	Account# XXXXXX
7	4701	WO 110500 replacement
8	4702	Economy
Sheet2

 

[steve the fish]

You could try:

=INDEX(Sheet2!$A$2:$A$8,IFERROR(MATCH("*WO "&A2&"*",Sheet2!$B$2:$B$8,0),MATCH("*W/O "&A2&"*",Sheet2!$B$2:$B$8,0)))

 

[jtakw]

Hi,

This will work, change sheet # to match your data:

Book3.xlsx
	A	B
1	WO#	PO#
2	110500	4701
3	140899	4698
4	106270	4697
5	117953	4696
6	110222
7	175123
Sheet816

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(LOOKUP(2,1/SEARCH(" "&A2&" "," "&Sheet817!B$2:B$8&" "),Sheet817!A$2:A$8),"")

Book3.xlsx
	A	B
1	PO#	Notes
2	4696	PLEASE SHIP FEDEX WO 117953
3	4697	W/O 106270 SHIP FEDEX
4	4698	SHIP FEDEX ACCOUNT XXXX999WO 140899
5	4699	Please ship fedex
6	4700	Account# XXXXXX
7	4701	WO 110500 replacement
8	4702	Economy
Sheet817

 

[willow1985]

Both work perfectly! Thank you very much

 

[jtakw]

You're welcome, thanks for the feedback.