Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Using INDEX/MATCH across workbooks with totals and dates

0

I am by no means a power user, but I have managed to muddle through and find an INDEX,MATCH formula which comes close to doing what I want. What I have is several worksheets(sheet 1, sheet 3, sheet 4 etc.) that are all the same but I need to return information from 3 columns(which on my example file I have only inputted info to for simplicity) into a master worksheet (sheet 2). On sheet 2, I have the date and then I have a row of products. I want to return the amount of each product  used under the correct product heading by date in order. My formula returns it under the correct product but not in order of date. I also need it to return a '0' (zero) or blank (no error or #REF) if there is no amount entered for a nonused product, because worksheet 2 totals everything. For bonus, if it can search by product number only instead of the whole name and number that would keep inputs simpler.

Right now I have one formula to return the date to sheet 2 and a separate formula to find the right column to return the amount into. Sheet 1 and the others are just input tables, sheet 2 is the formula (master) sheet that totals the inputs from the other sheets.

excelformulas-functions
MS Office Version: 2016
posted by: Schneids (rep: 2) on Mon Jan 28, 2019 at 3:13 pm
Answer
Discuss

Discussion

There are too many questions in your question. (A) Your dates aren't dates. You won't be able to sort them. The idea to keep date and time in separate columns may not be good because Excel keeps date and time in one value. (B) The "Area" is C1 in Sheet2 but "1" in the other sheets. Not suitable for looking up until amendments have been made. You can have a cell value of "1" and display 'C1", if that is what you want. (C) If you want product numbers and names processed separately, why you enter them in the same cell in the first place? (D) Please look up the syntax of the MATCH function. From your faulty syntax it's impossible to understand what you are trying to do. If this were a separate question in its own thread you would be able to explain it properly. I suggest you edit your question to focus on one of the points and then ask another question when you got the reply. These questions are free. You needn't be so stingy with them.
Variatus (rep: 4889) Jan 29, '19 at 12:02 am
Yes, I know my questions are too complex, it was hard to figure out even how to ask it. Sorry, I will try to simpifly and do better. The log sheets have a lot of information that we have to keep but for my master sheet, I am only interested in keeping the date and amount for each product. The master sheet tallys up the total amounts used so I can see how much of each product is used every year. The sheet numbers correspond to a small group of people and I can change the name of the sheet to better represent that as in sheet 1 = C1, sheet 2 = Master, sheet 3 = C3 etc.. (I am afraid that that will confuse the formula so I will probably go with names there). So question 1; the formula I have is returning the information I want it to (this is the formula under the product names) should I nest the date into it to get it to successively return the amounts according to date?
Schneids (rep: 2) Jan 30, '19 at 10:03 am
Add to Discussion

Answers

0
Selected Answer

Question 1; the formula I have is returning the information I want it to (this is the formula under the product names) should I nest the date into it to get it to successively return the amounts according to date?

That the formula you have in Sheet2!C2 doesn't error out is a miracle. The lookup value can, obviously, only be one. Excel appears to be able to divine your intention but that comes at a high cost. Therefore I suggest to use the recommended syntax. Here it is.

=IFERROR(INDEX(Sheet1!$L$8:$L$110,MATCH(C$1,Sheet1!$J$8:$J$110,0)),"")

Note how the $ signs are set. You can copy this formula both to the right and down. I have also embedded your formula in an ISERROR function to avoid the display of #N/A. It isn't an array formula.

"Nesting the date into it" won't work. You will need a different kind of formula. I suggest to use SUMPRODUCT because it has the capability to handle arrays. For the formula below I created named ranges for the columns in Sheet1, "Qty" replacing Sheet1!L8:L110.

=SUMPRODUCT(((Dates=$B2)*(Products=C$1)),Qty)

This formula can be copied right and down as well. It can't return an error. Instead, it will return lots of zeroes. I suggest that you deal with them either by sheet setting (File > Options > Advanced > Display Options for this Worksheet and "Show a zero in cells that have zero value" unchecked) or by means of cell formatting (Format Cells > Number > Custom and 0;0; in the Type field).

posted by: Variatus (rep: 4889) on Wed Jan 30, 2019 at 8:52 pm
Discuss

Discussion

Pardon my ignorance, where does the sumproduct formula go?
Schneids (rep: 2) Jan 31, '19 at 1:59 pm
Thank you for your help BTW, Like I've said, I am not someone who has a lot of experience in excel and don't even use it all that often, but it is definitely a very useful tool.
Schneids (rep: 2) Feb 1, '19 at 8:50 am
Okay, I have inputted the formula and tried to copy it right and down. It works to the right but returns the same number in every box down.  So if the first number is 10, it repeats '10' all the way down. This is just the first formula. I will add the new file above. (active log2)
Schneids (rep: 2) Feb 1, '19 at 3:46 pm
Sorry for the delay Schneids. I'm presently enjoying not only a nice holiday but also a severe flu. Please bear with me.
Your efforts have succumbed to the temptation of "easy" range names instead of the recommended "meaningful" ones. Where or what is "Area1"? Here is the translation of the original formula I supplied: =SUMPRODUCT(((Dates=$B2)*(Products=C$1)),Qty). It says, "read the number from the row of the Qty range where the Dates range value = B2 AND the Products range value = C1". Obvously, to make this formula work you need 3 ranges, either named or defined by address. They must be of equal size. In fact, the formula will return the sum of all matching entries but that doesn't matter because there can only be a single one.
Variatus (rep: 4889) Feb 4, '19 at 9:49 pm
Add to Discussion
0

Okay I played around with the formula Variatus provided and with the removal of some $ signs and some column references, I got a formula to work and copy both right and down.

=IFERROR(INDEX(Area1!$L8,MATCH(C$1,Area1!$J8,0)),"")
Does that syntax look correct? I still don't know what to do with the sumproduct formula though.
posted by: Schneids (rep: 2) on Mon Feb 4, 2019 at 12:43 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login