VLOOKUP Issues 1

[Vatchunter]

I am having problems with a VLOOKUP between 2 different work books. The field I am linking on is a field that is made up of data from 5 columns that I have CONCATENATED together. The function is finding the data in the H column but not consistently. I have found places where #N/A is being returned but the data does exist is the file... I think I am dealing with a numbers/text issue...

Here is what I have tried:

Text to Columns on the CONCATENATED fields in each work book.
Trim Function on the CONCATENATED field in each work book.
Text Function on the CONCATENATED field in each work book.
Substitute Function on the CONCATENATED field in each work book.

In the process of converting all 5 fields to text before building the CONCATENATED field.

Any suggestion would be appreciated!

Formula for building the CONCATENATED field
=UPPER(TRIM(BD6)&TRIM(BF6)&TRIM(BJ6)&TRIM(BA6)&TRIM(AS6))

Formula for VLOOKUP
=VLOOKUP(L6,'[list2014-08-13.xlsx]Products'!$A:$H,8,FALSE)

Thanks,

 

[kjv1611]

By the way, on the CLEAN() function, here's an interesting article / BLOG post:

http://exceluser.com/blog/797/excels-clean-function-is-more-powerful-than-you-think.html

"Excel’s CLEAN Function is More Powerful Than You Think"

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57