Solved

Help with Excel Formula

Posted on 2013-02-06
11
188 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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

by:AIX25
Comment Utility
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
Comment Utility
What are some expected results in column E?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 4

Expert Comment

by:Michal_Zyzak
Comment Utility
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
Comment Utility
Did you try using the method I suggested above? - see attached

regards, barry
vlookup.xlsx
0
 

Author Comment

by:AIX25
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now