Combining, Matching, Multiple Worksheets

**strikinglyfamiliar** · 06-13-2012, 01:51 PM

I have a major project that I am working on. In the example I have attached, I show where I have the two sheets, with similar information. (Formula I was given to do the initial VLOOKUP is in F2)
I need to combine the two, but have the information matched across each row so that the final result is a matching part number, description, but also the additional information for each row is pertinent. I have to start with this file, because I have two more to add to it making the final result the combination of four databases that will be match, and I can then manipulate the data correctly.

Combine Project.xlsx

Thank you in advance for any help that you can give.

**arlu1201** · 06-13-2012, 02:43 PM

That vlookup is not working because the numbers in column E are formatted differently than column G.

Select all the contents of column G (row 2 onwards), you will see a yellow diamond with an exclamation mark to the left of column G. Select that and select Convert to number. Then you will find the vlookup values populating in column F.

**strikinglyfamiliar** · 06-13-2012, 04:54 PM

The original formula that was given to me, and still didn't work was =VLOOKUP(TRIM($I6),Sheet2!$F16637:$J19284,3,FALSE)

As you may see the person was triming the data, in string I6, pulling from Sheet 2 to find the correct data from between F16637 and J19284, placing it in the 3rd cell from the I6 string, making an exact match (I hope I am understanding and explaining the delima correctly)

My whole problem is that when I do convert data into a number, it truncates it to a point that it is no longer an exact match (dropping any 00 prefix) and I am then returned a #N/A result. When it was all explained, and sent to me originally, the person dropped several of the steps that made this all work in the original file and now I have two more files that I have to combine with my previous example, and all data must be associated to the correct part number.

**arlu1201** · 06-14-2012, 03:14 AM

Why this range F16637 and J19284?

When i converted the data into a number, it worked fine.

**strikinglyfamiliar** · 06-14-2012, 09:11 AM

The range is set for the original sheet provided. When I did as you said, it worked. Question is, now I have two more databases to add to this work book, so will the same formula work when I add the data to the right of the existing data. I did notice that the formula worked as you said, when I made your suggested changes, and even worked when columns were added so I have added to your reputation, but am holding off on marking this as solved until I get all the data in place.

Thank You for your help.