Simple way in Excel to copy data

[Closed]
Report
Posts
3
Registration date
Sunday February 28, 2016
Status
Member
Last seen
March 1, 2016
-
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
-
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 reply

Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
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
Posts
3
Registration date
Sunday February 28, 2016
Status
Member
Last seen
March 1, 2016

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
Posts
2806
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 20, 2021
482
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