Enter specific data with multiple criteria

**gjergji** · 07-25-2012, 04:46 PM

help.xlsx
In the file that I attached there are two sheets 1 and 2 which have tables in them. What I want is for excel to look up each cell in sheet 1 Inventory column in sheet 2 Materialcode# and if it finds the number then I want it to look up dates closest to the start of the month of June in the rec'd date column, if it does find the date then it will put the number next to it from column lbs. rec'd, this number will be entered in sheet 1 cell b2 and so on under Starting Stock.
It should look like this for Inventory number 300001. And if it says 0 for Entries then it would also say 0 for starting stock.

Inventory__________Starting Stock
300001____________14,000

This would be the correct amount for that one.

**icestationzbra** · 07-27-2012, 12:57 AM

see if this solves your requirement. this solution assumes that ALL of the dates in the RECEIVED DATE column on SHEET2 are, and will be, in ASCENDING order (oldest first, newest last). also, wherever you have "cancelled" in the DATE and LBS columns, the formula returns a zero (0). this is because excel considers text to be greater in value than numbers during comparative mathematical operations.

also, the data formatting in your file is all over the place; some numbers in the Material Code columns are stored as numbers, certain others as text. also, there are several instances of blanks. these are key ingredients in the recipe for disaster in formulaic operations.

**gjergji** · 07-27-2012, 09:16 AM

First of all thank you I appreciate you taking the time to do this, I'm very thankful. The formula some what works, but if you can explain to me how this formula work especially the index part I could fix it up my self.
=IFERROR(N(INDEX(Sheet2!$C$1:$C$1000,MATCH(1,INDEX(--(Sheet2!$A$1:$A$1000&""=Sheet1!$A2&"")*--(Sheet2!$B$1:$B$1000>=DATE(2012,6,1)),0),0))),"-")
Thank You once again!!!!!!

**gjergji** · 07-27-2012, 09:16 AM

First of all thank you I appreciate you taking the time to do this, I'm very thankful. The formula some what works, but if you can explain to me how this formula work especially the index part I could fix it up my self.
=IFERROR(N(INDEX(Sheet2!$C$1:$C$1000,MATCH(1,INDEX(--(Sheet2!$A$1:$A$1000&""=Sheet1!$A2&"")*--(Sheet2!$B$1:$B$1000>=DATE(2012,6,1)),0),0))),"-")
Thank You once again!!!!!!

**icestationzbra** · 07-27-2012, 09:49 AM

i think it might be easier for me to fix the formula than explain what it does

. i do not mind explaining, it is just that i will have to type a lot, which i am not a huge fan of. if you would like to figure out for yourself, you can use the Evaluate Formula function in Excel (ALT > M > V).

can you share with me what is it that "somewhat" works? i will attempt to draw up the update and hence, may be, indicate how this formula works...

by the way, in that formula, you can drop the minus signs that i have inserted in two places (double negs, double unary as they are called).

**gjergji** · 08-02-2012, 05:01 PM

Thank You for your effort in this but i was able to figure a simpler way of doing with no formula, but I do have a new post if you want to check it out.

**JBeaucaire** · 08-02-2012, 05:49 PM

As a courtesy to those who helped and those who might research later, please post your solution to this thread, then select Thread Tools from menu above and set this topic to SOLVED.