Index(match(),match()) with a partial search

[Afro_Cookie]

Hi all,

I am trying to use a partial text search in one of the matches and it is not returning any results. I've used this method numerous times in the past but never with a partial search. Can someone please take a look at my example below and let me know if they can help?

In my example, I want to input a PO number and have the job number populate based on a partial search of the part. I have also tried two iterations of the wild card function ("*"&"WO" as well as "*WO")

Work Order Current.xlsx
	A	B	C	D	E	F
1	JOB	PART	CUST PO		PO	4509480964
2	X001734016	JAR0680601	4509480964		JOB	#N/A
3	X001734097	JAR06806A	4509480964
4	X001734177	JAR06806WO	4509480964
Sheet3

Cell Formulas
Range	Formula
F2	F2	=INDEX(A2:A4,MATCH(F1,C2:C4),MATCH("*WO",B2:B4))

 

[Fluff]

You cannot use Index like that, the 1st match is for rows & the 2nd for columns.
How about

=FILTER(A2:A4,(C2:C4=F1)*(RIGHT(B2:B4,2)="WO"))

 

[Afro_Cookie]

You cannot use Index like that, the 1st match is for rows & the 2nd for columns.
How about

=FILTER(A2:A4,(C2:C4=F1)*(RIGHT(B2:B4,2)="WO"))

I tried this and it returned a #CALC! error

 

[Fluff]

That suggest you don't have anything that matches both criteria.

Fluff.xlsm
	A	B	C	D	E	F
1	JOB	PART	CUST PO		PO	4509480964
2	X001734016	JAR0680601	4509480964		JOB	X001734177
3	X001734097	JAR06806A	4509480964
4	X001734177	JAR06806WO	4509480964
Main

Cell Formulas
Range	Formula
F2	F2	=FILTER(A2:A4,(C2:C4=F1)*(RIGHT(B2:B4,2)="WO"))