Relating values to $

[lucas257]

I'm finding this kind of tricky, I want to relate the material number on the Jan-Dec sheets to the MAT sheet which lists all of the materials and the price they cost per lb. To condense this down I need Material number on the Month sheets to relate to the price/lb on the MAT sheet for that material, so when i enter in total lbs scrapped for that material it will relate the price/lb on the MAT sheet to the lbs on the month sheets thus ending up with price in the $'s cell. I've tried tons of ways...I've found errors that i didn't even know excel could produce trying to get this to work correctly...Help

[MrShorty]

It might be nice to describe some of the things you have tried, so that we don't simply repeat what you have tried.

My first thought is to use a simple lookup function to return the cost/lb for a given part # (https://support.office.com/en-us/art...8-93a18ad188a1 help file for VLOOKUP() function). Something like =VLOOKUP(part#,MAT!$B$2:$C$142,2,FALSE) should return the price/lb for a given part#.

The main complication I see is that you have a mix of numbers and "numbers stored as text" in your part#'s, which causes all kinds of problems for Excel's lookup functions. I would recommend that you choose a data type (numbers or numbers stored as text -- my preference would be to convert them all to numbers), make certain that all of the part#'s are converted to that data type.

Is that something you have already tried?