Index Match Search Not Using Exact Match

Hello,

In the attached file I have some macros that go through and rename pdf files that come out of our accounting system and then pushes a copy of the renamed file to another location. I have some search functionality in the file that searches for the entity name to figure out what the renaming is however if I have entities of similar names it will not find the exact match on the name but the first closest match. Any idea how I can change the formula in column H on the Rename Calculation Tab to do a search for the exact match to get the right naming? Cell H10 is the example where it isn't finding the right match. if you look at the yellow Tables (2) tab it has the entities NorthTown Mall and NorthTownMall_Co03104. It is picking up the first instance when it should grab the second.

So our accounting system generates reports and gives them a very long report number with an odd description. This file does a search on the entity name and once it finds it it builds the path to rename the pdf file via macro. once renamed I then have a macro that moves a copy to a second path for reporting requirements. so the file does a search on the entity and report name to restructure the file path so it can rename it. the way that formula is working now i get a conflict because it wants to rename the second instance with a name that already exists. Is there a way around it so it picks up the second instance instead of the first?

Your match IS in fact using Exact Match, so it will return the first match.

If you want it to return the last match, try
=IFERROR(LOOKUP(2^15,SEARCH(lstSearchText2,$A10),lstSearchText2),"No")

would that work as a replacement though where it would pull the first item for the first one in the list and the second item for the second one?

As long as the table lstSearchText2 is sorted in a way that the more specific string is listed first...

OK
NorthTown Mall_Co03104
NorthTown Mall


NOT OK
NorthTown Mall
NorthTown Mall_Co03104

Wait, that's backwards.
The more specific string should come last, which looks like the way you have it already.

NOT OK
NorthTown Mall_Co03104
NorthTown Mall


OK
NorthTown Mall
NorthTown Mall_Co03104

It works for some and breaks for others. I have some that have about 4 to 6 similar names.

For Example?

it isn't in the file i sent you however there are about 180 paths that I go through and rename. An example is the list below. It pulls Staten Island for every instance. Is there a way to add an exact formula or something?

Staten Island Mall Phase II
Staten Island Mall Phase I
Staten Island Mall Wendys
Staten Island Mall
Staten Island The Crossings

and thank you very much for the assistance.

Those should be listed with the More specific strings last (doesn't have to be alphabetical)
Staten Island Mall
Staten Island Mall Wendys
Staten Island Mall Phase I
Staten Island Mall Phase II
Staten Island The Crossings


Sorting the data in Ascending order should take care of it.

what is weird is the original formula works for the Staten Island names just not the North Town name. And the formula you gave me fixes the North Town Name but breaks the Staten Island name

Did you re-arrange those items as I suggested?
Can you attach a new sample?

you are the bomb. I believe that did it. I will test it out more later and let you know. Much appreciated.

You're welcome.

Also make note, there can't be any blanks in the range lstSearchText2