Last wildcard match in a row and returning columnnumber

[RogerMaur]

Hello everyone,

I've spend weeks trying to find a solution on the internet for my spreadsheet. Usually I can find a solution or work something out, but this time I'm really stuck. It's also embarrissing to ask, because I have the feeling it's simple but I don't/can't see the solution.

Here is my problem:
I want to do a last wildcard match on a row.
For example The row contains code like B1, E2, B2, E1, N2, T1, T2, E3 etc. I want the last cell containing the letter 'B' and get that columnumber for using the Index function.

I am trying to use an arrray to accomplish this, but I can't get it to work.
Is there someone who can put me in the right direction?

I would be very thankfull!

Kind regards,
Roger

 

[rpaulson]

Roger

try this code.

r = 1 'row number

c = Rows(1).Find(What:="b", After:=Cells(r, "A"), LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
        , SearchFormat:=False).Column
        
MsgBox c

hth,
Ross

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH("B",A2:Z2),A2:Z2)

 

[RogerMaur]

Rpaulson,

Thank you very much for your time and effort. But I'm trying to accomplish it with formulas instead of VBA. Sorry for my unclear post. But nonetheless, I will make a note of your code. It could be helpfull for future reference.

Kind Regards,
Roger

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH("B",A2:Z2),A2:Z2)

Native column number...

=LOOKUP(9.99999999999999E+307,SEARCH("B",A2:Z2),COLUMN(A2:Z2))

 

[RogerMaur]

Aladin Akyurek,

Thank you for your time and help. I'm trying to adapt your formula. But because I use Excel 2010 and in a different language I have to change it a little. I will give you feedback once I 'translated' it.

Kind regards,
Roger

 

[RogerMaur]

Aladin Akyurek,

Thank you very much. I adapted your formula and it works like a charm! I would never come up with this solution. I was searching in the direction of using an array. Althought I do not fully understand how the formula works, but I will delve into it. Thank you for your help.

Kind regards,
Roger

 

[Aladin Akyurek]

Aladin Akyurek,

Thank you very much. I adapted your formula and it works like a charm! I would never come up with this solution. I was searching in the direction of using an array. Althought I do not fully understand how the formula works, but I will delve into it. Thank you for your help.

Kind regards,
Roger

You are welcome. Thanks for the update. Here is a small write-up on this type of formulas:
https://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html