Microsoft Excel 2003 (full product)

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 403063Note: Data in Col. A & B are fixed (permanent).

TIA.

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 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:

=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

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??

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

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.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History