Ref error coming when using index & matchfunction

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.