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:


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).

See More: Matching items in Excel

Report •

January 15, 2010 at 12:41:09

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 C
in C1 enter = A1
and 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 D
In cell E1 enter this:
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.


Report •

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 •

January 15, 2010 at 15:13:53

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.


Report •

Related Solutions

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 •

Ask Question