Index, Match, CountIf Formula returning inocrrect data

Hi all

{=INDEX(files!$A:$A,MATCH(1,COUNTIF($D40,"*"&files!$A:$A&"*"),0))}

I am using the formula above to extract a file number from a string of data where I never know where the file number will be (if it is there at all)

It works to a point however, due to the wildcards in the formula if my files list has W250/4 followed by W250/459 it will return W250/4 when the exact match is W250/459.

I think reversing the sort order of the files data Z-A rather than A-Z may fix this, but this would mean re-sorting data each time.

I was going to record a macro to resort on opening file, but macros are wiped from our servers overnight

If this is my only option then I will manually resort, but would love a way around this if possible!

Thanks all for any help - hope I have given enough details (new to this)

Jo

Thanks.

I attach a file and i have highlighted the section showing incorrect file ref (P603/2 rather than P603/25)

The files list on the second tab

Thanks for heads up

+1, a sample book would be very helpful.

Will the file number within D40 be seperated by spaces ?

"some text this is great file number more text here."

If so, try
=INDEX(files!$A:$A,MATCH(1,COUNTIF($D40,"* "&files!$A:$A&" *"),0))

Thanks Jonmo1

There is no way to predict where the file number will be: it could be at the beginning, middle or end, and there may or may not be a space after it - this field is filled in by users who NEVER follow instructions on format...

I have posted a made up example sheet which I hope helps

Thanks for your time

Jo

This is the root of all evil.

Originally Posted by jj190870

this field is filled in by users who NEVER follow instructions on format...

I think you already mentioned the best solution, to sort the file numbers in files!A:A in DEscending order.

Other than that, your efforts will be better spent educating the users on correct data entry.
Rather than racking your brain trying to make formulas that accomodate their laziness.
Once they know you'll do that, it will only get worse.

Thanks for confirming

I thought that might be what I would have to do, but thought I'd 'ask the experts' first

Thanks for you time

Jo

You're welcome.