IF Error Index Match formula to give Not found instead of N/A error

Hi I have a cell that can sometimes be blank but usually has IDs. In some cases the IDs include a prefix of 3 letters that tells me what team that ID belongs to.

Look up values (in a separate sheet called specialties prefix)
A B
Car Cardiology
Der Dermatology

Data
ID Team (DI
CAR-001 Cardiology

TFT0101 Not Found
Der0014 Dermatology


IF(F2="","",IFERROR(INDEX('[Specialty Prefixes .xlsm]Prefix'!$B$2:$B$54,MATCH(LEFT(A2,3),'[Specialty Prefixes .xlsm]Prefix'!$A$2:$A$54,0),"not found"),INDEX('[Specialty Prefixes .xlsm]Prefix'!$B$2:$B$54,MATCH(LEFT(F2,3),'[Specialty Prefixes .xlsm]Prefix'!$A$2:$A$54,0),1)))

This formula works great to identify the prefix (if there is data in there) and provide me with the right team but when it does not find a prefix, it gives you N/A. I would like it to not show up as an error but to say "Not found" instead. I know how to do this with a simple formula but this has so many sections that no matter where i try to add the "not found", the formula does not work for me. Can anyone help?

Many thanks in advance.
Apologies if i have not followed the appropriate process. This is probably my 2nd post here!

You reference "Speciality Prefixes .xlsm" in your formula. Is your lookup table by any chance in another workbook? And one that's closed at the time you're expecting a result from it?

Some formulas won't work on closed workbooks and could be why you're getting an N/A err.

But as TMS says, a sample workbook would be of great help in providing a solution.

BSB

Hi

I'm trying to attach the look up spreadsheet as well as a small sample of my spreadsheet with the formula with explanations as to what i am struggling with and why. I've modified the formula to not give me black if the cell has no data as i would like the result if the Index/max formula does not find the prefix i am interested in, it says "not found".
I am not sure it is attaching properly though. let me know if you cannot see them.
Just to clarify the 2 files are currently open and the formula gives me either the right match or N/A. I just want N/A to say "not found" so another formula I have works. This however needs to work even if the other look up spreadsheet with the prefixes is closed. I guess i could consider adding the prefix list to a 2nd sheet on my spreadsheet potentially if necessary.

Thank you

Does this do what you need?

Formula:

Please Login or Register  to view this content.

BSB

Thank you, that worked perfectly!

Glad I could help

BSB