0

This is my first excel task and I am a bit lost. Here are my sheets:

 sheet1
 name    product_id
 ABC
 DEF
 ERG
 ZYT


sheet2
name     product_id
ABC       1
DEF       2
WRT       8
ERG       9

Now what i want is for sheet product_id field to be filled in based on name field of the sheets. E.g. ABC is 1 in sheet 2 and in sheet 1, i need it to become 1 also. The number of rows differ (sheet 1 is shorter) and any not found names in sheet 1 should be ignored. In the end, I need sheet 1:

 sheet1
 name    product_id
 ABC      1
 DEF      2
 ERG      9
 ZYT

Since ERG and ZYT are not found in sheet 2, they should be ignored. The order of the names could be different so I am looking for away to scan/loop and replace, if i am making any sense.

I have seen this https://www.extendoffice.com/documents/excel/4112-excel-copy-column-based-on-cell-value.html but it keeps on saying formula incorrect.

I am not familiar with VBA so it would be nice if it can be done using simple excel formulas.

Update:

following the suggested answer, i did come close but somehow rows are skipped if A column (searched column) don't match

enter image description here

Nie Selam
  • 1,343
  • 2
  • 24
  • 56
  • nice spot @pnuts. Typo error, sorry. – Nie Selam Jun 03 '18 at 23:52
  • If you can type into an image you can type into an [mcve] that we do not have to retype. Post your redacted sample data into your question then highlight it and tap ctrl+K. Please [edit] your question to show a) what you started with b) what you expect and c) what you have tried on your own to accomplish b). Hint: 'this doesn't work' is not a valid error message. –  Jun 04 '18 at 01:20

1 Answers1

2

You can do this with a VLOOKUP. This goes in your sheet 1 in cell B2, then drop this down to your last cell

=IFERROR(VLOOKUP(A1,Sheet2!A:B,2,0),””)

If it does not find your value it will return “”. You can put anything inside the quotes if you want it to show something else, like maybe “Stack Overflow is Awesome”

urdearboy
  • 14,439
  • 5
  • 28
  • 58
  • Thanks @urdearboy. Noticed the typo already. It does the job row for row basis but it is better than nothing. Thanks. – Nie Selam Jun 03 '18 at 23:37
  • I did that but if shee1.A2=sheet.A2, it works. If not, it skips them (blank). But give me a minute to remove the IFERROR portion. – Nie Selam Jun 03 '18 at 23:54
  • The order won’t matter here, it will take your A2 value, scan column A on your second sheet for a match, and then return the corresponding value on Col B. If you remove the IFERROR, it will return “#N/A” for all values that are not found on sheet2 col A – urdearboy Jun 03 '18 at 23:57
  • Please let me attach a screenshot and you tell me if i am making a mistake. i have 800 rows only so the few missing rows are like 200 which is ok but wanted to learn it. – Nie Selam Jun 04 '18 at 00:00
  • Check the image in the question itself. – Nie Selam Jun 04 '18 at 00:07