Match Function not working as intended

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!

Hello @Martin_Estrada_Jr,
The issue here seems to be with the datatype mismatch in the arguments you are passing to the MATCH function.

MATCH function takes 3 arguments with the third one being optional. When we don’t pass the 3rd argument, MATCH considers it to be 1(default value) and tries to return the exact match or the next smallest value in the range.

Since lookup-value type is different from the type of lookup-range against which we are doing MATCH, it is unable to find the exact match and is returning an incorrect(next smallest) value.

Request you to consider having the same datatype for column B in “IDEM Audit Form Items” worksheet and range A1:BN1 in the “IDEM Audit Logs” worksheet(preferably both as Number or Automatic).
Similarly in the case of C3 & D3 of “IDEM Audit Form Items” worksheet where you have a drop-down with date values and are trying to match the range A1:A33 in “IDEM Audit Logs” worksheet.

If you want to receive values with date type instead of text via form submissions, you can define an additional column in “IDEM Audit Logs” worksheet with column formula =TRIM(A) and use this range in the MATCH function.

We have prepared a similar setup in case you want to check it out: Spreadsheet.com

Let us know if you have any further questions.

4 Likes

That worked really well, thanks.

2 Likes