getting #N/A error after using RIGHT SEARCH functions

I have attached a workbook that I am having challenges with and need some advise on how to resolve. You will notice that I have a report that was exported to excel. Since it does not have all the information that I need, I first used the RIGHT(B10,LEN(B10)-SEARCH("_c",B10)-1) in cell G10 to take the customer number out of the concatenated field (that had customer name and customer #). The result is correct but then I am then trying to use that cell to do a VLOOKUP to find the sales rep associated with that account in another source (TAB Name = Sales reps).

Anytime I try to refer to that cell (h10, h11, h12), I get a #/NA message. The entry for sales rep # is in the Sales rep List. Does the RIGHT SEARCH function create some other data type? Any thoughts as to how to resolve?


Thanks in advance for your help!

Jim

In G10 add a unary minus (--) to your formula:

Formula:

Please Login or Register  to view this content.

Otherwise you're comparing a number to text