Search with wildcards symbols in IF fnction

[Alex Gildenberg]

Hello

I wonder if I can use IF function with wildcards.

If I have several values in the Excel column, like

B1
C1
A1
B2
D1

and I'd like to find last instance of a pattern "B*" (B2 in our case)
Is there a way to do so?

Thanks in advance
Alex

 

[Frank Kabel]

Hi
for your example try
=LOOKUP(2,1/(LEFT(A1:A100,1)="B"),A1:A100)

 

[Max]

One try:

Values posted are in col A, A1 down

Put in B1: =COUNTIF($A$1:A1,"B"&"*")
Copy B1 down

Put in say, C1: =OFFSET($A$1,MATCH(MAX(B:B),B:B,0)-1,)

C1 returns "B2"