VLookup not working

[Summer123]

Hello, can some one please assist on why this formula is not working? I've inheritated this workbook from someone else so really its not making any sense at this point.

=IF(ISERROR(VLOOKUP(CONCATENATE(LEFT($C5,FIND(" / ",$C5)-1), "_",M$1),TCA_lOOKUP,2,FALSE)),IF(ISERROR(VLOOKUP(CONCATENATE(RIGHT($C5,LEN($C5)-(FIND(" / ",$C5)+2)), "_",M$1),TCA_lOOKUP,2,FALSE))," ","X"),"X")

So there is the above formula for many cells on sheet1. From what i can tell it basically looks for these records on sheet3 and if they are there then on sheet1 it will mark with an X under that cell. It works for some cells but not all (What is TCA_Lookup thats listed above? it seems to me like on sheet 3 there is some logic there that i cant pin point). Does anyone know why?

please assist
Summer

 

[NBVC]

So what's happening is that the formula looks at cell C5 and first takes everything the the left of " / " which is blank, slash, blank and it concatenates that with an underscore and then with what is found in cell M1. It looks for that new string in a table called TCA_lOOKUP. If you are using Excel 2003, go to Insert|Name|Define, and you should find that name. Click it and in the refers to box, you should see where the named range is located. If you click in that refers to field, you should jump to the table itself. In Excel 2007, go to Formulas tab and Name Manager to find it.

So it looks for the concatenated string in the first column of that table. And if it doesn't find it an error (#N/A) is returned, causing the formula to go to then next step and search a different string, now it is the part of C5 after the " / " concatenated with the underscore and the M1 value, and looks in same table. If not found there either, it returns a blank. If either of the concatenated strings are found, an X is returned, supposedly to identify that one of the 2 possible combinations are found.

Copying formula down, will check the next cell, C6 broken up, etc....

If it doesn't work when you think it should, check that an exact match exists.. looking for typos, extra spaces, etc...

 

[Summer123]

thank you! Ok so I went to name manager and extended the cells to go to the following
='Test'!$E$3:$G$65536

but for some reason it still wont populate the X's... it did before (up to G6) when the value in name manager was ='Test'!$E$3:$G$6... but it went to G6 so i changed the value since it can go more then that... but for some reason it now doesnt do any X's...

thanks again for your help.

 

[Summer123]

never mind i had the input value in correct.
thank you sooo much!