I'm Stuck - Please, Please Help

[Todd265]

Hello Everyone,

I'm not that good at Excel and I desperately need to do something that is a bit beyond me, so would be truly grateful for the help of any Excel Geniuses out there....

Basically I have two tabs on a spreadsheet.

One tab contains an items unique reference number and some other info like prices.

The other tab contains the same unique item reference number and a barcode for the product.

I need to append the barcode info contained in tab 2 to the relevant product row in tab 1. Unfortunately tab 2 contains every single product from this manufacturer, whereas tab 1 contains the limited products that I purchase from them, so the rows don't match up, therefore it isn't just a copy and paste job - I need to do some kind of look up that matches the reference number and takes the barcode and puts it into the right cell.

I'm currently doing this manually and it's taking me forever, so I would truly be grateful for some help as I know this must be possible in Excel...

Thanks so much for reading and for considering this problem.

T

 

[Trevor G]

Look at using a VLookup Formula this will look at a cell contents then go to the next sheet look to find the match then return the barcode

 

[Ruddles]

I have two tabs on a spreadsheet

"Two sheets in a workbook"

Sounds like you need to use VLOOKUP. If you can't work it out for yourself, you'll need to tell us which rows and columns your data is in in each sheet so that we can suggest a formula for you to try.

You can paste a couple of sample rows from each sheet directly into a message but please use a smallish font and place borders around the cells so we can work out exactly which columns the data's in.

 

[Todd265]

Hi there,

thanks so much for your reply.

In sheet 1, the unique item reference is in Column B and the barcode would ideally go into Column K

In sheet 2 the unique item number is in column B and the barcode that I want to move to sheet 1 is in Column D.

Please let me know if you would prefer to see the data and I will post it.

Thanks again

T

 

[Darren Bartrup]

You'll need a formula like:

=VLOOKUP($B2,Sheet2!$B$2:$D$44,3,FALSE)

Enter it in cell K2 on your first sheet.
The part of the formula that says Sheet2!$B$2:$D$44 needs to reflect the size of your actual list on the second sheet, and will need to use the name of the second sheet rather than Sheet2.

This will return an #N/A error if the reference in cell B2 doesn't appear in your second sheet.

 

[Todd265]

I just got back from a meeting. Wow, thank you so much I will try this formula now!!!

T

 

[Todd265]

Hi Darren,

Something is not right - I think I made a mistake somewhere... I tried to adapt the formula to my spreadsheet and I'm getting N/A's everywhere despite knowing that there is a match on the second worksheet because of the manual matching I did....

Attached below is a screenshot of the first worksheet (the one where I wanted to embed the barcode)

Also attached is a screenshot of the second worksheet where I am taking the barcode from.

Since the missing barcodes start from row 172, I used the following formula which I inserted in cell K172.
=VLOOKUP($B172,Barcodes!$A$5:$D$280,3,FALSE)

I then copied this formula downwards from K172

Any idea what I'm doing wrong? Thanks

 

[rallcorn]

Change
=VLOOKUP($B172,Barcodes!$A$5:$D$280,3,FALSE)

to
=VLOOKUP($B172,Barcodes!$B$5:$D$280,3,FALSE)

 

[Todd265]

It's working - thank you so much Darren - this has been invaluable for me as it's something I will use again and again and it has saved me hours today alone.

Thanks for sharing your knowledge and I hope somebody can do you a similar favour someday soon.

All the best

T

 

[Todd265]

Sorry, that last post was directed to you as well Rallcorn - sorry for missing you off. Thanks so much guys!!