Previous Topic Next Topic

Simple way in Excel to copy data

Closed
dean94x Posts 3 Registration date Sunday February 28, 2016 Status Member Last seen March 1, 2016 - Feb 28, 2016 at 06:32 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Mar 3, 2016 at 11:10 AM
Greetings, Can anyone help me.

I am working on a huge project and looking for a simple way in Excel to copy data.

If I have a master document with alpha numeric values in column A lets say for example Sku001 through sku099, in column B is the corresponding value for each Sku...... (like a price list) ..... in column C i have 10 randomly selected Sku's.

sku003, sku0036, sku0051... and so on.....

My question is how can I use a formula in column D to take that SKU # from Column C, to search for the match in Column A, and put the corresponding value from Column B and put in Column D.

If anyone can solve this I would greatly appreciate it

Thank You

Dean Martin



1 response

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 1, 2016 at 11:18 AM
Hi Dean,

You are probably looking for the VLOOKUP formula.

=VLOOKUP(Search value, matrix, column index, 0)

So your formula in D2 would look like:
=VLOOKUP(C2,A2:B100,2,0)

Best regards,
Trowa
0
dean94x Posts 3 Registration date Sunday February 28, 2016 Status Member Last seen March 1, 2016
Mar 1, 2016 at 03:36 PM
Thank you very much for responding. i'm half way there, i now just have 2 issues with the formula.
1. for some reason if the sku is a number eg. 2551, i get a result of #N/A, but if if sku is alpha numeric eg 2551K, i get the correct result.

2. When i copy the formula down, it changes the parameters.........
original search parameter A2;b100, next line a3:b101, next line a4:b102 and so on......

Is there anyway to keep the search area constant ....... while only changing the search tern in column C

I really appreciate your time in helping me out.

Have a wonderful day
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Mar 3, 2016 at 11:10 AM
Hi Dean,

1. Not sure what is going on here. It shouldn't matter if the value in column A is Sku2551, 2551 or 2551k, as long as the value in column C is the same. Make sure the values are the same, So if column A contains the value Sku2551 and you want to look up the value 2551 then there isn't a match. Is this what you meant?

2. To keep the cell reference fixed, we use so called absolute reference by putting a dollar sign in front of the row and/or column. Easiest way to do this is to select the whole matrix (A2:B100) and hit F4.

Best regards,
Trowa
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
AMD data change update new data to DMI
Luis -
dwebb -

1 response
Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response