x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 200

# Help with Excel Formula

We have four columns A-D. Column A and B are Keys. While column C and D are values.

Column A has number sequence of that ranges from 1-100
Example:
1
2
3
4
etc...

Column B has the coordinated text value of Column A's  number sequence.
Example:
ABC
BFG
AZw
etc...

In column C and D have more repetitive similar values from column A.

How do I replace each value in column C and D with the column B's value.
0
AIX25
• 4
• 3
• 2
• +1
3 Solutions

Commented:
A formula can't replace existing values - you need VBA for that

....but you could use a formula in columns E and F and then copy > paste special  > values over columns C and D, i.e. in E1

=VLOOKUP(C1,\$A\$1:\$B\$100,2,0)

copy across to F1 and then down both columns

regards, barry
0

Commented:
Not sure I follow.

Can you give more examples of what is actually in C and D and how you want to replace them?
0

Windows Client EngineerCommented:
Hi,

I'm not sure I understood the problem.
You want to have same data in each row in column C and D as in column B ?
Like:
1  ABC  ABC  ABC
2  BFG  BFG  BFG
3  AZw  AZw  AZw
?

If yes then simply put =B1 in C1 and D1 mark both cells and doble-click the fill handle
0

Author Commented:
I have attached a sample. If you look at the key, where ever there is a number sequence that is in Column C or D, reference Column A and replace the value to Column B and put it in Column E. Is this possible?
Book1.xlsx
0

Commented:
What are some expected results in column E?
0

Windows Client EngineerCommented:
in Column E:

=concatenate(VLOOKUP(C2;A\$2:B\$11;2;0);VLOOKUP(D2;A\$2:B\$11;2;0))

Please just move data in columns C and D one row down so that the formula will not try to catch the merged cell A1
0

Commented:
Did you try using the method I suggested above? - see attached

regards, barry
vlookup.xlsx
0

Author Commented:
@harryhoudini, its not working for me as your output looks good. I have attached mine.
Book1.xlsx
0

Commented:
You have only put the formula in the top row - you need to "drag" the formula down,

To do that select both cells E1 and F1 together then put cursor on the bottom right of F1 until you see a black "+" the hold down left mouse button and drag the formula down the column

regards, barry
0

Author Commented:
Ok, it worked. However this was just a sample spreadsheet I attached with sample numbers. My spreadsheet is much more advanced and convoluted. Can you explain the formula so that I can adapt it to my spreadsheet?
0

Commented:
Sure...

In this formula

=VLOOKUP(C1,\$A\$1:\$B\$100,2,0)

C1 is the "lookup value" and that is always searched for in the first column of  the "table array" \$A\$1:\$B\$100, so the first column is \$A\$1:\$A\$100. C1 is searched for in that range and the formula returns a value from the column designated by the "column index" - 2 in the above formula - so that's the second column of \$A\$1:\$B\$100, so if C1 matches A14 then the formula returns B14.

The zero at the end (or you can use FALSE) designates an "exact match", so C1 has to exactly match a value in \$A\$1:\$A\$100.

If C1 doesn't match any value in \$A\$1:\$A\$100 the formula returns #N/A

Excel help also explains VLOOKUP quite well......

regards, barry
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.