Vlookup of duplicate data from a seperate sheet

[saenz9000]

I have a project that I have been working on that is beyond my realm of knowledge. The data in the forecast sheet changes in sequence so I need a search by SKU and give me usages in the planning sheet. This seems to be working, but I have duplicates of SKU depending on the line that I need to get a total for planning. See corresponding colors.

[Lifesigns]

Hi,

The Vlookup & Hlookup formulas only return results from the first match, so you if you are going to use them you have to create a Unique ID for each entry. I've done this in Column A of the forecast by adding together the SKU and Prd Line. Then you just change the vlookup so it looks in Column A instead.

I've done the first three for you.

Although this is not best way, you can perhaps look at using Sumifs instead I put them below the green. If you used them they wouldn't need the extra column, plus they are faster and easier to work with.

Hope that helps.

Apologies the Sumifs won't work unless you have 2007...

[alansidman]

Put a helper column in the forecast sheet and changed the vlookup accordingly. Only did three columns.

Alan

[saenz9000]

Thank you both, I feel I have learned so much in the past week. I thought I could not use the helper column as the person I am building this for may have trouble doing this consistently, but it seems okay. The Vlookup Lifesigns did for me works great, but I am curious as to how the sumif works. I have 2007, but the gold cells read #NAME?. I will fiddle around with it a to see if I can figure it out.

[Lifesigns]

If you open it in 2007 and it still shows as #Value then try F12 to save as and then select .xlsx as the extension. Then if you reopen the file it should show correctly.

[saenz9000]

Aha! I just leanred that I have Excel version 2003. The vlookup is working for me and I am thankful.

[Lifesigns]

Yea sadly Sumifs are a new feature in 2007 but you can also use Sumproduct.

I've attached the file but changed the Sumifs to Sumproduct.

[saenz9000]

Thats awesome!