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,

 

[SkipVought]

Is the value in L6 found in Products column A?

On which value does it fail?

 

[Vatchunter]

Skip,

Yes, the CONCATENATED field is in the A column in the workbook that is being searched.

Thanks!

 

[Vatchunter]

Forgot to mention working in Excel 2010, version 14.

Thanks,

Frank

 

[SkipVought]

How about the other questions?

 

[Vatchunter]

I'm sorry I don't understand the question on which value does it fail?

 

[SkipVought]

When you get an N/A, what's the lookup value and what is the corresponding value in column A?

"I have found places where #N/A is being returned but the data does exist is the file.

 

[Vatchunter]

Skip,

It would be much easier if I could post the info for you to see but I can't because of its sensetivity... Let me do the best I can to describe it.

This is on the place I found where I know the date exists in both tables. The data is all upper case, there are no spaces in the string, there are three fields that are primarily number, 1 that is mixed number/text 1 that is all text. Visually, the info looks identical in both tables. I think I have found the field causing the problem on this particular instance, if I type the value in the field my VLOOKUP works.

Not sure of any other functions to make sure the data is all the same.

Thanks,

Frank

 

[SkipVought]

there are three fields that are primarily number, 1 that is mixed number/text 1 that is all text."

Three fields? I thought we were talking about ONE field??? The lookup field!

 

[Vatchunter]

I'm talking about the 5 separate fields that make up the one lookup field.

 

[SkipVought]

This is not rocket science. On the row where you have the N/A, if you COPY that lookup value in column L, and then do a FIND in the lookup column in the lookup table, what happens?

 

[Vatchunter]

It does not find the info either....

 

[SkipVought]

Then you do NOT have a lookup match! The N/A is valid.

 

[Vatchunter]

I understand that... BUT they should be the same, it's either a space(s) or data type mismatch that is keeping these records from getting a valid match. Need to figure out how to get the data on both work sheets consistent.

 

[SkipVought]

Well unless we can SEE a valid example of BOTH values, there's nothing else to suggest!

 

[mintjulep]

You might try throwing CLEAN() at it.

 

[Vatchunter]

Mintjulep,

You might have found the ticket! On the test group I was using I did the Clean Function, (No Change) in result, followed with Text to Columns in both worksheets and it worked! Will work on the actual files later this evening or tomorrow morning. Will post the outcome.

Thank you both for the assistance!

Frank

 

[SkipVought]

You mentioned Text to Columns.

Where is this data coming from? Maybe a text file that has been generated by some other application. I always look for opportunities to tap a table source whenever possible, which is a much better course than table to text file to workbook.

 

[PWD]

But that's because you ARE a rocket scientist Skip!!!! Ha ha!!

Many thanks,
D€$

 

[kjv1611]

mintjulep,

Thanks for mentioning the CLEAN() function. I didn't even realize - or else forgot - that it exists. I will have to try it out sometime. I can see where it would easily come in handy on occasion.

Vatchunter,

Any updates on this? Did you find the solution, get your issue resolved? Looks like you've left this thread hanging, and you've got one of the all-time greats trying to offer you some assistance. If you've resolved your issue, please post some feedback/final results.

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