# Matching items in Excel

Microsoft Excel 2003 (full product)
January 15, 2010 at 09:37:55
Specs: Windows XP
 I have hundreds of matching pairs (552 to be exact) of data under Col. A & B. If I were to enter or paste some data into Col. C (into row 1 and down) similar to the data under Col. B. How can I have it show the match number under Col. A into Col. D? See sample below:A B C D 177 327048 328048 179 178 327049 328050 181 179 328048 401049 183 180 328049 402037 186 181 328050 402038 187 182 401038 402050 190 183 401049 403027 192 184 401050 403028 193 185 401051 403039 195 186 402037 187 402038 188 402039 189 402049 190 402050 191 402063 192 403027 193 403028 194 403038 195 403039 196 403050 197 403051 198 403063 Note: Data in Col. A & B are fixed (permanent).TIA.

See More: Matching items in Excel

#1
January 15, 2010 at 12:41:09
 Hi,The function to do this is VLOOKUP()Unfortunately VLOOKUP() wont return values from columns to the left of the value being looked up.So add a new column Cin C1 enter = A1and drag the formula down.Column B will have the values to be searched and column C will have the matched data to be returned.Your values to be searched are now in column DIn cell E1 enter this:=VLOOKUP(D1,\$B\$1:\$C\$22,2,FALSE)Drag the formula down alongside all the values to be matched in column D.The \$ signs keep the search range fixed.After dragging the formula down it should look like this in Cell E9 =VLOOKUP(D9,\$B\$1:\$C\$22,2,FALSE)The 2 in the formula means that the result is returned from the second column of the table.FALSE is required to ensure an exact match.More information can be found about VLOOKUP() in Excel help files. Regards

Report •

#2
January 15, 2010 at 13:44:02
 Hi Humar,I tried what you've suggested and it worked very well. The only problem (question) I still have which can't be explained is when I paste the data (which were copied from somewhere else) into the cells for checking, it worked perfectly. But when I type in similar (or even exact) data, it won't find a match. I can't figure out the difference or format of the data between the copied ones and the entered ones. Any idea??

Report •

#3
January 15, 2010 at 15:13:53
 Hi,As these are numbers, it may be that Copy and paste is text, but when entering directly into the cells, Excel recognizes them as numbers.Another problem can be spaces after a word - they are invisible but stop the match working.To test for Numbers vs. Text format the cells as 'General' and if Excel recognizes the data as a number it is right aligned, but if it's text its left aligned.You can use the LEN(A1) function to look for extra spaces - if the length is more than it should be you have extra spaces.Hope this helps.Regards

Report •

Related Solutions

#4
January 18, 2010 at 06:35:33
 Hi Humar,Thanks for all your suggestions. That's exactly what the problem was. I just have to check and maybe format the data first. Thanks again.

Report •