Vlookup with 3 search Criteria

[neuman43]

Hello Excel Gurus, I have a problem for you to solve and I have attached the spreadsheet for you.
If under transportation costs, the Supplier, Item, and Plant Match up with the corresponding Bid Price for Supplier, Item, and Plant, I want Excel to perform a function to add those two costs for that respective year and return a value in column AJ, AK, & AL for each year.


Hope you guys can help me on this one.

thanks
Brett

[NBVC]

what do you mean by "add up those two costs"? Can you give example of results expected for first few items?

[neuman43]

Yes,
In the highlighted fields, the 2013 Transportation Costs and the 2013 Bid Price are added together only when the 3 search criteria (Site, Part, and Supplier) correspond to each other. I need a function to search the three criteria in Z, AA,& AB and find the matching 3 criteria in columns S, T, & U, and then add the two costs outlined above.

Does this help explain it?(see attached)

[NBVC]

Try:

Please Login or Register  to view this content.

copied across and down

[neuman43]

Hi NBVC,
Thanks for the quick response but Its giving the ?Name Error. Any thoughts?

[NBVC]

Are you working in 2003 or 2007 version?

[neuman43]

working in 2003

[NBVC]

Your workbook was .xlsx

If in 2003, try:

Please Login or Register  to view this content.

[neuman43]

Thoroughly Impressed,
Can I ask why you used 2 "sumproducts" in the formula?

[NBVC]

One is for the first table, the other is for the second table.

Sumproduct adds values in a range, based on criteria being met in the same rows, so unless the rows in each table were aligned, we need to make 2 separate summaries and add together.

[neuman43]

Im so impressed with that formula.

thanks for the insight
-B