Using VLOOKUP across two sheets with multiple matches

[willallison]

I have two separate spreadsheets.
Sheet 1 has a product ID, Description and Barcode. The Cost column is empty and it is this that I am trying to fill from sheet 2.
Sheet 2 Product ID, Description and Cost.
As you can see, there are multiple lines with the same Product ID and description, but different barcode - these represent different sizes/colours etc. but they all cost the same.
So how do I fill column "D" - the Cost in sheet 1 from column "C" - the Cost in sheet 2?
I have assumed it is a VLOOKUP, but maybe something else altogether... Warning - I am an excel novice....

[wk9128]

(1) workbook : sheet 1
(2) worksheet name : sheet1
(3) Cell D2 formula, Drag down

[willallison]

Hmm...
1st of all - thanks! I would never have come up with that!
I pasted the formula into cell D2 of sheet 1, but was returned with "Cost", whereas I actually want it to return 2.5
I probably shouldn't have included the "Description" column... it was just to try to make things clearer....
So what I need is... in sheet 1 - whenever Product ID = 1, the corresponding Cost = 2.5 (from Sheet 2)
When Product ID = 2, Cost = 4.5
when Product ID = 3, Cost = 10.5
etc

Apologies for the confusion

[menem]

It may be easier, if you can combind both sheets into the same file.
Then simply vlookup (or sumif) can slove your request.
[cole]=vlookup(A2,Sheet2!$A$1:$C$4,3,false)[/code]

Attachment 745319
Regards.

[willallison]

Thanks again... that worked :-)
That's basically what I was doing but across two files and couldn't get it to work... probably also to do with the ! and $ signs I didn't have
Much appreciated

[wk9128]

POST #2

formula OK

Please watch this animation teaching file FILE name as S9

[willallison]

Thanks wk9128 ... never doubted your formula - just my interpretation!

[willallison]

Frustratingly, whilst I got the VLOOKUP than Menem posted to work in the example file, I now can't get it to work in my actual one!
I have tried changing the cells so they are all number, or general, or text in the hope that might fix it but no luck, so have attached the actual file
Sorry - you have both been very helpful - would you mind taking a look and see where I am going wrong?
I am trying to populate column K in sheet1 (Cost) from column E in Sheet2 (cost), based on their common SKU - column A in both sheets

[wk9128]

worksheet name : Sheet1

Cell K2 formula , Drag down

[willallison]

sorry - still no luck... that just gives me empty cells...

[wk9128]

Please remove the @ symbol in your formula first, and try again

[willallison]

Apologies if I have missed something... but there is no symbol..??

[AliGW]

You don't see this?

=IFERROR(VLOOKUP(--A2,--@Sheet2!$A$2:$F$107,5,),"")

[willallison]

Wow... bizarre!... no that is 100% not in my formula!
This is copy paste straight from the command line....
=IFERROR(VLOOKUP(--A2,--Sheet1!A2:F107,5,),"")

I would post an image, but can't....

[AliGW]

This won't work properly:

=IFERROR(VLOOKUP(--A2,--Sheet1!A2:F107,5,),"")

It will need to be this:

=IFERROR(VLOOKUP(--A2,--Sheet2!$A$2:$F$107,5,),"")

or this:

=IFERROR(VLOOKUP(--A2,--Sheet2!$A$2:$F$107,5,0),"")

[AliGW]

Any luck?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[willallison]

Sorry... my mistake... I actually tried removing $'s to see what happened... pasted the wrong thing... I did actually have it like that..
Here it is again... I notice that when I click up into the command bar, it highlights the A2 in blue, but not the Sheet2... like it should...
I have typed the formula in manually, rather than cut/paste in case there is something happening.... but still no luck

[willallison]

Oh... and thanks for the tips... will definitely do all of that!

[AliGW]

When I open your workbiook it appears like this:

=IFERROR(VLOOKUP(--A2,--@Sheet2!$A$2:$F$107,5,),"")

As soon as I remove the @ sign, it works. Try this:

=IFERROR(VLOOKUP(--A2,Sheet2!$A$2:$F$107,5,0),"")

[willallison]

Might also be worth noting, that when I received the email notification about AliGW's response, there were indeed @ symbols in the test of the email! But when I look at them in the forum response they are not there!!!

[AliGW]

That's because I removed them (they were not meant to be there - it was a typo).

See post #19.

[willallison]

Maybe my Excel is broken!!... either that or I am!! Still no luck.
Thanks for all your help. I will try sending the files to another computer and try them at home tonight... see if I have any better luck... will let you know how I get on.

[AliGW]

Did you open my attachment?

Try repairing your Office installation if all else fails.

[willallison]

Aha.. ok - I went back to your post #19 and downloaded the file... and you are right.. it is exactly as it should be
I'll try it at home tonight and see how I go....
Thanks again

[AliGW]

Please mark as solved (post #16).

[willallison]

I have marked as solved, Thanks AliGW...
Though I would really mark it as semi-solved!
Your copy of the spreadsheet does indeed work... but I can't figure out why mine doesn't... the @ symbol doesn't appear on my home computer when I open the file either.
Anyway... once again - thanks

[AliGW]

Are you using this:


=IFERROR(VLOOKUP(--A2,--Sheet2!$A$2:$F$107,5,0),"")

or this:


=IFERROR(VLOOKUP(--A2,Sheet2!$A$2:$F$107,5,0),"")

???

If the former, change to the latter. If this doesn't work, then there is an issue with your Excel installation, and that's beyond the scope of this thread (which should be marked as solved).

[willallison]

Aha!
Thanks - that fixed it....

[wk9128]

You're Welcome. Glad to help . Thank You for the feedback