Looking up values.

**gjergji** · 08-06-2012, 02:25 PM

lookup.xlsx

Hello I want to take the prices from sheet2 and put them on sheet1 but the date and material number has to match.
Thank You!

**tigeravatar** · 08-06-2012, 03:06 PM

gjergji,

In Sheet1 cell B2 and copied over and down to M290:

Please Login or Register  to view this content.

I don't know why your month name headers have a fake space (character code 160) at the end of them.

**gjergji** · 08-07-2012, 02:08 PM

can you give me a formula if you took out the spaces from the months

**tigeravatar** · 08-07-2012, 02:10 PM

Please Login or Register  to view this content.

**gjergji** · 08-07-2012, 02:32 PM

Originally Posted by tigeravatar

Please Login or Register  to view this content.

But this gave me the sum I want to get the actual price or at least an average and the sum did even give me all the prices it missed some

---------- Post added at 02:32 PM ---------- Previous post was at 02:29 PM ----------

didn't* i meant

**tigeravatar** · 08-07-2012, 02:35 PM

You have multiple items in the same month. The only criteria you give it is the month. How is it supposed to know which of the many items to return?

Also, it did not miss any that I could find, it summed all that were appropriate based on the code and month.

If you'd prefer an average instead of a sum, use this array formula. Note that array formulas must be confirmed with Ctrl+Shift+Enter and not just Enter, that's how the formula gets the curly braces {} around it. Do not try to add those yourself:

Please Login or Register  to view this content.

**gjergji** · 08-07-2012, 02:40 PM

thats why i combined the material with the month so there would be a unique code created

---------- Post added at 02:40 PM ---------- Previous post was at 02:38 PM ----------

and i did sumif/countif and i got the average but the problem is that not all the values show up for example material code 300006

**tigeravatar** · 08-07-2012, 03:25 PM

gjergji,

Directly from the attachment in your first post, Sheet2, first 10 rows:

MATERIALCODE#	REC'D DATE	PRICE/LBS.
300025	04/09/12	$0.00
300001	04/04/12	$1.01
300071	04/05/12	$1.69
300089	04/04/12	$1.26
300202	05/02/12	$2.25
300001	04/06/12	$1.01
300112	04/23/12	$0.84
300067	04/26/12	$0.84
300067	04/26/12	$0.84

300001 and 300067 are both repeated in the month of April. That is why I was adding them together.
As for 300006 not working, it worked just fine for me.

Attached is a modified version of your posted workbook. I have removed the fake spaces from each month header in Sheet1. The formula in cell B2 and copied over and down is this array formula (see my note about array formula in my last post):

Please Login or Register  to view this content.

I have highlighted cell E8 which shows the value of 300006 in April (its only occurrence) of 1.045. That is the correct value. It is pulling it from row 66 of Sheet2. It looks like $1.05 because it has been formatted as currency with 2 decimal places. If you were to format that cell as General, you'll notice that it is actually 1.045

**gjergji** · 08-07-2012, 03:32 PM

thank you very much for taking your time to do this i really appreciate it ill try to work it into my actual file. The file i have provided is only a small part of the file I'm using.