I want to match this string: [nnnn]*, nnnn is a number held in cell A5 so I would use match like this:
=MATCH("[1301]*", B5:B40,0)
This returns 20 correctly.
I can’t work out how to replace this “[1301]*” so that the number, in this case 1301, is obtained from cell A5.
Can you help? Thank you.
=MATCH("["&A5&"]*", B5:B40,0)
Should work if you don’t need leading zeroes, or if A5 contains a “number as text” without leading/trailing spaces.
If you need something “robust”, the TEXT() and TRIM() functions can be useful additions to the formula for eliminating input errors.
Thank you, I thought I might need to use CONCATENATE and wasn’t sure about using wildcards
Very pleasing have now used with range names and named range like this:
=INDEX(list_ProcessName, MATCH("["&D4&"]*",list_ProcessName,0))
where list_ProcessName is a a named range. Unfortunately it seems not to work with an external file ref.
Thank you again!