How do I copy a cell that has matching items in a different cell

babe7362000

New Member
Joined
Mar 25, 2019
Messages
4
A

C1, C20
B

2
C

101X18N102MV4E
D

C1
E

101X18N102MV4E
1 C2, C3, C21, C23, C28, C31, C32, C33, C35, C36, C38, C42, C43, C44, C45, C46, C51, C52, C54, C56, C5821CC0603KRX7R9BB104C2

<tbody>
</tbody>


I am trying to figure out a way to copy C1 to E1 if A1 has the same value in the data as D1
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,731
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Place this formula in cell E1:
Code:
=IF(ISNUMBER(SEARCH(D1,A1)),C1,"")
 
Last edited:

babe7362000

New Member
Joined
Mar 25, 2019
Messages
4
What if D1 is not the same as A1 but might be in B1? I need to search in all of Column B. The first one worked great because it was matching the same as A1. But D3 is found in A1?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,731
Office Version
  1. 365
Platform
  1. Windows
What if D1 is not the same as A1 but might be in B1? I need to search in all of Column B. The first one worked great because it was matching the same as A1. But D3 is found in A1?
That is a bit different than the origianl question you asked:
I am trying to figure out a way to copy C1 to E1 if A1 has the same value in the data as D1
I see that you are new to the board. So here is a posting tip - don't oversimplify your question for the sake of posting it here. Otherwise, you may get answers that answer the specific question that you asked, but may not actually solve the problem that you REALLY have. It is best to be detailed as possible, and post questions that accurately reflect the nature of your data and do what you actually need.

Are the values in column A, in that any value you are looking up (i.e. "C20"), will only appear in EXACTLY one cell in column A?
Or could it appear multiple times?
If it could appear multiple times, which value do you want to return?
 
Last edited:

babe7362000

New Member
Joined
Mar 25, 2019
Messages
4

ADVERTISEMENT

How do I copy if the match is not in Cell A1 but is in A2 and I need to copy C2 to D2?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,731
Office Version
  1. 365
Platform
  1. Windows
How do I copy if the match is not in Cell A1 but is in A2 and I need to copy C2 to D2?
Please answer the three questions I asked in my previous reply.
 

babe7362000

New Member
Joined
Mar 25, 2019
Messages
4
The Values in Column D could be anywhere in Column A (but may be on a different line). So if it is found on a different Line, I need the same column C to be put in column E. So for instance I had C1 in line A3, I would need column C3 go into Column E1. C1 should only appear once. Hope this answers the questions you asked. Just kind of hard explaining it. lol
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,731
Office Version
  1. 365
Platform
  1. Windows
Well, we can use VLOOKUP with Partial Match and that ***almost*** works (see: https://exceljet.net/formula/partial-match-with-vlookup),
i.e. place this formula in D1.
Code:
=IFERROR(VLOOKUP("*" & D1 & "*",A:C,3,0),"")

Where this runs into problems is if you tried to look up a value like "C4".
In your example, "C4" is not listed anywhere. However, you have values like "C42", "C43", etc.
When it looks for "C4" as part of a string, it finds lots of matches, though there really isn't a perfect "C4" only match.
So you are going to have "false matches".

The only ways I can think to prevent this from happening is to:
- Change the format of your data so that all values are of the same length. So, if there are all to be 3 characters, you should have "C01" instead of "C1", etc.
or
- Fix your data table so that you only have one value per row in column A. We could ever write a macro to expand that out. If you do that, then a simply VLOOKUP will do what you want.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,128,074
Messages
5,628,493
Members
416,321
Latest member
tomazik123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top