Apr 12 2019 05:33 PM
Hi Everybody.
I’m struggling with formula and wonder if you can help.
I have 2 spreadsheets. The first one represents a list of payments made on a particular day of the month.
The columns are date, room number, amount.
The second spreadsheet represents a table with days (horizontally) from the 1st day of the month thru 31st and room numbers (vertically) in the 1st column from 100 thru 150.
I’m trying to achieve a compact view of the entire month with the 2nd spreadsheet by populating appropriate cells in the 2nd spreadsheet based on payments made during the month.
I use Index function with two match factors (one based on room number and one based on date), but it doesn’t always work right.
The issue is that I have to scan the room numbers from the beginning for every payment as well as dates.
Any help is greatly appreciated.
Mark
Apr 13 2019 03:02 AM
Hi Mark,
The idea is correct, what exactly doesn't work? Perhaps you may attach sample file or at least give exact formula you use.
Apr 13 2019 02:50 PM
Apr 14 2019 06:46 AM
Hi Mark - unfortunately there was no file attached, could you please repeat it.
Apr 14 2019 10:06 AM
SolutionMark, you takes the value from 1D array and shall position row in it using two criteria (date and room #), not row and column.
It could be
=IFERROR( INDEX($U$3:$U$19, MATCH(1, INDEX( ($A3=$T$3:$T$18)*(B$1=$S$3:$S$18),0),0 ) ), 0)
INDEX within MATCH generates array of TRUE and FALSE (or 1 and 0) depend on criteria is met or not. Multiplication means AND condition.
Please check in attached.
Apr 14 2019 10:24 AM
I would suggest a pivot table.
Date in the columns area, Room # in the rows area and Pay in the values area.
Apr 14 2019 10:06 AM
SolutionMark, you takes the value from 1D array and shall position row in it using two criteria (date and room #), not row and column.
It could be
=IFERROR( INDEX($U$3:$U$19, MATCH(1, INDEX( ($A3=$T$3:$T$18)*(B$1=$S$3:$S$18),0),0 ) ), 0)
INDEX within MATCH generates array of TRUE and FALSE (or 1 and 0) depend on criteria is met or not. Multiplication means AND condition.
Please check in attached.