=MATCH Function not working even when =A1=A2=TRUE

[Ecologic]

I've been using Excel/VBA for years and I haven't ever come across something this frustrating.

I have a formula (see below), that results in #N/A because the MATCH function fails. I have checked formatting, TRIM() for spaces, and a CHAR() by CHAR() breakdown as well as using =cell=cell and got TRUE. Google hasn't been helpful. Please help!

=IF(A2="","",IF(L2="0",INDEX(Tables!$L$3:$L$100,MATCH(LEFT(J2,FIND("!",SUBSTITUTE(J2,"~","!",2))-1),Tables!$K$3:$K$100,0)),"Project"))

Basically I'm trying to take a department name in a table, take a portion of the name out as a string, and then match that to a lookup table and display the proper name for the department from that list.

 

[Ecologic]

Some Screenshots. Cell V2 is using the formula above with exact match parameter (0). Cell below is using a parameter of (1), while this works most of the time, I don't get why the exact match doesn't work.

 

[Ecologic]

https://ibb.co/nEjmrp
https://ibb.co/dqgAHU

Links didn't work the first time.

 

[Eric W]

Welcome to the MrExcel forum.

The only thing I see offhand is that the formula is looking in columns K:L on the Tables sheet, but your picture of the Tables sheet has the table in N:O.

 

[Ecologic]

Thanks for the reply. The sheet had some personal info in it that I had to delete before posting and I took the screenshots before and after that. Sorry about that. Here are the correct screenshots. I still haven't found a solution.

https://ibb.co/bC1ewp
https://ibb.co/n9Q6bp

 

[steve the fish]

Using the first code in your list:

OPL~EQ2~00053487

<colgroup><col width="119"></colgroup><tbody>
</tbody>

Try to convert it to:

OPL~~EQ2

to find your match.

 

[Eric W]

steve the fish raises a good point. ~ is used as a special wildcard character in such functions as MATCH or SUMIF. When found in your data, it confuses MATCH. You can double it as he says, or use SUBSTITUTE to change it to something else, or use a different formula that doesn't use MATCH, like:

	A	B	C	D	E	F
1	abc~def~zzzz	#N/A		nop~qrstuv	1
2	ghij~klm~yyy	2		ghij~klm	2
3	nop~qrstuv~xxx	1		xxx	3
4				rrrr	4
5				rrrr	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =LOOKUP(2,1/(LEFT(A1,LEN($D$1:$D$5))=$D$1:$D$5),$E$1:$E$5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>