Index/Match vs. Index/Collect?

MJones

I am trying to collect multiple information sources into one collective sheet. First is the hours. Once I get the hang of the formula needed I can write for the others.

Collection Sheet: The blank highlighted cell below is where I am trying to write the formula. I want the formula to return the facility hours from the "Hours Sheet" underneath this one.

I want to say, in January (match the 1's), Belton (highlighted) had 9,382.2168

Hours sheet:

Everything I am trying is giving me "unparseable" or "incorrect argument set". I've not done a multiple criteria formula and I'm finding myself a bit lost.

Also to note: I'm not sure if I needed to set up column 6 in the hours sheet with the "Month" formula. I did this because I have a copy/paste of each month going into the hours sheet, so "Belton" will show up in each copy/paste every month. The idea is to pull from the correct month by matching the month numbers. Again, not sure if this is necessary or not.

Please help, and thank you!!

Paul Newcome

Try something like this:

=IFERROR(INDEX(COLLECT({Hours}, {Location}, @cell = $Facility$2, {Date Column}, AND(IFERROR(MONTH(@cell), 0) = January$1, IFERROR(YEAR(@cell), 0) = 2023))), 1), "")

The above can be dragfilled across to the right and is under the assumption that each of your month columns are for 2023.