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.
please help to complte this Thank you 

9PLEASE REFR MY WORKING SHEET

Answer
Discuss

Answers

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.
Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login