Solved

Help with Excel Formula

Posted on 2013-02-06
11
195 Views
Last Modified: 2013-02-06
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
Comment
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
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 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

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

by:Michal_Zyzak
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

by:NBVC
ID: 38861378
What are some expected results in column E?
0
 
LVL 4

Expert Comment

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

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

regards, barry
vlookup.xlsx
0
 

Author Comment

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

Assisted Solution

by:barry houdini
barry houdini earned 500 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

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

by:
barry houdini earned 500 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question