Performance is slow // Need Alternate method// Unable to join the tables

Hello Experts, 

Need a solution or workaround. Below are the situations. 

We have 2 tables. 1st table named: PartFinder, it has 3 columns and the structure is like is 

Anoter table named ReferenceTable which is having all the columns filled with, the structure is something like this:

The user wants to search either of the columns value from  PartFinder to the ReferenceTable. 

For example: 

1. If user search with MAN of PartFinder table to the ReferenceTable with the value A, it should give the result like: 

2. If user search with MPN of PartFinder table to the ReferenceTable with the value W, it should give the resulelike: 

3. If user search with MATERIAL_NUBER of PartFinder table to the ReferenceTable with the value 1, it should give the resut like: 

We couldn't join these tables, as there is no particular key we can use for. We have used ISFILTERED function, and it's working fine, but the performance is very slow. We just want to get rid of this slowness, so trying to join these two tables which will significantly give me the best performance. Tried to bring the values of PartFinder table to the Reference table using LOOKUPVALUE and create a combined key to join, but  Circular dependency is coming which is normal. 

We need a solution for this situation. Please help.