Is this crazy or am I. Match function result different than expected.

[DigitalZugzwang]

I have a workbook with a number of tables for dependent lists. Below is the formula for the third dependent list and it works for the most part, but I am getting strange results. When certain values are selected in column B the problem part of the below formula will result in #N/A or the wrong row. If I change the value in the table that is giving me a problem the code will work if it "likes" the new value.

For example, if I put in the word Flag Pole I get the #N/A but if I put in Test it will result in 3 when it should have been 5. I have no idea what is going on here. PLEASE HELP!

INDIRECT(INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")),MATCH(B23,INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"["&SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"]",0)),2))

Below is the portion of the formula that is acting up.

MATCH(B23,INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"["&SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"]",0))

Below are two of the tables I'm using Activity (1 column) and Erection (2 columns)

Activity Erection Category Erection Guyed Base Cost Install Mono Base Cost Repair Self Support Base Cost Inspection Water Tank Base Cost Modification Flag Pole Base Cost Decom Purchase/Sale Sub Ancillary <tbody> </tbody>



<tbody>
</tbody><colgroup><col></colgroup>

 

[FDibbins]

Really hard to say without seeing a better sample of what you are working with, but are you putting that text in A23, or where?
Why are you (from the looks of it) doubling up on the text?
INDIRECT(
SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"["&
SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"]",0))

and what are the [ and ] for?

 

[DigitalZugzwang]

No I was pulling the text from A23. The reason I was doubling up like that is to get a table and column reference because I'm using dependent lists. So I needed to get Activity[Activity] to target the Activity table and the Activity column of that table. Its the whole reason I named the column and the table the same thing.

That being said I found a work around with vlookup. I don't like using vlookup but it is working.

 

[Jonmo1]

That formula is 'really' hard (and I mean really) to decipher.
But it looks like you have the 3rd option in the match function omitted. Which makes match do a 'closest match' type of lookup.
If that is the case, then match assumes your data is sorted in ascending order (which it's not).
All sorts of strange things happen with unsorted data and the match doing a 'closest match' type.

Your match needs to have the 3rd argument set to 0 to make it do an exact match.
MATCH(B23,INDIRECT(SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"["&SUBSTITUTE(SUBSTITUTE(A23,"/","")," ","")&"]",0),0)