Find last matching cell using wildcard

[Pritch]

I am trying to find a formula to match the last cell in a row range containing a set character and return the value from another row in the same column.

The closest solution I can find is:-
=LOOKUP(2,1/(A1:A10=J1),B1:B10)

but this only appears to work for exact matches.
Does anybody know how to use wildcard characters with this, or does anybody have any other suggestions?

 

[Aladin Akyurek]

Care to provide a small sample along with the expected result?

 

[Pritch]

In this example I want the result in column R to be the value in row 8 for the last column containing the letter O

last matching cell.xls
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
8	2.00	2.03	2.00	2.03	2.06	2.09	2.12	2.15	3.00	3.03	3.06	3.09	3.12	3.15	3.18	desiredresult
9					O			XXO								2.15
10								O			XO					3.06
11	O			XO			XXO									2.12
12		XXX
13				X-						XO					XXO	3.18
14													XO			3.12
15							XO			-O		XXX				3.03
16				XO			XXO	XO								2.15
17				---						O			XO	XXX		3.12
U15BHJ

 

[NBVC]

Like this?

Book2
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
8	2.00	2.03	2.00	2.03	2.06	2.09	2.12	2.15	3.00	3.03	3.06	3.09	3.12	3.15	3.18	desired result
9					O			XXO								2.15
10								O			XO					3.06
11	O			XO			XXO									2.12
12		XXX
13				X-						XO					XXO	3.18
14													XO			3.12
15							XO			-O		XXX				3.03
16				XO			XXO	XO								2.15
17				---						O			XO	XXX		3.12
Sheet1

Formula in R9: =IF(OR(ISNUMBER(SEARCH("O",C9:Q9))),LOOKUP(2,1/ISNUMBER(SEARCH("O",C9:Q9)),$C$8:$Q$8),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER copied down.

 

[Pritch]

That's spot on

Many thanks

 

[Aladin Akyurek]

Faster to invoke...

R9, copied down:

=LOOKUP(9.99999999999999E+307,SEARCH("O",C9:Q9),$C$8:$Q$8)

 

[NBVC]

That's spot on

Many thanks

I think my original suggestion was a little overkill, now that I look at it again .....

you could eliminate the Isnumber() checks and replace with this formula in R9 and you only need to confirm simply with ENTER.

=IF(COUNTIF(C9:Q9,"*O*"),LOOKUP(2,1/SEARCH("O",C9:Q9),$C$8:$Q$8),"")

or =IF(COUNTIF(C9:Q9,"*O*"),LOOKUP(9.99999999999999E+307,SEARCH("O",C9:Q9),$C$8:$Q$8),"") implementing "faster" method suggested by Aladin.