Hello everyone, I recently set up a form to be used for inspecting municipalities. This form submits the data to a sheet called IDEM Audit Logs. From there I have another sheet called IDEM Audit Form Items. On this sheet you can select a date from a drop down list for 2 columns to compare different inspections. The list of dates is generated from the range on IDEM Audit Logs which is the submission dates from all of the forms submitted. On the IDEM Audit Form Items sheet, there is a formula next to each question that will find the Form answer from the date specified for that question. The formula I am using works perfectly until I get to the highlighted cells where it then only holds one value. See the screenshots below,
IDEM Audit Form Items
IDEM Audit Logs
The formula being used is,
=INDEX(‘IDEM Audit Logs’!A1:BN33,MATCH(C3,‘IDEM Audit Logs’!A1:A33)+1,MATCH(INDEX(A1:D68,ROW(),COLUMN()-1),‘IDEM Audit Logs’!A1:BN1)+1)
I will breakdown this formula for easier interpretation;
1st INDEX: This is the shell to find the answer for the form question I am looking for.
(1) ‘IDEM Audit Logs’!A1:BN33 is the range of the forms sheet where we want to find our answer. This includes all rows and columns.
(2) MATCH(C3,‘IDEM Audit Logs’!A1:A33)+1 is the second expression to INDEX which is the row number. MATCH gives the row number from IDEM Audit Logs sheet, for the date specified at the top of Audit Item Form Items sheet. This is where the first issue is. I had to add a +1 to the expression because the MATCH function was returning the ROW value of 1 less than the correct ROW. For Example, the date was on ROW 3 so MATCH was giving me a value of 2 instead of 3. INDEX seemed to work as intended. I isolated this to the MATCH function itself.
(3) MATCH(INDEX(A1:D68,ROW(),COLUMN()-1),‘IDEM Audit Logs’!A1:BN1)+1 is the 3rd expression to INDEX which is the column number. A +1 again is at the end to correct the wrong return value of MATCH.
(3A) INDEX(A1:D68,ROW(),COLUMN()-1) is the first expression within MATCH which determines the value we want to find a match. For this, INDEX was used to give the value. I did this instead of simply referencing the cell because I wanted to copy and paste the formula in each cell. (Dragging it down was messing up my cell format.)
(3B) ‘IDEM Audit Logs’!A1:BN1 is the 2nd expression in MATCH which determines the range to look for a match. Since this range is horizontal, the value returned is the COLUMN instead of ROW. The 3rd expression in MATCH was not used as it was not necessary and when added still did not correct the issue.
As you can see from the screenshots, this formula worked until reaching ROW 13 where at that point and after it only held the answer from ROW 12. I wanted to use the FORMULA data type but since I have drop down lists for the date at the top, this was not an option. Options like ROLLUP could potentially be used but with how my headers were set, those would also not work. I liked the headers how they were because it wrote the questions in my form for me which saved a lot of time since there were 66 questions.
So to recap the issues, MATCH is not returning the correct value and is consistently 1 less than actual. This could be related to setting the row as a header and column as primary. The respective row and column may not count towards the row or column count.
Also, the formula I have written is working for the first few ROWS and then only holds the value of the last ROW that calculated correctly.
I look forward to everyone’s responses on this.
Thanks!