# Ref error coming when using index & matchfunction

0

Hi Everyone,

Im working in a worksheet to capture labour attadance.

but when Im using Index and match fuction it coming correctly but if i drag it ref error appears in the sheet.

0

The error is in this MATCH.

``MATCH(H6,\$B\$2:\$B\$3090,0)``

It features a relative reference in 'H6'. So, the fault is in the test rather than the formula itself.

The complete, corrected formula ( rearranged for better logic) should be as below. Paste in G2 and copy down.

``=INDEX(\$D\$2:\$D\$3090,MATCH(\$B2,\$B\$2:\$B\$3090,0),MATCH(\$C2,\$C\$2:\$C\$3090,0),MATCH(\$E2,\$E\$2:\$E\$3090,0))``

You can make the formula both more readable and more efficient by using dynamic named ranges. Try setting up a named range with 5 columns with this formula in the 'RefersTo' field (Excel will add the sheet name automatically). The size of the range will adjust automatically to the number of rows in use.

``=OFFSET(\$A\$2,0,0,COUNTA(\$A:\$A)-1,5)``

With this named range in place you can now use the same above formula referencing any of the columns of the named range. I called this range "Data" and the formula refers to columns 4, 2, 3 and 5 of it.

``=INDEX(INDEX(Data,,4),MATCH(\$B2,INDEX(Data,,2),0),MATCH(\$C2,INDEX(Data,,3),0),MATCH(\$E2,INDEX(Data,,5),0))``

The formula will become much shorter and still easier to read if you set up several one-column named ranges.

``=INDEX(Times,MATCH(\$B3,IDs,0),MATCH(\$C3,Dates,0),MATCH(\$E3,Status,0))``
For this example I set up the ranges "Times", "Ids", "Dates" and "Status", each with a dynamic formula like this:
``=OFFSET(\$A\$2,0,0,COUNTA(\$A:\$A)-1)``
As you see it is exactly like the formula above except that it misses the "5". You might add a "1" (for 1 column) but that is the default which Excel will presume if the parameter is omitted.