Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 198
  • Last Modified:

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
Asked:
AIX25
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
barry houdiniCommented:
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
 
NBVCCommented:
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
 
Michal_ZyzakCommented:
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!

 
AIX25Author 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
 
NBVCCommented:
What are some expected results in column E?
0
 
Michal_ZyzakCommented:
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
 
barry houdiniCommented:
Did you try using the method I suggested above? - see attached

regards, barry
vlookup.xlsx
0
 
AIX25Author Commented:
@harryhoudini, its not working for me as your output looks good. I have attached mine.
Book1.xlsx
0
 
barry houdiniCommented:
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
 
AIX25Author 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
 
barry houdiniCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now