Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Help with Excel Formula

Posted on 2013-02-06
Medium Priority
196 Views
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
Question by:AIX25
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 2
• +1

LVL 50

Assisted Solution

barry houdini earned 2000 total points
ID: 38861119
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

LVL 23

Expert Comment

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

LVL 4

Expert Comment

ID: 38861282
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 Comment

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

LVL 23

Expert Comment

ID: 38861378
What are some expected results in column E?
0

LVL 4

Expert Comment

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

LVL 50

Expert Comment

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

regards, barry
vlookup.xlsx
0

Author Comment

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

LVL 50

Assisted Solution

barry houdini earned 2000 total points
ID: 38861514
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 Comment

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

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 38861652
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month5 days, 14 hours left to enroll